Thanks for your reply Gary - but your example places the values in one field
I am trying to place the 11 cases into 11 separate fields
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 Gary Thompson
Sent: Thursday, June 11, 2015 9:48 AM
To: Midrange Systems Technical Discussion
Subject: RE: How to use SQL CASE
Alan,
I'm not following your SELECT statement example.
The following is an example that may be similar to what you want:
SELECT wekday,
CAST ( CASE WHEN wekday = 1 THEN 'Monday'
WHEN wekday = 2 THEN 'Tuesday'
WHEN wekday = 3 THEN 'Wednesday'
WHEN wekday = 4 THEN 'Thursday'
WHEN wekday = 5 THEN 'Friday'
WHEN wekday = 6 THEN 'Saturday'
WHEN wekday = 7 THEN 'Sunday'
ELSE 'Day Error'
END AS CHAR(9) ) AS wekdaytxt
FROM library1/om06
Output from the sql example:
Week WEKDAYTXT
Day
2 Tuesday
5 Friday
5 Friday
2 Tuesday
2 Tuesday
4 Thursday
2 Tuesday
4 Thursday
4 Thursday
1 Monday
3 Wednesday
4 Thursday
2 Tuesday
4 Thursday
5 Friday
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, June 11, 2015 7:32 AM
To: midrange-l@xxxxxxxxxxxx
Subject: How to use SQL CASE
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.
midrange.com
