Case in Mainframe SQL



IBM's flagship relational database management system

Case in Mainframe SQL

Postby ontheDB » Wed Mar 03, 2010 7:50 pm

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;
ontheDB
 
Posts: 1
Joined: Wed Mar 03, 2010 7:44 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Case in Mainframe SQL

 

Re: Case in Mainframe SQL

Postby ctrevino » Wed Mar 03, 2010 8:03 pm

what language are you using, is it COBOL?
Christy T.


Wherever you go, there you are - Buckaroo Banzai
ctrevino
 
Posts: 62
Joined: Tue Feb 23, 2010 1:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: Case in Mainframe SQL

Postby ctrevino » Wed Mar 03, 2010 9:11 pm

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)
Christy T.


Wherever you go, there you are - Buckaroo Banzai
ctrevino
 
Posts: 62
Joined: Tue Feb 23, 2010 1:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: Case in Mainframe SQL

Postby GuyC » Thu Mar 04, 2010 2:27 pm

open a manual and look up case
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Case in Mainframe SQL

Postby GuyC » Tue Apr 27, 2010 2:18 pm

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;
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post