Page 1 of 1

ifnull charecter conversion.

PostPosted: Thu Dec 16, 2010 4:31 pm
by gokulNmf
Hi,

The column PRC_MSTR_ID is char(20)

I am trying to unload some data from db2 using ikject01 util.
While doing so
I first tried with
IFNULL(PRC_MSTR_ID,' ')

in the output file only one byte is replaced and rest of the 19 bytes are nulls.

2nd:
IFNULL(PRC_MSTR_ID,Space(20))

this gives all the 20 byte as spaces.

3rd:
One of my friend suggested like
IFNULL(PRC_MSTR_ID,CHAR(' ')) or CHAR(IFNULL(PRC_MSTR_ID,' '))

both the statement resulted in all the 20 bytes as spaces.

    My doubt is :?
      1)if I give CHAR(' ') only one byte has to converted to character,but how come all the 20 bytes are converted to spaces. I asked him but he is also not aware of how this is happening.

      2)
      CHAR(IFNULL(PRC_MSTR_ID,' ')) is as similar to CHAR(1st statement)
      So is this statement like char(space+19nulls)= 20 spaces?

Thanks in Advance

Re: ifnull charecter conversion.

PostPosted: Thu Dec 16, 2010 6:04 pm
by GuyC
ifnull() has nothing to do with x'00'
ifnull() doesn't replace anything, reread ifnull() in the manual.
also checkout replace()

Re: ifnull charecter conversion.

PostPosted: Thu Dec 16, 2010 7:00 pm
by Robert Sample
in the output file only one byte is replaced and rest of the 19 bytes are nulls.
You completely and totally do not understand the concept of NULL. A NULL is not a character -- it is an indicator that the associated data base variable does not have a value. NULL cannot exist outside a data base -- the term only has meaning when dealing with data bases. Terminology is critical in IT, where similar terms may mean very different things. So far, your lack of knowledge of terminology and misuse of terms is not a good sign.

Research EBCDIC, collating sequence, and learn what the various hex character representations are. X'00' is a valid character on mainframes and is called LOW-VALUES in COBOL. It is never, under any circumstances, referred to as NULL -- languages such as C and Perl use an X'00' as a string termination character (which is not a NULL, by the way).

Re: ifnull charecter conversion.

PostPosted: Wed Dec 22, 2010 6:38 pm
by gokulNmf
:( , At times things have to be learnt in hard way. Thanks Guy and Robert.

Re: ifnull charecter conversion.

PostPosted: Thu Dec 23, 2010 2:13 am
by dick scherrer
At times things have to be learnt in hard way.
Yup. . .

And we have many sticks and "stuff" to smote one about the head and shoulders :)

Don't be discouraged - learning does take a while ;)

d

Re: ifnull charecter conversion.

PostPosted: Thu Dec 23, 2010 10:56 am
by NicC
dick scherrer wrote:learning does take a while


You could say that again - I started in mainframes in 1973 and I am still learning which is why I read every post in every section

Re: ifnull charecter conversion.

PostPosted: Thu Dec 23, 2010 8:45 pm
by GuyC