Re: select literalt from empty table
Michael Wimmer wrote:
> Hi,
>
> I just encountered some strange behaviour. Informix 9.4 trial.
>
> I select a constant and do an union with a query.
>
> If the table is empty, I get no rows. If it has at least on entry I get
> the expected result.
>
> Simplified:
>
> table has an entry:
> query: select 'hi' from test
> result: (constant)
> -------------
> hi
> 1 record(s) selected
>
>
> table is empty
> delete from test> query: select 'hi' from test
> (constant)
> -------------
> 0 record(s) selected
>
> Anyone can give me an explaination?
Well I'm not going to explain the workings of SQL, but suffice to say that
you have requested a constant for every row that satisfies your WHERE clause.
If you want to turn it on it's head and select a single constant every
time, then make sure you define a SELECT statement that always returns a
single row, like:
SELECT 'hi'
FROM systables
WHERE tabid = 1
Also be careful with UNION, as it will remove duplicates by default. So if
your WHERE clause was wrong, you could be doing a lot of extra processing,
which could be masked by the effect of UNION.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
sending to informix-list