Reg: CASE usage in DB2

[156138]

Hi,

Suppose if i have a CASE statement in my select, is it mandatory to use
Else statement for the CASE.

Suppose i have a select statement as follows.

Select

CASE
WHEN B.SPG = '01' THEN C.PROM_SELL_PRICE_11
WHEN B.SPG = '02' THEN C.PROM_SELL_PRICE_12
WHEN B.SPG = '03' THEN C.PROM_SELL_PRICE_13
WHEN B.SPG = '04' THEN C.PROM_SELL_PRICE_14
WHEN B.SPG = '05' THEN C.PROM_SELL_PRICE_15
WHEN B.SPG = '06' THEN C.PROM_SELL_PRICE_16
WHEN B.SPG = '07' THEN C.PROM_SELL_PRICE_17
WHEN B.SPG = '08' THEN C.PROM_SELL_PRICE_18
WHEN B.SPG = '09' THEN C.PROM_SELL_PRICE_19
WHEN B.SPG = '10' THEN C.PROM_SELL_PRICE_20
ELSE 0
END

Suppose i have an unload step in my JCL for above select with some
record length.
And while selecting B.SPG not = '01' thru '10' then it is inserting a null indicator after C.SELL_PRICE field and record length is being automatically increased by one.

All these things are happening when there is no else for CASE statement.

I don't want Else statement ,is there is any way achieve this without else.

Regards,

Vinod Kumar Reddy.

[Kalicharan]

Hi,

If none of the conditions are matched, the value mentioned in the ELSE part is returned.
The ELSE part of the expression is not mandatory--
CASE expression will return null if nothing is satisfied.


If u are not selecting the range between 01 - 10 in your sql

[156138]

Thanks Kali for u r reply.

Let me provide some more details ,if nothing is selected it is returning NULL values.
Thats fine.

Suppose my length is say 40(as per JCL).

If it returns NULL value the file length is automatically changed to 41 which
i don't want .

Because whatever SQL iam using is Unload into a file which in turn the file
is used in my COBOL program.

So when i tried to read the file into my working storage layout,values are
not being populated into appropriate fileds.

Mind you the CASE is not only the SELECT statement using in my unload (i am selecting some
other fields which doesn't related to CASE).

So finally what i want to ask that , Is there any way stop that extra NULL indicator after NULL value being retrieved.

I think i can do it by comparing the value with NULL and removing NULL indicator in next step of my JCL( anyway i will try this option and let you know the result .)

Regards,
Vinod Reddy.
_________________
Regards,
Vinod Reddy.