ifnull charecter conversion.



IBM's flagship relational database management system

ifnull charecter conversion.

Postby gokulNmf » Thu Dec 16, 2010 4:31 pm

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
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 114
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 0 time
Been thanked: 0 time

Re: ifnull charecter conversion.

 

Re: ifnull charecter conversion.

Postby GuyC » Thu Dec 16, 2010 6:04 pm

ifnull() has nothing to do with x'00'
ifnull() doesn't replace anything, reread ifnull() in the manual.
also checkout replace()
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: ifnull charecter conversion.

Postby Robert Sample » Thu Dec 16, 2010 7:00 pm

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).
Robert Sample
Global moderator
 
Posts: 3443
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 242 times

Re: ifnull charecter conversion.

Postby gokulNmf » Wed Dec 22, 2010 6:38 pm

:( , At times things have to be learnt in hard way. Thanks Guy and Robert.
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 114
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 0 time
Been thanked: 0 time

Re: ifnull charecter conversion.

Postby dick scherrer » Thu Dec 23, 2010 2:13 am

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
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 92 times

Re: ifnull charecter conversion.

Postby NicC » Thu Dec 23, 2010 10:56 am

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
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2751
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 110 times

Re: ifnull charecter conversion.

Postby GuyC » Thu Dec 23, 2010 8:45 pm

GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post