Re: Query with NULL
Date: Thu, 31 Dec 2009 08:30:45 -0800 (PST)
Message-ID: <fc824560-14c8-4cf3-b2df-867377610421_at_e37g2000yqn.googlegroups.com>
On Dec 31, 8:33 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Wed, 30 Dec 2009 07:36:19 -0800, Mark D Powell wrote:
> > Being that :wp18 and :wp19 are program bind variables then depending on
> > which variables have a value they are actually 4 different conditions:
> > return all rows when neither variable has a value, return matching rows
> > for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is
> > null, and return only rows that match both wp18 and wp19 when both have
> > a value.
>
> Plus the fact that the job checking whether an external variable is null
> really belongs to the programming interface and not the database.
>
> --http://mgogala.byethost5.com
Since the OP has now informed us the SQL is part of a package and cannot be changed I think the OP's options are limited. Stored outlines might be of use but I think it is unlikely since one possible condition in the SQL as posted requires a full table scan that I do not think there is any way around without changing the SQL.
I suggest complaining to the vendor about the manner in which the SQL is written and its effect on performance. I have some limited experience with DB2 UDB and a liittle more experience with SQL Server so I believe it is safe to say that having the program issue simplier SQL for each desired result set based on program variables having values or not would be more effiicient in all three systems than having to have that check made in the SQL. I think this last is pretty much in agreement with Mgogla's previous post in that some actions belong in the program and some in the database.
HTH -- Mark D Powell -- Received on Thu Dec 31 2009 - 10:30:45 CST