|
View previous topic :: View next topic
|
| Author |
Message |
Ramanan-R
New User
Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India
|
|
|
|
Hello All,
In PGM1, I'm doing a Left Outer Join between two tables and the result set returns one of the field which is PIC X(10) as X'60606060606060606060' (Got this using HEX ON command in SPUFI for unmatched rows) which is a NOT NULL field as it is a part of Key field....
The requirement is to write them as is to a flat file in PGM1, which is used as i/p by another program, PGM2
Now in PGM2, i need to check that alphanumeric field for NULL or X'60606060606060606060' to do further processing...
Please advice...
Thanks!
Ramanan R |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Can't you ask in Prog1 whether a particular thing is NULL and then set a flag so that Prog2 can become aware?
This would be better than using a hex literal in Prog2 of X'606060...' for an appropriate length. |
|
| Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India
|
|
|
|
| Yes, i can but not sure how to check for a NULL in an alphanumeric variable. Your help is much appreciated... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Ramanan-R wrote: |
| Yes, i can but not sure how to check for a NULL in an alphanumeric variable. Your help is much appreciated... |
start by learning what NULL means.
Null is a column attribute,
not a value. |
|
| Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India
|
|
|
|
All i know is, NULL is a place holder for unknown value in DB2.
Does that mean, we can't check an alphanumeric field is NULL in COBOL? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Ramanan-R wrote: |
| All i know is, NULL is a place holder for unknown value in DB2. |
that is incorrect
| Ramanan-R wrote: |
| Does that mean, we can't check an alphanumeric field is NULL in COBOL? |
no, you can, but you need to learn what NULL means.
db2 manuals are available from hyperlinks at the top of the page.
if you wish to see the 'hex' value of a column,
you need to use the HEX function in your sql.
turning HEX ON for a spufi display is not always accurate.
now if the column is defined in the CREATE Table as NOT NULL,
then it can not ever be NULL..................................... |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Ramanan-R wrote: |
| All i know is, NULL is a place holder for unknown value in DB2. |
Well then, you should read the fine manual and learn something. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
Since you posted in the COBOL forum, you need to learn that NULL is a term very carefully and specifically defined in COBOL, and that definition has NOTHING to do with DB2 or any other database. Read section 1.3.7 of the COBOL Language Reference manual (click on the Manuals link at the top of this page) to find out what NULL is in COBOL.
The DB2 NULL is not a value in the collating sequence of EBCDIC that you can test -- as you have been told by others. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
Interesting - here we have been saying since time immemorial that there is no such thing as 'null value' wrt DB2 columns when the very first line, on the link Akatsukami gave above, that very phrase is used and several times in the piece!
But WE know that it is not a data value. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Nic Clouston wrote: |
Interesting - here we have been saying since time immemorial that there is no such thing as 'null value' wrt DB2 columns when the very first line, on the link Akatsukamii gave above, that very phrase is used and several times in the piece!
But WE know that it is not a data value. |
But DB2 is not a mere data storage and retrieval system; it operates at a higher level of abstraction. All the DB2 constructs are part of that abstraction. The difficulty arises when sotfware engineers conflate misunderstood concepts, as with, e.g., not understanding the relationship between binary, packed decimal, and zoned decimal data.
DB2 really does have a null value. But you can stare at a dump of an LDS until your eyes dry out, and you'll never find it. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Quote: |
| Distinct from all nonnull values, the null value is a special value that denotes the absence of a (nonnull) value. |
If null, in this DB2 context, represents the absence of a value, you can't store a value that is null, in this context, in a byte because that would necessitate it having a value.
If "[a]ll data types include the null value" then if NULL could represent a value, it would necessarily be a "moving feast" having a different actual byte values depending on data type - and with some data-types, anything which would yield a native binary value, it would make things extremely tricky as you'd need to find some way of differentiating between NULL-as-a-value and, for whatever-happened-to-be-a-genuine-value. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Ramanan-R wrote: |
Hello All,
In PGM1, I'm doing a Left Outer Join between two tables and the result set returns one of the field which is PIC X(10) as X'60606060606060606060' (Got this using HEX ON command in SPUFI for unmatched rows) which is a NOT NULL field as it is a part of Key field....
|
X'60' is an EBCDIC (dash)- and that is just SPUFI' way of showing a null result, it is not what is returned for a null field. With a left outter join you can get a null return for a column that is defined as not null. Suggest you do a lot of reading about db2. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Good man Craq. I have to say I didn't see that coming, as I assumed if it had to be looked at in HEX then it must have contained a non-dusplay value :-)
Next time someone says "C9C4F1F0E3 is what I see in hex" I'll check it :-)
If it is SPUFI doing it, it is not even a relevant value in the Cobol program anyway, so a total red-herring-piece-of-information from TS. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Bill Woodger wrote: |
| If it is SPUFI doing it, it is not even a relevant value in the Cobol program anyway, so a total red-herring-piece-of-information from TS. |
Yes but the TS doesn't know that it is a red-herring. |
|
| Back to top |
|
|
|
|
