Re: SQL query re unions and nulls
Ian Treadgold wrote:
>
> I'd really appreciate some help with this particular SQL problem I've
> been landed with. I need to write two selects joined by a union to
> deliver separate subsets of columns.
>
> Consider a table x with columns a,b,c,d,e all of which are type
> integer. Depending on various entries in the where clauses, I need
> columns a,b,c,d from the first select and a,b,c,e from the second but
> I need all five columns in total.
>
> 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.
>
> Thanks in advance if anyone can help
Ian,
I have had exactly this problem too. If you had read my technical tips
in the recent Informix User (Infuse) news in the UK you would have seen
this. See http://www.infuse.co.uk for more.
In the absence of the NULL keyword in Informix you need a typed null.
Write a SPL procedure like this:
create procedure null_date()
returning date; define global null_date date default null;
return null_date;
end procedure
document
'null_date takes no arguments and returns a null of type date.'
with listing in 'null_date.err'
;
Include the function call null_date() in your select list.
This would work for numbers if suitably amended - I just haven't needed
it yet so it's not in my SPL library yet.
Peter
--
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258, Fax: +44-1635-562281
Mail: Peter.Lancashire.PL1@bayer.co.uk
My Internet plumbing does not allow me to mail and post news together.
Sorry.
All opinions are my own and not those of Bayer plc.