Help with a complex connection request

Question

Keep in mind that I'm using SQL 2000

I have two tables.

tblAutoPolicyList contains a field called PolicyIDList .

tblLossClaims contains two fields called LossPolicyID and PolicyReview .

I am writing a stored proc that will receive a separate PolicyID from PolicyIDList and go through the LossPolicyID field (if a match is found, set PolicyReview to 'Y').

Table layout example:

 PolicyIDList LossPolicyID 9651XVB19 5021WWA85, 4421WWA20, 3314WWA31, 1121WAW11, 2221WLL99 Y 5021WWA85 3326WAC35, 1221AXA10, 9863AAA44, 5541RTY33, 9651XVB19 Y 0151ZVB19 4004WMN63, 1001WGA42, 8587ABA56, 8541RWW12, 9329KKB08 N 

How would I start writing a stored proc (looking for more logic than syntax)?

Keep in mind that I am using SQL 2000.

Answer 1

Select LossPolicyID, * from tableName, where charindex ('PolicyID', LossPolicyID, 1)> 0

Answer 2

Basically, the idea is this:

• 'Unroll' tblLossClaims and return two columns: a tblLossClaims (you did not specify anyone, so I think it will be LossPolicyID ) and Item = one item from LossPolicyID .

• Find unrolled.Item matches in tblAutoPolicyList.PolicyIDList .

• Find the matches of different matched.LossPolicyID in tblLossClaims.LossPolicyID .

• Update tblLossClaims.PolicyReview accordingly.

The main UPDATE might look like this:

 UPDATE claims SET PolicyReview = 'Y' FROM tblLossClaims claims JOIN ( SELECT DISTINCT unrolled.LossPolicyID FROM ( SELECT LossPolicyID, Item = itemof(LossPolicyID) FROM unrolling_join ) unrolled JOIN tblAutoPolicyList ON unrolled.ID = tblAutoPolicyList.PolicyIDList ) matched ON matched.LossPolicyID = claims.LossPolicyID 

You can use a fixed element width and a fixed list format and thus easily split LossPolicyID without UDF. I see this with a table of numbers and SUBSTRING() . unrolling_join in the above query is actually tblLossClaims connected to a number table.

Here's the definition of unrolled 'increased':

 ... ( SELECT LossPolicyID, Item = SUBSTRING(LossPolicyID, (v.number - 1) * @ItemLength + 1, @ItemLength) FROM tblLossClaims c JOIN master..spt_values v ON v.type = 'P' AND v.number BETWEEN 1 AND (LEN(c.LossPolicyID) + 2) / (@ItemLength + 2) ) unrolled ... 

master..spt_values is a system table that is used here as a table of numbers. The v.type = 'P' filter gives us a set of lines with numbers from 0 to 2047, which narrows to a list of numbers from 1 to the number of elements in LossPolicyID . In the end, v.number serves as the index of the array and is used to cut out individual elements.

@ItemLength, of course, is just LEN(tblAutoPolicyList.PolicyIDList) . I would probably also declare @ItemLength2 = @ItemLength + 2 , so it was not calculated every time the filter was applied.

Basically, this is if I did not miss anything.

Answer 3

If the PolicyIDList field is a splitting list, you must first separate the individual policy identifiers and create a temporary table with all the results. Then use the update request for tblLossClaims with 'where exists (select * from #temptable tt, where tt.PolicyID = LossPolicyID).

Depending on the size of the table / data, you can add an index to your temporary table.