Re: SQL query re unions and nulls

In article <34159c2e.3795620@news.cableol.co.uk>, Ian Treadgold <itr1@cableol.co.uk> wrote (but I reformated): >What I need is something like the following: > >select a, b, c, d, null > from x > where .... > union >select a, b, c, null, e > from x > where ...> >The problem is how to specify a literal null - if the fields where >char(n) then I could live with blanks or even two quotes together but >that won't work for numeric type fields. > >One last thing, I need a solution that will work on Informix and >Sybase through ODBC. It would be easy to do using a temp table but I don't know whether that fits your Informix+Sybase+ODBC condition (I know next to nothing about Sybase & ODBC). Could you maybe create a two-column, one-row table in the database:- create table my_kludge ( i integer, c char(1) ) ; insert into my_kludge ( c ) values ( "a" ) ; - the point being that my_kludge.i has a single null value - and never change that table. Then your query could be:- select x.a, x.b, x.c, x.d, my_kludge.i from x, my_kludge where .... union select x.a, x.b, x.c, my_kludge.i, x.e from x, my_kludge where ... which should do it. - Paul (not a spokesman)