Re: How to use SQL CASE

[Dan <dan27649@xxxxxxxxx>]

I see a comma after the '9' in the list.

- Dan

On Thu, Jun 11, 2015 at 9:31 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