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
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!
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..
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.
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)
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.
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.
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)
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.
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
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.