Re: SQL query re unions and nulls

Don't know about the odbc solution, but this type of Syntax will work for Sybase. Explicitly convert the column to a NULL of the desired type. select a, b, c, d, convert(int, null) from x where ... union select a, b, c, convert(int, null), d from x where... Paul Roberts (proberts@lynx.informix.com) wrote: : 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) -- Remove the '*', if present, to reply to this message +==============================+=============================================+ | David W. Pledger | S T R A T E G I C D A T A S Y S T E M S | | davidp@lexis-nexis.com | PO Box 498, Springboro, OH 45066; (513)743-SDSI | +=========================+==================================================+