IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Issue while fetching a column(Nullable) in DB2 SQL.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Tue Mar 31, 2020 9:33 pm
Reply with quote

Hi Group,

I am facing one Issue while fetching a column from a DB2 table.

Running a SQL in the JCL using DB2 UNLOAD utility to extract result into a dataset.

1) When I use below CASE statement in SELECT clause.
ST_CD is the last column in the select clause after delimiter '#]&#'
Data base declaraion is CHAR(2)

CASE
WHEN ST_CD IS NULL THEN ' '
ELSE ST_CD
END

I see some two spaces before State code in the extract file

Code:
&#0000#]&#   
&#0044#]&#  CA
&#0000#]&#   
&#0000#]&#   
&#0044#]&#  CA
&#0044#]&#  CA
&#0044#]&#  CA
&#0044#]&#  CA
&#0044#]&#  CA


2) If i use the column ST_CD directly , I see "?" marks when value is null


Code:
&#0000#]&#  ?
&#0044#]&#CA
&#0000#]&#  ?
&#0000#]&#  ?
&#0044#]&#CA
&#0044#]&#CA
&#0044#]&#CA
&#0044#]&#CA


Expected results is

Code:
0000#]&# 
0044#]&#CA
0000#]&# 
0000#]&# 
0044#]&#CA
0044#]&#CA
0044#]&#CA
0044#]&#CA
0044#]&#CA



I used the funcions COALESE (ST_CD, ' ') , TRIM(ST_CD), SUBSTR(CHAR(ST_CD,1,2)).. Nothing is working as length of the field is 2 bytes, So there would be no spaces before value "CA" for instance in the databse to trim.

Please suggest If there is anyway to get the results as expected. Thank you in advance!

[/code]
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Apr 01, 2020 2:57 am
Reply with quote

try
Code:
reaplce(Value(ST_CD,'  '),'?',' ')


However, when you store, just store it spaces when its NULL.
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Wed Apr 01, 2020 1:19 pm
Reply with quote

Hi Rohit,

Thanks for the reply..

I tried REPLACE(VALUE(ST_CD,' '),'?',' ') , Got same issue " CA" ( Two spaces before state code).

My Lead suggested to try SUBSTR(COALESCE(ST_CD, ' '),1,2) and its working as expected.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Apr 01, 2020 1:23 pm
Reply with quote

Why you got two spaces at first places ? If it’s char(2) then how does substring solve the problem ? Column must not be char(2) but char(4)

If you just want to get rid of the spaces then try replace(colname,’ ‘,’’) which assumed that state col only have leading or trailing spaces only.
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Wed Apr 01, 2020 5:02 pm
Reply with quote

Hi Rohit,

No, ST_CD is defined as CHAR(2) not CHAR(4)

