Case When Null or Zero

[GLS400]

Hi All:

I'm trying to test a field for null or zero.
(I'm doing a left join on the file f.fdrptdt comes from)

    d nullfld         s              1a   inz(X'00') 
    d Zerofld         s              1s 0            
*****************
    Case When (frsty * 10000 + frstm * 100 + frstd) Between  
               :FrDate30 and :FrFddYmd  and                  
               f.fdrptdt in (:nullfld, :zerofld)             
                       Then 'Missed First Date'              
                      END AS REASON

I'm getting a sqlcode of -117 (The number of insert or update values is not the same as
the number of columns.) The real error is the nullfld. When I test for only zerofld it works.
I have also tried f.fdrptdt in (null, 0).....No candy

Any assistance would be greatly appreciated

GLS

[Chandrak311]

Re: Case When Null or Zero

try this...

replace:
f.fdrptdt in (:nullfld, :zerofld)

with:
(f.fdrptDt Is null or f.fdrptdt = :zerofld)

[GLS400]

Re: Case When Null or Zero

Thanks ChandraK

That's exactly what I was looking for.
It worked perfectly.

Thanks Again
GLS

[jamief]

Re: Case When Null or Zero

Nice job Chandra -- Now get back to work!

[B.Hauser]

Re: Case When Null or Zero

Just for information a NULL value is NOT x'00' a NULL value is a value out of the valid range.
In a column in a table/phyiscal file a NULL value is simply a flag that is set to *ON or *OFF (or 0 or -1) that can be checked separately.

In SQL you have to check a NULL value with IS NULL or IS NOT NULL in the where conditions.
In embedded SQL you have to add an indicator variable which is defined as small integer (5I 0 in RPG) which follows immediately the host variable to check (simply separted by a blank) and which is set to -1 if a NULL value is returned.
In RPG you have to check or set a NULL value with the built-in-function %NULLIND.

Birgitta

[GLS400]

Re: Case When Null or Zero

Thanks Birgitta:

I'm looking into the null indicator variable now.

Thanks again
GLS

[arrow483]

Re: Case When Null or Zero

Or how about this ?

Case When (frsty * 10000 + frstm * 100 + frstd) Between                  :FrDate30 and :FrFddYmd  and                                  coalesce(f.fdrptdt,0) = 0                                     Then 'Missed First Date'                                     END AS REASON

[FaStOnE]

Re: Case When Null or Zero

You've been missing us .. haven't you Arrow!

[GLS400]

Re: Case When Null or Zero

Arrow:

Now that Jamie's upgraded the site I can't fine the *LIKE button

Thanks
GLS

[arrow483]

Re: Case When Null or Zero

You've been missing us .. haven't you Arrow!

Of course. Actually, I'm been lurking, just not as often as I used to. Now, most times I go to answer a question, someone beats me to it. Y'all are just so fast.