You are combining a select query and an assignment query, not allowed!
You need to nest your case statements as you need to test the case for each record, variable assignment is a once only operation (using a cursor spit would allow you to do the record by record processing).
This example test the value of flag but note that it must know what to expect in the nested case, your ... cannot be a random date, it must be a testable value!
CASE Flag WHEN'FX'THENCASE Records WHEN0THEN'Failed'ELSE'Ok'ENDWHEN'MM'THENCASE Records WHEN0THEN'Failed'ELSE'Ok'ENDWHEN'TZ'THENCASE Records WHEN0THEN'Failed'ELSE'Ok'ENDENDFROM ProcessLog PL
Never underestimate the power of human stupidity
Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not?
When does it generate the new ID? At transaction start or finish?
Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully?
Maybe we can chalk this down to a lost in translation but what Jorgen said is correct.
Identity columns allow you to assign a unqiue value to each row. It doesn't get reset to the previous value if you rollback the transaction.
If you require no gaps in your keys then you will need to think of a different approach.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
Last Visit: 31-Dec-99 19:00 Last Update: 28-Dec-14 22:31