select literalt from empty table

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 testquery: select 'hi' from test (constant) ------------- 0 record(s) selected Anyone can give me an explaination? Regards, Michael

"Michael Wimmer" <m.wimmer@lycos.de> 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? > > Regards, The behavior is same with Informix 9.21 also and it makes sense. Select output is driven by the WHERE clause. If there are no rows in the table, select has no meaning. Personally I always write such constants as select 'hi' from systables where tabid = 1. Apart from guaranteeing to work everytime, the output will also be limited to only 1 row. In your example, 'hi' will be repeated for every rows in the table test.

rkusenet wrote: > "Michael Wimmer" <m.wimmer@lycos.de> 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? >> >>Regards, > > > The behavior is same with Informix 9.21 also and it makes sense. > Select output is driven by the WHERE clause. If there are no rows > in the table, select has no meaning. > > Personally I always write such constants as > select 'hi' from systables where tabid = 1. > > Apart from guaranteeing to work everytime, the output will also be > limited to only 1 row. In your example, 'hi' will be repeated for > every rows in the table test. As I said it was a simplification. It was added to the main query with UNION to give an all entry above a select box list. The union made it appear only once unless the table is empty and it does not appear at all. But with your explaination it really does make sense. Regards, Michael > > > >