Re: CASE statement frustration...

Sorry guys, but you've missed the point :o) (Jean was close.) SQL CASE is an excellent feature, fully supported in SPL, and very different to 4GL CASE. The statement should be changed to: LET v_res = CASE v_test WHEN 0 THEN '1' WHEN 1 THEN '2' WHEN 2 THEN '3' ELSE 'else' END; I hate to say that it's identical in SQL Server ... Regards, Doug Lawry www.douglawry.webhop.org "Sean Kelsey" <sean.kelsey@moneyline.com> wrote: > IDS 9.40.TC4 (on Windows 2000 Advanced Server) > > Afternoon all... > > Can anyone tell me why the folloiwng CASE statement embedded within a > SPL won't work? Its really bugging me. > > TIA. > > Sean. > > CREATE PROCEDURE MIS_DoTest()> > DEFINE v_test INTEGER; > DEFINE v_res VARCHAR(12); > > LET v_test = 1; > LET v_res = '0'; > > CASE v_test > WHEN 0 THEN > LET v_res = '1'; > WHEN 1 THEN > LET v_res = '2'; > WHEN 2 THEN > LET v_res = '3'; > ELSE > LET v_res = 'else'; > END CASE; > > END PROCEDURE;

Doug Lawry wrote: > Sorry guys, but you've missed the point :o) (Jean was close.) > > SQL CASE is an excellent feature, fully supported in SPL, and > very different to 4GL CASE. The statement should be changed to: > > LET v_res = > CASE v_test > WHEN 0 THEN '1' > WHEN 1 THEN '2' > WHEN 2 THEN '3' > ELSE 'else' > END; > > I hate to say that it's identical in SQL Server ... Don't worry, CASE-expression is in the SQL Standard. I'm pleased to say that it is identical to SQL Server and DB2 (_all_ platforms) as it should be. Note that CASE statement is a different animal. It looks pretty much the same except that the "then-clauses" are statements and teh whoel beast stands on it's own. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab