Page 1 of 1

Case in Mainframe SQL

PostPosted: Wed Mar 03, 2010 7:50 pm
by ontheDB
Hi,

I would like to populate a field with a word depending on another column (e.g. use 'First' when field=1, use 'Second' when field =2).

The below 'CASE' usage works for TSQL, but doesn't in mainframe. Any suggestions on an alternative ?

Thanks!


Select *,
'Status' = CASE
WHEN place = 1 THEN 'FIRST'
WHEN place = 2 THEN 'SECOND'
WHEN place = 3 THEN 'THIRDE'
ELSE 'No Medal'
END
FROM dbo.finals;

Re: Case in Mainframe SQL

PostPosted: Wed Mar 03, 2010 8:03 pm
by ctrevino
what language are you using, is it COBOL?

Re: Case in Mainframe SQL

PostPosted: Wed Mar 03, 2010 9:11 pm
by ctrevino
Try this:
 (CASE  PLACE
   WHEN ‘ 1’     THEN UPDATE dbo.finals    SET  ‘Status’ = ‘FIRST’
   WHEN ‘2’     THEN UPDATE dbo.finals    SET  ‘Status’  = ‘SECOND’
   WHEN ‘3’    THEN UPDATE dbo.finals    SET  ‘Status’ ‘THIRD’
      ELSE ‘NO MEDAL’
  END CASE)

Re: Case in Mainframe SQL

PostPosted: Thu Mar 04, 2010 2:27 pm
by GuyC
open a manual and look up case

Re: Case in Mainframe SQL

PostPosted: Tue Apr 27, 2010 2:18 pm
by GuyC
1) giving a expression a name is not via syntax "Status" = .... , it 's ... as "Status"
2) when selecting other "columns" beside * you need to qualify it .

Select A.*,
CASE
WHEN place = 1 THEN 'FIRST'
WHEN place = 2 THEN 'SECOND'
WHEN place = 3 THEN 'THIRDE'
ELSE 'No Medal' A
END as 'Status'
FROM dbo.finals;