May 25, 2004 at 11:30 am
I would like to know the execution flow of a CASE statement. As the code processes, if a field gets updated, then is it left out of the rest of the flow, or let's say it meets criteria #1 and then later it meets criteria #3 as well? I just want my code once it meets the first criteria, to assign it that value. Thus, something like a precedence. If first criteria, that's it. If it meets two criteria, just pick the first (higher precedences). I don't want it to meet criteria #1 and then be overriden by the last flow statement which is low precedence. Or should I choose another flow statement to implement?
Let's say I have a CASE statement that UPDATES a certain table and with three conditions, e.g.:
UPDATE Table_Report
SET Field1 =
CASE
WHEN Field2 IN (SELECT E.EMP_PKEY FROM Table_Employee E INNER JOIN Table_Audit Q ON E.EMP_PKEY = Q.EMP_KEY INNER JOIN Table_Users T ON E.EMP_ID = T.EMPLID
WHERE (E.EMP_ID = '123456') OR
(E.EMP_ID = '100123') THEN 1
WHEN Field2 IN (SELECT E.EMP_PKEY FROM Table_Employee E INNER JOIN Table_Audit Q ON E.EMP_PKEY = Q.EMP_KEY INNER JOIN Table_Users T ON E.EMP_ID = T.EMPLID
WHERE (T.LOCATION = 'CALIFORNIA')) THEN 2
WHEN Field3 LIKE '%terminated%' THEN 3
END
WHERE Field1 IS NULL
May 25, 2004 at 6:01 pm
Only one of the paths will be taken. If it meets criteria#1 then it will not be tested for any others.
Note: your CASE excludes an ELSE, therefore Field1 will be assigned NULL if it meets none of your criteria.
Cheers,
- Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply