Date: 03/28/06 (SQL Server)    Keywords: no keywords

Here's my situation.

In either an INSERT INTO or an UPDATE query-- I can go either way with it, depending on what ends up being faster-- I need to assign a number to each row. That number is based on criteria from the other fields being pulled in. I could do this with a CASE statement, but that would be a long, long, long, long CASE statement.

An easier way to do it is with math. Pseudocode:

1. x = 0.
2. If CH.ParStatus = "N", x=x+5.
3. If Ch.ClmType not in ("I","O"), x=x+4 else
3a. If CD.POS = 21, x=x+1
3b. If CD.POS = 23, x=x+3
3c. For all other values of CD.POS, x=x+2
4. table.CatID = x.

Now, the question is... is it possible to do all this within one INSERT INTO/UPDATE statement? Back in the old FoxPro days, I'd have done this with a SCAN, but that option isn't available to me here...

Thanks.

Source: http://community.livejournal.com/sqlserver/46572.html