Re: How to use SQL CASE

[Justin Dearing <zippy1981@xxxxxxxxx>]

Gary,

I think Alan is trying to use CASE to PIVOT, if he wants two columns, then
the correct SQL would be, as you hinted at


SELECT OPER,
case BOX_SIZE when '1' then 'Count1',
when '2' then 'Count2',
when '3' then 'Count3',
when '4' then 'Count4',
when '5' then 'Count5',
when '6' then 'Count6',
when '7' then 'Count7',
when '8' then 'Count8',
when '9' then 'Count9',
when '0' then 'Count0',
ELSE 'Count_Other'
END AS SEL0003
FROM file




On Thu, Jun 11, 2015 at 10:04 AM Gary Thompson <gthompson@xxxxxxxxxxx>
wrote:

> 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.