...where max(date) is null
A user on IDS 11.10.UC3 (AIX/HP-UX/Solaris) wanted an Oracle-style query where an IS NULL test is applied to a scalar subquery (e.g. MAX(date1)), falling back to a second date column when the first is null. Informix rejected it with "IS [NOT] NULL predicate may be used only with simple columns." Replies noted this works from IDS 11.50 onward. Two workarounds were given: wrap the MAX() in NVL() and compare to a dummy value, or (Art Kagel's suggestion, which the poster adopted) move the MAX() into a derived table in the FROM clause and test that column for NULL.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I'm facing a problem that is i have to do a request on a table that contains
two date type columns.
If the first date column values of my request are null i have to check on the
second one.
With oracle, I can do this in one request ... but on Informix, i've got the
"IS [NOT] NULL predicate may be used only with simple columns." error.
A request example is sure better to understand the case :
Table struct : TB_EX(lib char(50), date1(DATETIME), date2(DATETIME), crit1
char(1))
SELECT lib FROM tb_ex
WHERE crit1='Y'
AND (
date1=(SELECT max(date1) FROM tb_ex WHERE crit1='Y')
AND date1 > "01.01.2010"
OR(
(SELECT max(date1) FROM tb_ex WHERE crit1='Y' AND date1 > "01.01.2010") IS NULL
AND date2 <=today
)
This request works fine on Oracle, but not on informix because the "is null"
predicate can not be applied on something else than a simple column :(
Is there an other way to do this job with just one request, or should i be
resigned to do this with two requests ?
Thank you for your reading and interest.
Yann DELANOE
What exact version of Ids are you running?
This should work on IDS 11.70
Khaled Bentebal de mon portable
Le 30 mai 2011 à 10:43, "YANN DELANOE" <yann.delanoe@sterci.com> a écrit :
> Hi,
>
> I'm facing a problem that is i have to do a request on a table that contains
> two date type columns.
> If the first date column values of my request are null i have to check on the
> second one.
>
> With oracle, I can do this in one request ... but on Informix, i've got the
> "IS [NOT] NULL predicate may be used only with simple columns." error.
>
> A request example is sure better to understand the case :
>
> Table struct : TB_EX(lib char(50), date1(DATETIME), date2(DATETIME), crit1
> char(1))
>
> SELECT lib FROM tb_ex>
> WHERE crit1='Y'
>
> AND (
>
> date1=(SELECT max(date1) FROM tb_ex WHERE crit1='Y')
>
> AND date1 > "01.01.2010"
>
> OR(
>
> (SELECT max(date1) FROM tb_ex WHERE crit1='Y' AND date1 > "01.01.2010") IS
> NULL
>
> AND date2 <=today
>
> )
>
> This request works fine on Oracle, but not on informix because the "is null"
> predicate can not be applied on something else than a simple column :(
>
> Is there an other way to do this job with just one request, or should i be
> resigned to do this with two requests ?
>
> Thank you for your reading and interest.
> Yann DELANOE
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi Khaled, We re using IDS version 11.10.UC3.
HI Yann, This should work starting IDS 11.50. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 30/05/11 11:03, YANN DELANOE a écrit : > Hi Khaled, > > We re using IDS version 11.10.UC3. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
OK, i can't change our version for that query :) I will do it with 2 queries. Thanks for your answers. Bonne journée ;)
PLEASE POST VERSION AND PLATFORM DETAIL information when you post. The
answer to your question will be different for earlier and later versions of
Informix.
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 Mon, May 30, 2011 at 4:43 AM, YANN DELANOE <yann.delanoe@sterci.com>wrote:
> Hi,
>
> I'm facing a problem that is i have to do a request on a table that
> contains
> two date type columns.
> If the first date column values of my request are null i have to check on
> the
> second one.
>
> With oracle, I can do this in one request ... but on Informix, i've got the
> "IS [NOT] NULL predicate may be used only with simple columns." error.
>
> A request example is sure better to understand the case :
>
> Table struct : TB_EX(lib char(50), date1(DATETIME), date2(DATETIME), crit1
> char(1))
>
> SELECT lib FROM tb_ex>
> WHERE crit1='Y'
>
> AND (
>
> date1=(SELECT max(date1) FROM tb_ex WHERE crit1='Y')
>
> AND date1 > "01.01.2010"
>
> OR(
>
> (SELECT max(date1) FROM tb_ex WHERE crit1='Y' AND date1 > "01.01.2010") IS
> NULL
>
> AND date2 <=today
>
> )
>
> This request works fine on Oracle, but not on informix because the "is
> null"
> predicate can not be applied on something else than a simple column :(
>
> Is there an other way to do this job with just one request, or should i be
> resigned to do this with two requests ?
>
> Thank you for your reading and interest.
> Yann DELANOE
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5186af4a5557c04a47df96f
In 11.10, try this:
SELECT lib
FROM tb_ex, (
SELECT max(date1) as date_mx FROM tb_ex WHERE crit1='Y') as tb_mx )
WHERE crit1='Y'
AND date1 = date_mx
AND (date_mx > "01.01.2010" OR date_mx IS NULL)
AND date2 <= today;
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 Mon, May 30, 2011 at 4:43 AM, YANN DELANOE <yann.delanoe@sterci.com>wrote:
> Hi,
>
> I'm facing a problem that is i have to do a request on a table that
> contains
> two date type columns.
> If the first date column values of my request are null i have to check on
> the
> second one.
>
> With oracle, I can do this in one request ... but on Informix, i've got the
> "IS [NOT] NULL predicate may be used only with simple columns." error.
>
> A request example is sure better to understand the case :
>
> Table struct : TB_EX(lib char(50), date1(DATETIME), date2(DATETIME), crit1
> char(1))
>
> SELECT lib FROM tb_ex>
> WHERE crit1='Y'
>
> AND (
>
> date1=(SELECT max(date1) FROM tb_ex WHERE crit1='Y')
>
> AND date1 > "01.01.2010"
>
> OR(
>
> (SELECT max(date1) FROM tb_ex WHERE crit1='Y' AND date1 > "01.01.2010") IS
> NULL
>
> AND date2 <=today
>
> )
>
> This request works fine on Oracle, but not on informix because the "is
> null"
> predicate can not be applied on something else than a simple column :(
>
> Is there an other way to do this job with just one request, or should i be
> resigned to do this with two requests ?
>
> Thank you for your reading and interest.
> Yann DELANOE
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5171eb150fc2404a47e1679
Sorry to not have posted version and OS
It's IDS v11.10 UC3 on AIX/HPUX/SunOS
I've found a solution in fact, using NVL :
SELECT lib FROM tb_ex
WHERE crit1='Y'
AND (
date1=(SELECT max(date1) FROM tb_ex WHERE crit1='Y')
AND date1 > "01.01.2010"
OR(
(NVL(SELECT max(date1) FROM tb_ex WHERE crit1='Y' AND date1 >
"01.01.2010"),'dummy)='dummy')
AND date2 <=today
)
Thank you all
Thank you Art, this query is far more beautiful (and surely more efficient)
than mine. My SQL is poor ....
Thank you
In final my request looks like this (surely it's not once the best we can
found) ... i will have to test it on multiple case to be sure she gives me
what is expected :)
SELECT release FROM tb_ntw_release rel, tb_ntw_type typ,
(SELECT MAX(rel.customer_cutover_date) as date_cust FROM TB_NTW_RELEASE rel,
TB_NTW_TYPE typ WHERE rel.type_id = typ.id AND
rel.customer_cutover_date <= DATETIME(2011-05-30 09:38:49) YEAR TO SECOND AND
typ.network = 'ABC') as tb_cust
WHERE rel.type_id = typ.id AND typ.network = 'ABC' AND
rel.customer_cutover_date = date_cust
OR (date_cust is null
AND rel.network_cutover_date = ( SELECT MAX(rel.network_cutover_date) FROM
TB_NTW_RELEASE rel, TB_NTW_TYPE typ
WHERE rel.type_id = typ.id AND rel.network_cutover_date <= DATETIME(2011-05-30
07:38:49) YEAR TO SECOND) AND typ.network = 'ABC' )
Once again thank you for your help.
Yann DELANOE