Null problem 1

[DanNorris2000]

I am trying to create a summary file from 2 files(parent/child)
select pf245.pc, pf245.st, pf245.lob, sum(pf245.lr) as LR1, sum(pf245.lx) as LX1,;
sum(dirsum.lr)as LR,;
sum(dirsum.lx)as LX,;
sum(dirsum.pw)as PW,;
sum(dirsum.lp)as LP,;
sum(dirsum.le)as LE;
From MRCDATA!pf245 left outer join MRCDATA!dirsum;
ON pf245.pc = dirsum.pc and;
pf245.st = dirsum.st and pf245.lob = dirsum.lob;
group by pf245.pc, pf245.st, pf245.lob;
into cursor tmp245sum

when a currency amount doesnt exist in the dirsum file I get .null. as a result.
I tried this:
iif(isnull(sum(dirsum.lr)),0,sum(dirsum.lr)) as LR,;
but it changed everything to whole numbers or an "*"(what is that?)
thanks

 

[Neil Toulouse]

I think you need the NVL() function

SUM(NVL(dirsum.lr),0))

Neil "I like work. It fascinates me. I can sit and look at it for hours..."

 

[Neil Toulouse]

Actually if it's currency you may need to add some decimals:

SUM(NVL(dirsum.lr),0.0000)) "I like work. It fascinates me. I can sit and look at it for hours..."

 

[DanNorris2000]

got error too few arguments?
unbalanced parentheses?

 

[rgbean]

Try:

SUM(NVL(dirsum.lr, 0.0000))

Rick

 

[DanNorris2000]

Thank did the job, thanks to all
Dan

 

[Neil Toulouse]

Apologies, brackets in the wrong place!

Neil "I like work. It fascinates me. I can sit and look at it for hours..."