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.
Reg: CASE usage in DB2
Moderators: Kalicharan, Moderator Group
- Kalicharan
- Moderator
- Posts: 31
- Joined: Wed Feb 08, 2006 1:51 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
Please elaborate your requirement clearly.
Thanks
Kalicharan
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
then what is the necessity of using case in sql.B.SPG not = '01' thru '10'
Please elaborate your requirement clearly.
Thanks
Kalicharan
Reg: CASE usage in DB2
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.
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.
Vinod Reddy.
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page