|
View previous topic :: View next topic
|
| Author |
Message |
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi,
I have a table TABLE1 which has a column SEQ_NO that is declared decimal & the DCLGEN generated shows it as equivalent to COMP-3.
I decided to run a batch SQL program using utility IKJEFT1B. The query execution is successful but the value returned in SEQ_NO is in COMP-3 format & hence un-readable. I have tried to use the cast function but without success.
Kindly let me know if I can get around this problem using some modifications to the SQL. One option is executing another utility to convert the PD format to ZD format in the next step. But handling it within query will be of immense help.
I have executed the same query in SPUFI & the result was in displayable format. So I was just wondering the reason the same query was throwing a different result in batch.
Also it would be great if you can provide me a way to print the column headers to output as well.
Thanks |
|
| Back to top |
|
 |
Rahul Agrawal
New User
Joined: 19 Jul 2010
Posts: 9
Location: Noida
|
|
|
|
Hi,
In batch, SEQ_NO stored is in hex format (packed decimal) and hence can be seen by doing "HEX ON" in the o/p dataset. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Depending on what you mean by "a batch SQL program", you should change the code to produce the desired output rather than packed-decimal output. You can also control the column headers in your code.
Unless there is really no program at all in which case something else will be needed. . . |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Quote: |
| I have tried to use the cast function but without success. |
That doesn't tell us a lot, show us what you used and the results. |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi,
The JCL I have used to get the O/P is:
| Code: |
//STEP01 EXEC PGM=IKJEFT1B
//STEPLIB DD DSN=<pre-qualifier>.DSNLOAD,DISP=SHR
// DD DSN=<pre-qualifier>.DSNEXIT,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN D *
DSN SYSTEM(sub-system)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
/*
//SYSREC00 DD DSN=<dataset name>,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(2,2),RLSE)
//SYSIN DD *
SELECT MODEL_NO,SEQ_NO
FROM creator.TABLE1
WHERE DISPATCH_DATE < '99999999';
/* |
A sample O/P I got on execution of the above query is:
| Code: |
XXXXXXXXX ð
YYYYYYYYY @
ZZZZZZZZZ æ
AAAAAAAAA @ |
The SEQ_NO displayed above is not in displayable format. Hence I need to:
1. Convert the SEQ_NO into displayable format & display it
2. No headers are printed in the O/P dataset. I want to print the column names as the headers as printed if the above query is run in SPUFI.
I tried using CAST function in the above JCL but without success. The modified SQL using cast function is:
| Code: |
SELECT MODEL_NO,CAST(SEQ_NO AS INT)
FROM creator.TABLE1
WHERE DISPATCH_DATE < '99999999'; |
Thanks |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| try cast( seq_no as char(10)) or char(seq_no) |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
and before you ask GuyC why to you should CAST as Char,
RTFM and see what is the datatype for Interger.
If you knew some of the fundamentals, you would not have somany problems. |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi Guy C,
Thanks for the help. I tried both CHAR function & CAST function as you stated above & both worked great. Thanks for the help once again.
I would also like to know if you can help me in printing the column names for each of the columns as displayed in SPUFI.
I tried using AS operator for printing column names but to no success.
Thanks |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
something along the line of:
| Code: |
SELECT 'Model No '
, 'Seq_no '
FROM SYSIBM.SYSDUMMY1
UNION
SELECT MODEL_NO,CAST(SEQ_NO AS INT)
FROM creator.TABLE1
WHERE DISPATCH_DATE < '99999999';
|
would accomplish what you want. |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi Dick,
I checked you way out & it's working as I intended. Thanks a lot.
By the way, is there any way we can alter the way DSNTIAUL processes (by passing parms maybe) to force it to print column headers?
Thanks for the help once again. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
never could find any, that's why I came up with the UNION.
that does not mean there are not any, just that I was too lazy to find them. |
|
| Back to top |
|
|
|
|
