|
View previous topic :: View next topic
|
| Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
Hi,
I am working on a DB2 query which looks like given below:
| Code: |
SELECT A_CODE >> Char(3)
, B_CODE >> Char(3)
, ARTICLE >> Char(20)
, AMT / QTY >> AMT and QTY -- data type INTEGER
FROM ARTICLE_TABLE
WHERE END_DT >= CURRENT DATE
AND STAT_CODE = 'A'
ORDER BY 1,2,3
|
Here, I am getting extract of this query in a file and File is getting generated with LRECL=31. I am not sure why Select on AMT divided by QTY is taking 5 bytes though both columns are of data type INTEGER and are greater than 1. Can anybody please explain the reason of it? Thank you. |
|
| Back to top |
|
 |
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
Have you checked your DCLGEN for this table for AMT or QTY column, how host variables are declared?
Please consider THIS also.
How much length are you expecting and little bit of data would be good to analyze further. |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
DCLGEN is having PIC clause of S9(9) COMP. I was expecting a length of 4 bytes.
E.g. AMT 12000
QTY 12
O/P value should be 1000
AMT 1000
QTY 6
O/P value should be 166
I am not interested in Fractional part of the value after Division. |
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
| Could you provide how are 1000 & 166 presented in a actual file? |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
As S9(9) Comp only.
| Code: |
A-CODE B-CO ARTICLE AMT
3/AN 3/AN 20/AN 4/BI
(1-3) (4-6) (7-26) (27-30)
1----------------- 2--------------- 3------------------- 4---------------
********************************* TOP OF DATA **********************-CAPS OFF-*
007 001 00000312 1000
007 001 00000313 166
007 002 00000314 30000
|
|
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
I don't see any problem here....
you are expecting length of 4 and you are getting 4 (27-30 as mentioned above).
So What is the value of 31st position in your file?
Please post the original data in the file (not file-aid view with copy book). |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
File without copybook ( Browse mode):
| Code: |
----+----1----+----2----+----3----+----4-
********************************* Top of
00700100000312 .....
00700100000313 .....
00700200000314 .....
|
View mode:
| Code: |
Command ===>
=COLS> ----+----1----+----2----+----3-
****** ***************************** Top of Data
000001 00700100000312
000002 00700100000313
000003 00700200000314
|
|
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
Hi Gagan,
Here are the way to see the actual value in the file:
1. Go to 3.4 and open your file in either Browse or View mode
2. Once opened, Issue 'HEX ON' command
3. Below ....., You can see how exactly the amount value has been stored.
Could you post that value here? Please take from 27-31 (5). |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
| Code: |
----+----1----+----2----+----3----+----4----+----5
----+----F----+----F----+----F----+----F----+----F
----+----1----+----2----+----3----+----4----+----5
-------------------------------------------------
00700100000312 ...Y.
FFFFFFFFFFFFFF444444444444000E0
0070010000031200000000000000380
-------------------------------------------------
00700100000313 ...D.
FFFFFFFFFFFFFF444444444444000C0
0070010000031300000000000000040
-------------------------------------------------
00700200000314 .....
FFFFFFFFFFFFFF44444444444400730
0070020000031400000000000000500
-------------------------------------------------
******************************** Bottom of Data **
|
|
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
| Have you checked your extract JCL for output file LRECL value ? |
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
As per your data,
| Code: |
000E
0038 is equal to 1000
|
but
| Code: |
000E0
00380 is equal to 256000
|
Same goes for 2nd and 3rd values.
You may refer here for conversion.
So I'm sure that the value in the 31st is not belongs to AMT fields. It is just a extra/junk value in the file. So if you specify LRECL=30 in your extract JCL, then it should be fine. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Any questions about what format/length/layout comes out of a particular unload can be solved by looking in the generated SYSPUNCH (if it is not set to dummy).
The 31st is probably not junk but a null-indicator. |
|
| Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur
|
|
|
|
Hi GuyC,
I'm little bit lost here....
it would be great if you explain why a Null-ind created for '/' operation.
Will it get generated automatically for all arithmetic operations? |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
Hi GuyC,
Thank you very much. you found it right.
| Code: |
LOAD DATA LOG NO RESUME YES INDDN SYSREC00 INTO TABLE
TBLNAME
(
A_CODE POSITION( 1 )
CHAR( 3) ,
B_CO POSITION( 4 )
CHAR( 3) ,
ARTICLE POSITION( 7 )
CHAR( 20) ,
" " POSITION( 27 )
INTEGER
NULLIF( 31)='?'
)
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
almost all output from calculations or functions return a nullable column.
I believe you can avoid this by adding the function COALESCE() in your query :
| Code: |
SELECT A_CODE, B_CODE, ARTICLE
, COALESCE(AMT / QTY , 0) as result
FROM ... |
If you do not want it to be binary, you could add the function
COALESCE(char(AMT/QTY),0)=> length:11, Remove leading zeroes, left aligned
or
COALESCE(digits(AMT/QTY),0) => length:10 |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
It worked. 
Thank you very much for the help. |
|
| Back to top |
|
|
|
|
