How to select zero or one record from a dummy table in Ingres

Question

In most SQL products, I can select from no table or from a dummy table like this:

-- Oracle
SELECT 1 FROM DUAL

-- Many other SQL products (including Ingres)
SELECT 1

Sometimes, I want to add a condition to the above statement, in order to retrieve 0 or 1 record, depending on the condition

-- Oracle
SELECT 1 FROM DUAL WHERE 1 = 0

-- Many other SQL products (but not Ingres)
SELECT 1 WHERE 1 = 0

But the above does not work for Ingres 10.0. How can I do it?

Answer 1

I've not used Ingres but I assume from your question that a FROM is mandatory if there is a WHERE? In that case how about

SELECT 1 FROM (SELECT 1 AS C) AS T WHERE 1 = 0

Or

SELECT CASE WHEN 1 = 0 THEN 1 ELSE 0 END

(The last one will always return a row but allow you to check a condition)

Answer 2

AFAIK the system catalog iidbconstants only has one row, so you could use that. I can't think of a case where there are multiple rows but you may want to add a DISTINCT in case:

select distinct 1 from iidbconstants
Executing . . .


+------+
|col1  |
+------+
|     1|
+------+
(1 row)
continue
* select distinct 1 from iidbconstants where 1 = 0
Executing . . .


+------+
|col1  |
+------+
+------+
(0 rows)