Want NULL returned from SQL query if no values were found

Archived Topics
1

We are using the SQL query:
Select tank from lookuptanks where projectedTank = ‘{Root Container.TankSelected}’

The problem is that sometimes there is no value in TankSelected, so there is nothing returned from SQL. We are looking for NULL or a tank number from this query. I tried using the Fallback Value and putting null in there, but it returned the string ‘null’ instead. Is there a good way to get a null back?

2

No, there is no way to specify a “null” fallback value. Either use an invalid tank number (e.g. -1) as a fallback, or write a query like this:

(SELECT tank FROM lookuptanks WHERE projectedTank = '{Root Container.TankSelected}') UNION (SELECT null)

This way, if the first query returns no rows, you’ll get the result from the second query, but if the first query returns a value, you’ll get that. This works because binding a scalar value (anything but a DataSet) to a query uses column 0 row 0 from the result.

3

Thanks. That was the solution we had come up with too, but thought perhaps there was a better way. It worked just fine though.
Thanks,
Ron