| View previous topic :: View next topic |
| Author |
Message |
newcobol
Beginner
Joined: 20 Apr 2006
Posts: 88
Topics: 22
|
 Posted: Wed Jul 30, 2014 3:42 pm Post subject: Unload putting low value at end of data set |
 |
|
running an unload with this sql:
SELECT CHAR(SUM(POR1_GROSS_PRM_AMT))
the row looks like this with hex turned on, there is a dot at the end
| Code: |
0033534144268.65.
4FFFFFFFFFFFFF4FF0
00033534144268B650
|
do u know how to remove this dot? i tried substring 1,17 around the code and it still did it
this data set it going to be ftp'd to pc then emailed to customer to open in their tool of choice |
|
| Back to top |
|
 |
newcobol
Beginner
Joined: 20 Apr 2006
Posts: 88
Topics: 22
|
| Posted: Wed Jul 30, 2014 3:46 pm Post subject: |
|
|
p.s.
this is comp-3 field I am selecting |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Jul 30, 2014 4:05 pm Post subject: |
|
|
newcobol,
What is the column definition of POR1_GROSS_PRM_AMT? DECIMAL(15,2) ? Does it allow NULLS?
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
newcobol
Beginner
Joined: 20 Apr 2006
Posts: 88
Topics: 22
|
| Posted: Wed Jul 30, 2014 4:31 pm Post subject: |
|
|
DECIMAL , 11 , 2 ,N
decimal 11(meaning total) with 2 after the decimal point no nulls |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Jul 30, 2014 5:26 pm Post subject: |
|
|
newcobol,
One good thing about Unload is that it writes out the positions of the data and if you looked at SYSPUNCH dataset you would have found this
| Code: |
LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TBLNAME
(
" " POSITION( 1 )
CHAR( 17)
NULLIF( 18)=X'00'
)
|
So we need to handle the NULL condition and you can use IFNULL/VALUE/COALESCE
| Code: |
CHAR(IFNULL(SUM(POR1_GROSS_PRM_AMT),0)) |
The SYSPUNCH will now have the following
| Code: |
LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TBLNAME
(
" " POSITION( 1 )
CHAR( 17)
) |
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
