I am trying to write a case statement to pull data....I can run reports and get data for all except 'NYKS-210' (the last one) and I can't work out why?
case
when [Union1].[Service Scope Code] IN ('AHE','APW','OPE','TAW','TPE','WPE') and [Union1].[POL Continent Code]<>'E' then
'NYKS-215'
WHEN [Union1].[Service Scope Code] IN ('LAN','LAS','LWN','LWS') THEN
'NYKS-216'
WHEN [Union1].[Service Scope Code] IN ('AHW','APE','OPW','TAE','TPW','WPW') and [Union1].[POD Continent Code]<>'E' THEN
'NYKS-217'
WHEN [Union1].[Service Scope Code]='TAE' AND [Union1].[DEL Country Code] not in ('IE' , 'FI' , 'NO' ,'DE' ,'PL' , 'SE') AND [Union1].[DEL Region Code] <>'GBR' THEN 'NYKS-210'
END
What's the Continent Code for the records in your last element? Anything with a scope code of TAE and a continent code other than E will be caught by the previous line, which means that the case statement will never get to the last line.
right so case statements will also work against each other... I will try removing TAE from line above...
I'm not sure about the "working against each other", but essentially CASE statements work from top to bottom. When they reach a true statement they evaluate that, then move on. Depending on what you're trying to achieve you can either take TAE out, or move the lower statement higher up in the CASE stack.
many thanks I restructured it works well now... yah.