Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to check for NULL in alphanumeric field?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Dec 22, 2011 12:50 pm    Post subject: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7224

PostPosted: Thu Dec 22, 2011 1:42 pm    Post subject: Reply to: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Dec 22, 2011 2:17 pm    Post subject:
Reply with quote

Yes, i can but not sure how to check for a NULL in an alphanumeric variable. Your help is much appreciated...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 2:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Dec 22, 2011 2:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 22, 2011 2:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Dec 22, 2011 5:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Thu Dec 22, 2011 5:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Thu Dec 22, 2011 5:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Dec 22, 2011 6:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7224

PostPosted: Thu Dec 22, 2011 6:10 pm    Post subject: Reply to: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Dec 22, 2011 6:19 pm    Post subject: Re: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7224

PostPosted: Thu Dec 22, 2011 6:50 pm    Post subject: Reply to: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Dec 22, 2011 6:59 pm    Post subject: Re: Reply to: How to check for NULL in alphanumeric field?
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us