If statement in MS Query

[SteveOPI]

SELECT customermaster_0.CustomerNumber, customermaster_0.CustomerName, Sum(invoicehistoryline_0.LineExtension) AS 'Sales'
FROM VOL01.customermaster customermaster_0 LEFT OUTER JOIN VOL01.invoicehistoryline invoicehistoryline_0 ON customermaster_0.CustomerNumber = invoicehistoryline_0.CustomerNumber}
WHERE (customermaster_0.SalesYearToDate>0) AND (customermaster_0.StoreNumber='03') AND (invoicehistoryline_0.InvoicePostDate>={d '2014-07-01'} And invoicehistoryline_0.InvoicePostDate<={d '2015-03-31'})
GROUP BY customermaster_0.CustomerNumber
ORDER BY customermaster_0.CustomerNumber

I have the query listed above. I would like to use an if statement so that if Sum(invoicehistoryline_0.LineExtension) AS 'Sales' is null, it will return a 0.

I have tried:

ISNULL(Sum(invoicehistoryline_0.LineExtension),0) AS 'Sales'
NZ(Sum(invoicehistoryline_0.LineExtension),0) AS 'Sales'

and neither work.
Any help would be greatly appreciated.

Thanks

 

[Joe4]

If it is similar to SQL, try applying ISNULL on the underlying value, i.e.

[COLOR=#333333]Sum(ISNULL(invoicehistoryline_0.LineExtension,0)) AS 'Sales'[/COLOR]

 

[SteveOPI]

Joe, when I first read your reply, I thought DAH, why didn't I think of that. I tried it and now I get this error.

You have an error in your SQL Syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0) AS 'Sales'

I cut and pasted what you suggested, and have verified spelling etc.

 

[Joe4]

'0) AS 'Sales'

Do you have a single quote before the 0 in that formula?
Try either enclosing it totally on both side with single quotes, i.e. '0', or try no quotes at all, i.e. 0

 

[SteveOPI]

There were no quotes, either single or double. I did try putting single quotes around the 0, but it errorred out also. I try putting spaces on either side of the 0. The result again errorred. This just can't be that hard, but I must be missing something simple and easy.

 

[Joe4]

OK. I just did some Googling and it appears that the ISNULL function are different between SQL and MySQL. It looks like the equivalent of the SQL ISNULL function in MySQL is IFNULL (see: MySQL: IFNULL Function)

So try:

[COLOR=#333333]Sum(IFNULL(invoicehistoryline_0.LineExtension,0)) AS 'Sales'[/COLOR]

 

[SteveOPI]

Joe,

Thanks a lot for all your help. I for the answer from another buddy. I am posting it here in case you are interested.

SELECT customermaster_0.CustomerNumber, customermaster_0.CustomerName, SUM(IF(invoicehistoryline_0.LineExtension IS NULL,0,invoicehistoryline_0.LineExtension))
FROM
VOL01.customermaster customermaster_0
LEFT JOIN
VOL01.invoicehistoryline invoicehistoryline_0
ON customermaster_0.CustomerNumber = invoicehistoryline_0.CustomerNumber
AND (invoicehistoryline_0.InvoicePostDate>={d '2014-07-01'} And invoicehistoryline_0.InvoicePostDate<={d '2015-03-31'})
WHERE (customermaster_0.SalesYearToDate>0) AND (customermaster_0.StoreNumber='03')
GROUP BY customermaster_0.CustomerNumber
ORDER BY customermaster_0.CustomerNumber

Again, thanks a lot for all your help.

 

[Joe4]

Did you see/try my last suggestion?
Looks like it might be a little shorter.

 

[SteveOPI]

Joe,

Yes, I did try it, and I could not get it to work. I got syntax errors when I ran it.

 

[Joe4]

Hmmm... Interesting.
Like I said, I have not programmed directly in MySQL before, so I was really on the articles out there on the internet, and it seems like it should work.

But the important thing is you were to get your problem figured out.