1

I'm trying to run the below query using an Excel Workbook as a data source, but I don't get why the COALESCE function won't work. Is there anything wrong with the query or the COALESCE function doesn't work with Excel?

SELECT COALESCE([net value], 0) 
FROM   [sheet1$a1:e6] 
WHERE  [units] = 'UN03' 
UNION ALL 
SELECT COALESCE([net value], 0) 
FROM   [sheet1$a1:e6] 
WHERE  [units] = 'UN06' 
UNION ALL 
SELECT COALESCE([net value], 0) 
FROM   [sheet1$a1:e6] 
WHERE  [units] = 'UN04' 
UNION ALL 
SELECT COALESCE([net value], 0) 
FROM   [sheet1$a1:e6] 
WHERE  [units] = 'UN02' 
UNION ALL 
SELECT COALESCE([net value], 0) 
FROM   [sheet1$a1:e6] 
WHERE  [units] = 'UN01'

It seems that the only thing works is the query below, but I can't return the output as the IFF function only accepts values inside single quotes, for example, 'true', 'false'. I'm trying to return these values sheet1$.net value, '0', the sheet1$.net value contains the query output and the 0 is an indication the query didn't return anything. Can anyone tell what I'm doing wrong?

SELECT IIF(Count(`sheet1$`.`net value`) > 0, `sheet1$`.`net value`, '0') AS 
'Net Value' 
FROM   `sheet1$` `Sheet1$` 
WHERE  ( `sheet1$`.f1 = 'UN03' )

enter image description here

Tony Batista
  • 177
  • 2
  • 13
  • 1
    Which provider are you using: Jet, ACE,...? Have you tried `IFNULL` instead of `COALESCE`? – Andrew Morton Aug 30 '18 at 15:03
  • COALESCE is used for lists or multiple columns, if you want to change / check one item, use IsNull, as in `SELECT IsNull([net value], 0)` – JonTout Aug 30 '18 at 15:09
  • Hey Andrew, I'm using Microsoft.ACE.OLEDB.12.0. I'd tried IFNULL and ISNULL as well. – Tony Batista Aug 30 '18 at 15:13
  • Not sure if this is helpful to mention, but you shouldn't bother to alias a table to its own name: ``sheet1$` `Sheet1$`` – n8. Aug 31 '18 at 15:50

1 Answers1

0

You should write the query as though writing for MS Access. Please reference this, where a number of alternatives are available for your concern:

coalesce alternative in Access SQL

n8.
  • 1,732
  • 3
  • 16
  • 38
  • Hey @n8, I'm using "IIF(Count([net value]) > 0,[net value], 0)," but it just doesn't work. – Tony Batista Aug 31 '18 at 14:26
  • If you're doing an aggregate function in the criteria then you need to group by and include the aggregate in the output, like `IIF(Count([net value]) > 0,Count([net value]), 0)`, or don't aggregate like `IIF([net value] > 0,[net value], 0)`. Mixing those is probably giving you results you don't expect. – n8. Aug 31 '18 at 15:48
  • Did you try Nz? – n8. Aug 31 '18 at 15:59
  • Hey I'd tried "IIF([net value] > 0,[net value], 0)" before but I'm getting the same result; the zero is not being reeturned. – Tony Batista Aug 31 '18 at 19:26
  • What is Nz? Is it another provider? – Tony Batista Aug 31 '18 at 19:27
  • 1
    Nz is an Access (vba) function ... `Nz ( variant, [ value_if_null ] )` – Dessma Aug 31 '18 at 19:40