|
View previous topic :: View next topic
|
| Author |
Message |
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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: |
�#]&#
,#]&# CA
�#]&#
�#]&#
,#]&# CA
,#]&# CA
,#]&# CA
,#]&# CA
,#]&# CA
|
2) If i use the column ST_CD directly , I see "?" marks when value is null
| Code: |
�#]&# ?
,#]&#CA
�#]&# ?
�#]&# ?
,#]&#CA
,#]&#CA
,#]&#CA
,#]&#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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
try
| Code: |
| reaplce(Value(ST_CD,' '),'?',' ') |
However, when you store, just store it spaces when its NULL. |
|
| Back to top |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| 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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2263
Location: USA
|
|
|
|
| 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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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: |
�#]&#
,#]&# CA
�#]&#
�#]&#
,#]&# CA
,#]&# CA
,#]&# CA
,#]&# CA
,#]&# 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| 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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Did you retry what I shown above ? |
|
| Back to top |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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#]𖘶 #]�#]�#]�#]�#],#]&# CA
000002 #]&#CA#]𖂊 #]�#]�#]�#]�#]�#]&#
000003 #]&#CA#]𖚎 #]�#]�#]�#]�#]�#]&#
000004 #]&#CA#]𖰣 #]�#]�#]�#]�#]�#]&#
000005 #]&#CA#]𖁭 #]�#]�#]�#]�#]�#]&#
000006 #]&#CA#]𖙓 #]�#]�#]�#]�#],#]&# CA
000007 #]&#CA#]𖚏 #]�#]�#]�#]�#],#]&# CA
000008 #]&#CA#]𖴆 #]�#]�#]�#]�#],#]&# CA
000009 #]&#CA#]𖪁 #]�#]�#]�#]�#],#]&# CA
|
|
|
| Back to top |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
