Thanks Vern
That was exactly it
I changed my last case to be equal to your last case and magically it works
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 Vernon Hamberg
Sent: Thursday, June 11, 2015 10:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: How to use SQL CASE
Hi Alan
Someone else noted the extra comma after '9' in the list of values.
But that's not the main issue - I think there are a couple ways to do this.
First to explain what you have - this is a case1 style according to the documentation - you name a column after CASE, then have a value for that column after the WHEN - the key thing here is, it has to be a value that matches the column in data type and all.
I don't think I've ever tried to put a predicate after the WHEN as you did for the exception - in any "case", that might fail even if you get rid of the comma, because "not in" returns a boolean - a true/false - and this will not match a single-character data type that your column has.
Gary's suggestion to use ELSE is probably the simplest and lets you keep this style - the case1 style.
Another approach that lets you specify any conditionals is my choice, usually - and that would be to put the column name after WHEN with the test condition operators. IBM calls this case2 styele. Then you could have something like the last one you have, with the list.
So that would be something like this -
SELECT OPER,
case when BOX_SIZE = '1' then SEL0003 end as Count1, case when BOX_SIZE = '2' then SEL0003 end as Count2, case when BOX_SIZE = '3' then SEL0003 end as Count3, case when BOX_SIZE = '4' then SEL0003 end as Count4, case when BOX_SIZE = '5' then SEL0003 end as Count5, case when BOX_SIZE = '6' then SEL0003 end as Count6, case when BOX_SIZE = '7' then SEL0003 end as Count7, case when BOX_SIZE = '8' then SEL0003 end as Count8, case when BOX_SIZE = '9' then SEL0003 end as Count9, case when BOX_SIZE = '0' then SEL0003 end as Count0, case when BOX_SIZE not in ('0', '1', '2', '3', '4', '5', '6', '7'
'8', '9',) then
SEL0003 end as Count_Other FROM file
But i still like the ELSE option better - that works with either style.
HTH
Vern
On 6/11/2015 8:31 AM, Alan Shore 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.
midrange.com
