CODEWITHSUNDEEP
sqlinformix
xecollons
xecollons

Reputation: 608

Is it possible to use Informix NVL with two subqueries?

I want to get a parameter. The priority for getting that parameter is that I have to look for it in Table1, but if it doesn't exist there, I have to look for it in Table2. If not, so that parameter is null (this situation should not happen, but, well, there is always an edge case).

I wanted to try something like this:

SELECT NVL(
  SELECT paramValue from Table1
    where paramName = "paramName" and Id = "id", 
  SELECT paramValue from Table2
    where paramName = "paramName" and Id = "id")

But it gives me a syntax error.

Is there any way of doing something like this?

Upvotes: 2

Views: 426

Answers (2)

Bohemian
Bohemian

Reputation: 425198

There's another way:

SELECT * FROM (
  SELECT paramValue from Table1
  where paramName = "paramName" and Id = "id"
  union all
  SELECT paramValue from Table2
  where paramName = "paramName" and Id = "id"
) x
LIMIT 1

Which is IMHO easier to read.

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 754450

Enclose the sub-queries in their own set of parentheses, like this:

SELECT NVL((SELECT Atomic_Number FROM Elements WHERE Name = 'Tungsten'),
           (SELECT Atomic_Number FROM Elements WHERE Name = 'Helium'))
    FROM sysmaster:informix.sysdual;
74
SELECT NVL((SELECT Atomic_Number FROM Elements WHERE Name = 'Wunderkind'),
           (SELECT Atomic_Number FROM Elements WHERE Name = 'Helium'))
    FROM sysmaster:informix.sysdual;
2
SELECT NVL((SELECT Atomic_Number FROM Elements WHERE Name = 'Wunderkind'),
           (SELECT Atomic_Number FROM Elements WHERE Name = 'Helios'))
    FROM sysmaster:informix.sysdual;
 

The last query generated a NULL (empty line) as output, which is mimicked by a non-breaking space on the last line.

Granted, I'm not selecting from two tables; that's immaterial to the syntax, and the sub-queries would work on two separate tables as well as on one table.

Tested with Informix 12.10.FC6 and CSDK 4.10.FC6 on Mac OS X 10.11.5.

Upvotes: 1

Related Questions