nulls in a union
A developer porting an app from Oracle/SQL Server hit a syntax error in Informix with "SELECT col1, NULL, NULL FROM table2" as the second branch of a UNION. The answer: Informix requires the NULLs to be typed, e.g. NULL::VARCHAR / NULL::SMALLINT or CAST(NULL AS VARCHAR). Other suggestions were adding dummy null columns to the table or joining a one-row table of NULLs. The poster resolved it by putting the DBMS-specific detail behind a provider class whose NullColumn() method emits "null::type" for Informix and plain "null" elsewhere.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, IDS newbie returning for guidance!
In an application I am porting to work with Informix I have to adapt a
standard query (currently running on Oracle or Sql Server) to work with
Informix. It takes the form
select col1, col2, col3 from table1
union
select col1, null, null from table2
Table2 does not have a col2 or col3.
There is a good reason for this, and Oracle and Sql Server seem quite happy
with the approach, but not Informix which objects to the second query in the
union, the nulls causing a syntax error. Equally if I leave the 'null' columns
out, it complains that the columns do not match in the union.
How can I get around this?
TIA
Neil.
On Thu, Jul 14, 2011 at 02:01, NEIL HAUGHTON <neil.haughton@autoscribe.co.uk
> wrote:
> In an application I am porting to work with Informix I have to adapt a
> standard query (currently running on Oracle or Sql Server) to work with
> Informix. It takes the form
>
> select col1, col2, col3 from table1
> union
> select col1, null, null from table2>
> Table2 does not have a col2 or col3.
>
> There is a good reason for this, and Oracle and Sql Server seem quite happy
> with the approach, but not Informix which objects to the second query in
> the
> union, the nulls causing a syntax error. Equally if I leave the 'null'
> columns
> out, it complains that the columns do not match in the union.
>
> How can I get around this?
>
This works (tested 11.70):
SELECT Tabid, Owner, TabName FROM informix.SysTables WHERE TabID = 1
UNION
SELECT 1, NULL::VARCHAR, NULL::VARCHAR FROM SysMaster::SysDual;
You need to specify the types of the nulls.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--0015175774a6c2de9904a805d2b7
Thanks - understood.
I was hoping to find a 'neutral' construction that would save me having to
construct the sql conditionally, but it looks like I can't achieve that.
Another solution I found was to add 'null' columns to the second table and
refer to them by name, eg
Create table2
(....
nullint integer,
nullchar nvarchar(50),
...etc
)
and
select col1, col2, col3 from table1
union
select col1, nullint, nullchar from table2
but of course that means I have to amend all three supported databases
(Oracle, Sql Server, Informix).
Neil.
Or just create a one row table with the columns of the correct type in it
and a single row of NULLs and then join that in the second part of the UNION
instead of using the second table used in the first part of the UNION.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Jul 14, 2011 at 7:36 AM, NEIL HAUGHTON <
neil.haughton@autoscribe.co.uk> wrote:
> Thanks - understood.
>
> I was hoping to find a 'neutral' construction that would save me having to
> construct the sql conditionally, but it looks like I can't achieve that.
>
> Another solution I found was to add 'null' columns to the second table and
> refer to them by name, eg
>
> Create table2
> (....
> nullint integer,
> nullchar nvarchar(50),
> ....etc
> )
>
> and
>
> select col1, col2, col3 from table1
> union
> select col1, nullint, nullchar from table2>
> but of course that means I have to amend all three supported databases
> (Oracle, Sql Server, Informix).
>
> Neil.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf307f325a47cc3b04a806d6e5
On Thu, Jul 14, 2011 at 04:36, NEIL HAUGHTON <neil.haughton@autoscribe.co.uk
> wrote:
> I was hoping to find a 'neutral' construction that would save me having to
> construct the sql conditionally, but it looks like I can't achieve that.
>
Do all the DBMS support: CAST(NULL AS VARCHAR)?
Informix does:
SELECT tabid, owner, tabname FROM SysTables WHERE tabid = 1
UNION
SELECT 1, CAST(NULL AS VARCHAR), CAST(NULL AS VARCHAR) FROMsysmaster:sysdual;
[...] but of course that means I have to amend all three supported databases
> (Oracle, Sql Server, Informix).
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--00221532c9dcab879004a806f8ca
Hi Neil,
select a1,b2,c1 from t1
union
select a1, NULL::char(20), NULL::date from t2
is OK for Informix ;-)
Petr
On 07/14/2011 11:01 AM, NEIL HAUGHTON wrote:
> Hi all, IDS newbie returning for guidance!
>
> In an application I am porting to work with Informix I have to adapt a
> standard query (currently running on Oracle or Sql Server) to work with
> Informix. It takes the form
>
> select col1, col2, col3 from table1
> union
> select col1, null, null from table2>
> Table2 does not have a col2 or col3.
>
> There is a good reason for this, and Oracle and Sql Server seem quite happy
> with the approach, but not Informix which objects to the second query in the
> union, the nulls causing a syntax error. Equally if I leave the 'null'
columns
> out, it complains that the columns do not match in the union.
>
> How can I get around this?
>
> TIA
>
> Neil.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
That's a neat idea, better than my 'extra columns' idea I think.
However of all the ideas put forward I'm going to plump for a little bit of
conditional code, hidden inside a 'databaseprovider' class I've knocked up to
handle all these DBMS-specific details. One for Oracle, one for SqlServer, one
for Informix, all behind an interface and constructed by a factory class which
looks at the type of the IDbConnection object I have. Outside the
database-providers the app doesn't care what it is connected to.
The provider has a NullColumn method, which returns a string to substitute
into my sql. For Informix it returns "null::smallint" (or whatever I need),
and for the others it simply returns "null". it all works quite nicely.
eg (this is C# code):
IProvider provider = ProviderFactory.GetProvider(myConn); //get a suitable
provider
comm.CommandText = string.Format("select col1, {0}, {1} from mytable",
provider.NullColumn("nvarchar"), provider.NullColumn("smallint"));
which for Informix gives
select col1, null::nvarchar, null::smallint from mytable
and for the others gives
select col1, null, null from mytable
it's not the most beautiful solution, but it works and it's done now. :-)
Everyone's a winner!
Thanks to all for the suggestions.
Neil