Hi All,
I am using DSNTIAUL utility to unload data and the query used to unload data is somewhat similar to the following query,
Select shop_id,
shop_name,
(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),
shop_addr1,
shop_addr2
from Shop_det
where shop_city_cd = 'NC'
Problem Description:
after successful completion of the unload step,
all the columns from the table are unloaded to the file correctly, except the third column in the above query.
That timestamp column(max(shop_last_pur_ts)) was unloaded as 27 bytes, the 27th byte being null. I need your suggestion to get this timestamp in the output file with only 26 bytes.
Additional information:
Shop_last_pur_ts was defined as TIMESTAMP type also NOT NULL.
I had tried to convert that whole column into Char type to take only 26 char of that timestamp. but didn't work for me. when i tried the following
again i got the timestamp value in that file as 26+1 bytes
(select substr(char(max(shop_last_pur_ts)),1,26) from shop_pur_det where pur_type = 'POS').
when i tried the following i got 25+1
(select substr(char(max(shop_last_pur_ts)),1,25) from shop_pur_det where pur_type = 'POS')
Please suggest me a way to have this problem fixed.
Thanks in advance
Facing issues when using DSNTIAUL
Moderators: Kalicharan, Moderator Group
- thamodharan
- Member
- Posts: 21
- Joined: Tue Oct 21, 2008 4:45 pm
- Location: chennai
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
you receive a null indicator when the result of the select for a column could potentially be null.
(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),
could be null. so try using case, value or coalease to insure you never receive a null from the max function.
has nothing to do with the column being not null. it is the result of the scalar function MAX. read the manual concerning MAX.
(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),
could be null. so try using case, value or coalease to insure you never receive a null from the max function.
has nothing to do with the column being not null. it is the result of the scalar function MAX. read the manual concerning MAX.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
- thamodharan
- Member
- Posts: 21
- Joined: Tue Oct 21, 2008 4:45 pm
- Location: chennai
Hi Dick,
I will try using Case, Value or Coalease function as well and let you know if that worked.
Thanks
The above field has never been a NULL. It was 27 bytes instead of 26 bytes. First 26 bytes were having the Timestamp and the 27th byte was having no value. Please suggest me how to get only timestamp(26 bytes).(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),
could be null.
I will try using Case, Value or Coalease function as well and let you know if that worked.
Thanks
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
DB2 does not have fields, it has columnsThe above field has never been a NULL
improve your reading skills.dbz wrote:has nothing to do with the column being not null. it is the result of the scalar function MAX. read the manual concerning MAX.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
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