|
View previous topic :: View next topic
|
| Author |
Message |
cvijay784
Warnings : 1
New User
Joined: 18 Jun 2008
Posts: 54
Location: Colombo
|
|
|
|
Hi,
I have a db2 load statement as shown below.
-----------------------------------------------------------------------------------------------------------------------------------
LOAD DATA RESUME YES LOG YES SHRLEVEL CHANGE ENFORCE CONSTRAINTS INTO TABLE table_name
(column1 POSITION (1:6) Char,
column2 POSITION (7:10) Integer)
-----------------------------------------------------------------------------------------------------------------------------------
Input for the integer field column2 is S9(9) COMP. I need to nullify the field when value is 0. I tried below nullif statement; but it throwed invalid keyword '0'. I dont know how to compare binary signed value in nullif. Please help me.
-----------------------------------------------------------------------------------------------------------------------------------
LOAD DATA RESUME YES LOG YES SHRLEVEL CHANGE ENFORCE CONSTRAINTS INTO TABLE table_name
(column1 POSITION (1:6) Char,
column2 POSITION (7:10) Integer nullif (7:10 = 0))
----------------------------------------------------------------------------------------------------------------------------------- |
|
| Back to top |
|
 |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Use something like
| Code: |
Column2 POSITION(7:10) integer NULLIF(6)=X'FF'
|
where 6 is a position in the load file having null indicators. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
would it not be:
| Code: |
(column1 POSITION (1:6) Char,
column2 POSITION (8:11) Integer NULLIF (7) = X'FF') |
|
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| It can be either way... I assumed that file would have a null indicator at position 6. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| gylbharat wrote: |
| It can be either way... I assumed that file would have a null indicator at position 6. |
gylbharat,
i don't understand how it can be in 6 or 7.
the first 6 are the char column.
then the integer column for 4.
null ind for 1.
so would it not be 6 1 4
or 1-6 for 6 (for the char(6) column)
7-7 for 1 (for the null ind)
and 8-11 for 4? (for the int column)
by my way of thinking,
if the null ind was at 6,
then the char column would only have 5 instead of 6.
i was not disagreeing with your syntax,
only the correct column positions. |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Thanks Dbz... Yes you are correct... i was thinking in some other way... |
|
| Back to top |
|
|
cvijay784
Warnings : 1
New User
Joined: 18 Jun 2008
Posts: 54
Location: Colombo
|
|
|
|
Hi,
Thanks for your responses. I think you are checking only the 1st byte of the input integer (S9(9) Comp) value. I want to check the whole 4 bytes from 7 to 10 for zeroes. Please help me. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm not sure , but doesn't this work ?
| Code: |
| column2 POSITION (7:10) Integer NULLIF (7:10) = X'00000000') |
|
|
| Back to top |
|
|
cvijay784
Warnings : 1
New User
Joined: 18 Jun 2008
Posts: 54
Location: Colombo
|
|
|
|
| Adding some more information. Input of integer field is coming from cobol output in S9(9) Comp format. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| cvijay784 wrote: |
| Adding some more information. Input of integer field is coming from cobol output in S9(9) Comp format. |
Just out of interest, what are any Cobol programs doing with this field? |
|
| Back to top |
|
|
cvijay784
Warnings : 1
New User
Joined: 18 Jun 2008
Posts: 54
Location: Colombo
|
|
|
|
Cobol program gives the feed to table load step.
And i tried the following statement. It works fine. Thanks.
column2 POSITION (7:10) Integer NULLIF (7:10) = X'00000000') |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Sorry, I guessed that bit. What I was wondering is if the Cobol is doing any calculations with that. The reason being that a 9 digit binary is less efficient for calculation that either an 8 digit or a 10 digit. If doing none or not much calculation, don't worry about it (now). If doing truckloads upon truckloads it might be something to think about.
I just tend to avoid 9 digit binary, in the same way as 1, 2, 3, 5, and 7 digit. And unsigned numerics. And even-number of digits for packed. And defining as numeric things which will never be used for calculation even if they have the word "number" in their normal name (account number, part number, catalogue number etc ). |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
cvijay784 and gylbharat,
sorry for my post.
was not on topic and provided a non-solution to a different problem.
will attempt to follow the TS's requirement better in the future. |
|
| Back to top |
|
|
|
|
