MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

CAST with CASE Statement

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
subho
Beginner


Joined: 19 Oct 2004
Posts: 13
Topics: 4
Location: Atlanta
PostPosted: Thu Jul 09, 2009 5:14 pm    Post subject: CAST with CASE Statement Reply with quote
Code:

SELECT
CAST (CASE WHEN A1 IN (1,2,9) THEN 4
         WHEN A1 IN (6,7) THEN 5
END CASE AS SMALLINT),
COL2
FROM TABLE1
WHERE A1 IN (1,2,6,7,9)
;


My objective is get the result from the CASE statement in SMALLINT format. DB2 does not like this SQL. Any suggestion will be appreciated.

Thanks,
Subho
Back to top
View user's profile Send private message
subho
Beginner


Joined: 19 Oct 2004
Posts: 13
Topics: 4
Location: Atlanta
PostPosted: Thu Jul 09, 2009 5:17 pm    Post subject: Reply with quote
I think I have got the solution. The following SQL works for me:
Code:

SELECT
CASE WHEN A1 IN (1,2,9) THEN CAST(4 AS SMALLINT)
WHEN A1 IN (6,7) THEN CAST(5 AS SMALLINT)
END CASE,
COL2
FROM TABLE1
WHERE A1 IN (1,2,6,7,9)
;


Is there any other way to write this SQL?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Jul 09, 2009 7:42 pm    Post subject: Reply with quote
subho,

you don't need to code cast and case statement for getting the desired. Since your where clause is already picking a defined set of records , you really don't need the second when statement as the first when is covering the other possible values , so an else is sufficient instead of another when statement

Try this untested sql
Code:

SELECT CASE WHEN A1 IN (1,2,9)
            THEN SMALLINT(4) ELSE SMALLINT(5)
            END
      ,COL2               
  FROM TABLE1                               
 WHERE A1 IN (1,2,6,7,9)                       
 ;       


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
subho
Beginner


Joined: 19 Oct 2004
Posts: 13
Topics: 4
Location: Atlanta
PostPosted: Fri Jul 10, 2009 8:22 am    Post subject: Reply with quote
Kolusu,
Thanks for your reply. Yes, it works as well!
I have multiple WHEN in CASE statements of my query and my last WHEN is substituted with ELSE.

Thanks,
Subho
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group