5

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?

BradleyDotNET
  • 60,462
  • 10
  • 96
  • 117
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • For portability reasons, consider creating your own permanent auxiliary base table which is guaranteed to always have exactly one row. – onedaywhen Oct 17 '11 at 10:41
  • @onedaywhen: In some cases, that might be a good idea. In my case, however, I don't have control over the client database, as I'm developing a database abstraction library for Java (http://www.jooq.org) – Lukas Eder Oct 17 '11 at 10:59

2 Answers2

5

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)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Your second solution won't work in my case (my example was somewhat simplified), but the first one will, of course. I'll wait a little longer with accepting though, as I'd prefer a dummy table for syntactic reasons – Lukas Eder Oct 15 '11 at 11:41
0

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)
PaulM
  • 446
  • 2
  • 12