RE: How to use SQL CASE

Hi Justin
Thanks for your reply
This may be what I am looking for
I will need to play around with it and see


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Justin Dearing
Sent: Thursday, June 11, 2015 9:58 AM
To: Midrange Systems Technical Discussion
Subject: Re: How to use SQL CASE

You might be able to achieve this more elegantly with a PIVOT:

http://stackoverflow.com/questions/13579143/how-can-i-pivot-a-table-in-db2

The PIVOT might perform well enough where you can keep that as a VIEW and keep your original TABLE (Physical File) as to store the data. and then just SELECT from the VIEW when you need that data.






On Thu, Jun 11, 2015 at 9:32 AM Alan Shore <ashore@xxxxxxxx> wrote:

Hi everyone
Before I forget - we are on V5r4 - hopefully moving up to the latest
release very soon Here is my predicament I have a file that contains 3
fields User Profile name Box Size Count

Box size can have valid values of '0', '1', '2', '3', '4', '5', '6',
'7', '8', '9'
I want to create a new file with the following SELECT OPER, case
BOX_SIZE when '1'then SEL0003 end as Count1, case BOX_SIZE when
'2'then SEL0003 end as Count2, case BOX_SIZE when '3'then SEL0003 end
as Count3, case BOX_SIZE when '4'then SEL0003 end as Count4, case
BOX_SIZE when '5'then SEL0003 end as Count5, case BOX_SIZE when
'6'then SEL0003 end as Count6, case BOX_SIZE when '7'then SEL0003 end
as Count7, case BOX_SIZE when '8'then SEL0003 end as Count8, case
BOX_SIZE when '9'then SEL0003 end as Count9, case BOX_SIZE when
'0'then SEL0003 end as Count0, case BOX_SIZE when not in ('0', '1',
'2', '3', '4', '5', '6', '7'
'8', '9',) then
SEL0003 end as Count_Other FROM file

It's the last case clause that I am having a problem with case
BOX_SIZE when not in ('0', '1', '2', '3', '4', '5', '6', '7'
'8', '9',) then
SEL0003 end as Count_Other



I want to capture the count if its not a valid value into its own
field

I hope this makes sense
As always - all response gratefully accepted



Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.