Code:
( ST_CD CHAR ( 2 ) NOT NULL
  WITH DEFAULT             
  FOR SBCS DATA           


Note: Coulmn is Defined as not null but Null values are due to Left outer join with other table.


I noticed that When there is null value, Its printing "?" on 3byte.. But i am not sure why valid value also printing 3rd position.

SUBSTR(COALESCE(ST_CD, ' '),1,2) solved the Issue.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Apr 02, 2020 3:57 am
Reply with quote

But if the column defines as NOT NULL then how can you possibly have null ? Something isn’t righ to what’s happening CS what you described
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Fri Apr 03, 2020 9:58 pm
Reply with quote

STATE table Left Outer joined with CUSTOMER table as I mentioned in the above reply.
State Number is common column in both the tables to join and fetching State code.

So when ever State Nbr from customer table does not exists in State table. We will get Null value in State code..
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Apr 03, 2020 11:11 pm
Reply with quote

VINAY PALLELA wrote:
STATE table Left Outer joined with CUSTOMER table as I mentioned in the above reply.
State Number is common column in both the tables to join and fetching State code.

So when ever State Nbr from customer table does not exists in State table. We will get Null value in State code..


You must present the exact code you are talking about, and not to tell here fairy tales about magnificent query.
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Mon Apr 06, 2020 4:40 pm
Reply with quote

Sergeken,

I thought info i provided is enough for the issue, as i just need to fetch the column to handle the null value and print in the required format.

Below is the total step from the JCL

Code:
//IKJE0180 EXEC  PGM=IKJEFT01,                           
//             DYNAMNBR=20                               
//SYSPRINT DD  SYSOUT=*                                 
//SYSUDUMP DD  SYSOUT=D                                 
//SYSTSPRT DD  SYSOUT=*                                 
//SYSTSIN  DD  *                                         
 DSN SYSTEM(DB0D)                                       
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('SQL')     
/*                                                       
//*                                                     
//SYSREC00 DD  DSN=HXVXP6.COMMON.CUSTOMER.SYSREC.FINAL, 
//             DISP=(NEW,CATLG,DELETE),                 
//             SPACE=(TRK,(16000,1600),RLSE),           
//             UNIT=SYSDA                               
//SYSPUNCH DD  DSN=HXVXP6.COMMON.CUSTOMER.SYSPUNCH,     
//             DISP=(NEW,CATLG,DELETE),                 
//             SPACE=(TRK,(150,15),RLSE),               
//             UNIT=SYSDA                               
//SYSIN    DD  *                                         
 CONNECT TO DSNDB0D;                                     
                                                         
 SET CURRENT SCHEMA = 'CID98D';                         
                                                         
   SELECT SUBSTR(CHAR(DIGITS(CUS_NBR)),4,7)             
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(CHAR(DIGITS(CUS_PC_NBR)),3,3)           
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_OPERATIONAL_NM                             
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_LEGAL_NM                                   
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_STREET_TXT                                 
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_CITY_TXT                                   
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_STATE_CD                                   
         ,SUBSTR('#]&#',1,4)                             
         ,CUS_ZIP_CD                                     
         ,SUBSTR('#]&#',1,4)                             
         ,DIGITS(CUS_RSTRCT_SUB_NBR)                     
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(CHAR(DIGITS(CUS_REP_NBR)),2,9)           
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(CHAR(DIGITS(CUS_GRP_RESTR_CD)),2,4)     
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(CHAR(DIGITS(CUS_CD128_BRCD_FL)),1,1)     
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(CHAR(DIGITS(CUS_RSTRCT_ST_NBR)),2,4)     
         ,SUBSTR('#]&#',1,4)                             
         ,SUBSTR(COALESCE(ST_CD, ' '),1,2)  <-- New column added to the existing Query         
     FROM CUSTOMER                                       
     LEFT OUTER JOIN STATE             <-- Added New table to fetch State Code                   
     ON   ( CUS_RSTRCT_ST_NBR - 39 ) = ST_NBR             
                                                         
    WHERE CUS_PC_NBR = SUBSTR(CHAR(DIGITS(CUS_NBR)),4,3) 
      AND CUS_PC_NBR IN (                                 
                          07, 21, 45, 68, 83,             
                          11, 23, 46, 71, 85,             
                          12, 24, 48, 75, 92,             
                          14, 40, 61, 80, 96,             
                          15, 43, 62, 81,                 
                          18, 44, 67, 82                 
                        )                                 
      AND CUS_INACTIVE_FL < 1                             
   ORDER BY CUS_NBR                                       
    FOR FETCH ONLY                                       
   WITH UR;   
/*           



Not sure why Null value is printing in 3rd Byte of the Sate code
COALESCE(ST_CD, ' ') will replace the NUll to Spaces but since Null is 3rd byte/position .. I was not getting expecting results for the valid state code prints after two spaces as shown below.

Code:
 &#0000#]&#   
 &#0044#]&#  CA
 &#0000#]&#   
 &#0000#]&#   
 &#0044#]&#  CA
 &#0044#]&#  CA
 &#0044#]&#  CA
 &#0044#]&#  CA
 &#0044#]&#  CA

Issue fixed by replacing Null with spaces and printing only first two bytes.
SUBSTR(COALESCE(ST_CD, ' '),1,2)

Code:
 0000#]&# 
 0044#]&#CA
 0000#]&# 
 0000#]&# 
 0044#]&#CA
 0044#]&#CA
 0044#]&#CA
 0044#]&#CA
 0044#]&#CA
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Apr 06, 2020 5:51 pm
Reply with quote

I still don’t understand how substr has anything to do with it when column is only 2 bytes.

Try this in proper way to handle null instead of substr hardcoding-
Code:
Case when state_cd is null then ‘’ else state_cd end
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Tue Apr 07, 2020 12:06 am
Reply with quote

Rohit,


Code:
Case when state_cd is null then ‘’ else state_cd end


My first trial was the above case statement, as it is commonly used way to handle null values. When it was not working as expected in this case, then I tried all other functions to replace the null with spaces.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Apr 07, 2020 12:15 am
Reply with quote

But why was it not working ? Unless you find that this hard coding isn’t useful as it might break if data changes. So to have it working for all the scenarios one must dig more and find why it did not work when it should. That’s how I will do it.
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Tue Apr 07, 2020 12:26 am
Reply with quote

SUBSTR(COALESCE(ST_CD, ' ') is working because when Null, "?" mark is printing in the 187 position which is 3rd position of ST_CD ( ST_CD is starting from 185 position).

I am assuming, COALESECE function is replacing Null value to spaces in 185 to 188 positions and SUBSTR is fetching only first two spaces and valid state code printing in 185 and 186 positions.

If I don't use SUBSTR , there will be space in 187 position causing the issue to print the valid state codes in 187 and 188 positions. (Spaces in 185 and 186 positions)
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Tue Apr 07, 2020 12:35 am
Reply with quote

When I logged in tomorrow my time. I can post how SYSPUNCH or Structure file is generating when Case statement was used. I could see Null handle is at 187th position in the structure file. Hence "?" Mark printing at 187th position for Null.

I am little confident there will be no issues. As I verified SYSPUNCH or Structure file with/without using SUBSTR to handle the Null value for the State code.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Apr 07, 2020 1:37 am
Reply with quote

I think , I know why it happened - try this
Run the same in SPUFI it will work but when you use it in utility it needs a twist. Some years back I went thru the same issue which I recall now.

Code:
CASE WHEN STATE_CD IS NULL THEN '  ' ELSE CHAR(STATE_CD,2) END
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Tue Apr 07, 2020 2:07 pm
Reply with quote

Yes, I was not facing any issue in Spufi/Terra Data.

Issue was only when runing the SQL in JCL using unload utility. As i told in my initial post, I tried some functions myself (COALESCE, TRIM, SUBSTR(CHAR( etc… ) to fix the format issue when nothing worked i posted the issue in the forum also sent an emial to my Project lead. Then my PL suggested to add SUBSTR to the COALESCE function.

SUBSTR(COALESCE(ST_CD, ' '),1,2).
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Apr 07, 2020 5:45 pm
Reply with quote

Did you retry what I shown above ?
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Wed Apr 08, 2020 2:44 pm
Reply with quote

Below Case statment did not solve the issue.

Code:
CASE WHEN ST_CD IS NULL THEN '  ' ELSE CHAR(ST_CD,2) END



OUTPUT File::

Code:
File  Edit  Edit_Settings  Menu  Utilities  Compilers  Test  Help           
                                                                             
VIEW       HXVXP6.COMMON.CUSTOMER.SYSREC.FINAL             Columns 00119 00190
Command ===>                                                  Scroll ===> CSR
****** ***************************** Top of Data *****************************
000001     #]&#CA#]&#91702     #]&#0#]&#000000000#]&#0000#]&#0#]&#0044#]&#  CA
000002     #]&#CA#]&#90250     #]&#0#]&#000000000#]&#0000#]&#0#]&#0000#]&#   
000003     #]&#CA#]&#91790     #]&#0#]&#000000000#]&#0000#]&#0#]&#0000#]&#   
000004     #]&#CA#]&#93219     #]&#0#]&#000000000#]&#0000#]&#0#]&#0000#]&#   
000005     #]&#CA#]&#90221     #]&#0#]&#000000000#]&#0000#]&#0#]&#0000#]&#   
000006     #]&#CA#]&#91731     #]&#0#]&#000000000#]&#0000#]&#0#]&#0044#]&#  CA
000007     #]&#CA#]&#91791     #]&#0#]&#000000000#]&#0000#]&#0#]&#0044#]&#  CA
000008     #]&#CA#]&#93446     #]&#0#]&#000000000#]&#0000#]&#0#]&#0044#]&#  CA
000009     #]&#CA#]&#92801     #]&#0#]&#000000000#]&#0000#]&#0#]&#0044#]&#  CA
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 24
Location: India

PostPosted: Wed Apr 08, 2020 3:01 pm
Reply with quote

Below is the SYSPUNCH file generated when i used

Code:
CASE WHEN ST_CD IS NULL THEN '  ' ELSE CHAR(ST_CD,2) END AS STATE_CD


STATE_CD column is starting at 186 position , Null at 190

Code:
VIEW       HXVXP6.COMMON.CUSTOMER.SYSPUNCH                 Columns 00001 00072
Command ===>                                                  Scroll ===> CSR 
000048    " "                                    POSITION(     173         )   
000049    CHAR(                      1)                ,                       
000050    " "                                    POSITION(     174         )   
000051    CHAR(                      4)                ,                       
000052    " "                                    POSITION(     178         )   
000053    CHAR(                      4)                ,                       
000054    " "                                    POSITION(     182         )   
000055    CHAR(                      4)                ,                       
000056    STATE_CD                               POSITION(     186         )   
000057    VARCHAR                                                             
000058         NULLIF(     190)='?'                                           
000059    )                                                                   
****** **************************** Bottom of Data ****************************
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts first column truncated in search result IBM Tools 13
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
Search our Forums:

Back to Top