ifnull usage

[Denny]

I am doing a left outer join so I am using SELECT FIELDA,FIELDB,IFNULL(FIELDR,0) ....
FIELDR is from the unmatched file.

The question is in the WHERE statement do I have to repeat the if null as in IFNULL(FILELDR,0) <> 0 or can I just say FIELDR <> 0 ?

SQLRPGLE by the way.

[B.Hauser]

Re: ifnull usage

Because a NULL Value is out of the valid range, it must be checked separately (xxx IS NULL or xxx IS NOT NULL).
If you only want the rows whitout a NULL value and without 0 in Column FIELDR, simply specify FIELDR <> 0. The newly created column (i.e. IFNULL(FieldR, 0)) in the select list is not considered.

If you want all records where FieldR is either 0 or NULL, you need to specify:

WHERE (FieldR = 0 or FieldR is NULL)

or

WHERE IfNull(FieldR, 0) = 0

The first version is the better one, because the query optimizer can use an index over FIELDR. In the second version an Index cannot be used.

Birgitta

[BenThurley]

Re: ifnull usage

Note that you can also use the coalesce scalar function instead of ifnull. The ifnull function can only receive two expressions to choose from, whereas, coalesce is the same but can receive as many as you like.

If you were joining several files you could select something like this:

coalesce(fieldfromtable1, fieldfromtable2, fieldfromtable3, 0) 

I always use coalesce now because of the added flexibility.

[Denny]

Re: ifnull usage

Here is the full statement:

 eval      statement = 'select +                           
           PDSTORE,PDNUMBER,PDLOCATION,+                   
           PDDESCRIP,PDTYPE,PDCLASS,PDFEDTAX,+             
           PDWAREHSE,PDWAREHALT,PDVENDOR,PDINVENTRY,+      
           PDACTCOST,PDALLOCATD,PDONORDER,PDONORDIC,+      
           PDMUSALES,PDMURECEVD,PDMUADJUST,PDRECVDATE,+    
           ifnull(qtyrec,0) +                              
           from tmprod +                                   
           left outer join qtemp/pitrp10w +                
           on pdstore = store +                            
           and pdnumber =  prdno +                         
           and pdlocation = location +                     
           where pdstore = ' + %char(pistore) +            
           ' and pddelete = ''A'' +                        
           and pdnoninv <> ''Y'' +                         
           and (pdinventry <> 0 +                          
           or ifnull(qtyrec,0) <> 0) +                     
           order by pdnumber,pdlocation'

I ran this under debug last night for 1 fetch and it seemed to work correctly.

I'll change the Where from ifnull(qtyrec,0) <> 0 to simply qtyrec <> 0.

Brigitta, my old brain still has problems with the null concept.

Rule: NULL is neither equal nor unequal to anything.

So qtyrec <> 0 will NOT select nulls.

[BenThurley]

Re: ifnull usage

If you code in a language that supports null values you can replace all your decimal data errors with null pointer exceptions!

[Denny]

Re: ifnull usage

Not likely, Ben. I am two years from retirement and learning a new language is not in my game plan.