Reg: CASE usage in DB2

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
156138
Member
Posts: 15
Joined: Tue Aug 29, 2006 7:27 pm
Location: Chennai

Reg: CASE usage in DB2

Post by 156138 » Wed Aug 30, 2006 10:44 am

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.

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Tue Sep 05, 2006 9:01 pm

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
B.SPG not = '01' thru '10'
then what is the necessity of using case in sql.

Please elaborate your requirement clearly.

Thanks
Kalicharan

156138
Member
Posts: 15
Joined: Tue Aug 29, 2006 7:27 pm
Location: Chennai

Reg: CASE usage in DB2

Post by 156138 » Fri Sep 08, 2006 12:41 pm

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.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others