Problem with SQL sub-select and null dates

Hi,
I am having problems with an SQL select where the sub-select yields a null
date value, as the field in the original select does not seem to equate to
the null date. I get no records returned.

The SQL select statement below works fine when there is a date in the
counts_dt field, but does not work when the sub-select yields a null date
value.


SELECT * FROM COUNTS WHERE counts.counts_dt = (SELECT distinct
MAX(isnull(c2.counts_dt,NULL)) FROM counts c2 WHERE c2.property =
counts.property AND c2.unit = counts.unit AND c2.cntitem_id =
counts.cntitem_id AND c2.cnttype_id = counts.cnttype_id)

Any help would be appreciated.
Thanks in advance,
Pauline

Anders
Mon Feb 14 20:58:38 CST 2005

Is this a VFP database or a SQL Server database?
BTW, a MAX value is by definition also DISTINCT.
-Anders

"Pauline Pearce" <PaulinePearce@discussions.microsoft.com> wrote in message
news:3D51F620-CB93-4D78-AC0C-C05588B2D0F2@microsoft.com...
> Hi,
> I am having problems with an SQL select where the sub-select yields a null
> date value, as the field in the original select does not seem to equate to
> the null date. I get no records returned.
>
> The SQL select statement below works fine when there is a date in the
> counts_dt field, but does not work when the sub-select yields a null date
> value.
>
>
> SELECT * FROM COUNTS WHERE counts.counts_dt = (SELECT distinct
> MAX(isnull(c2.counts_dt,NULL)) FROM counts c2 WHERE c2.property =
> counts.property AND c2.unit = counts.unit AND c2.cntitem_id =
> counts.cntitem_id AND c2.cnttype_id = counts.cnttype_id)
>
> Any help would be appreciated.
> Thanks in advance,
> Pauline

PaulinePearce
Tue Feb 15 08:15:06 CST 2005

Sorry, should have said this is for SQL Server data. I have a similar select
for VFP which works fine - but it just has 'c2.counts_dt' instead of
'MAX(isnull(c2.counts_dt,NULL))' in the sub-select.

Any help would be greatly appreciated - we are really stuck with this one.
Pauline

"Anders Altberg" wrote:

> Is this a VFP database or a SQL Server database?
> BTW, a MAX value is by definition also DISTINCT.
> -Anders
>
> "Pauline Pearce" <PaulinePearce@discussions.microsoft.com> wrote in message
> news:3D51F620-CB93-4D78-AC0C-C05588B2D0F2@microsoft.com...
> > Hi,
> > I am having problems with an SQL select where the sub-select yields a null
> > date value, as the field in the original select does not seem to equate to
> > the null date. I get no records returned.
> >
> > The SQL select statement below works fine when there is a date in the
> > counts_dt field, but does not work when the sub-select yields a null date
> > value.
> >
> >
> > SELECT * FROM COUNTS WHERE counts.counts_dt = (SELECT distinct
> > MAX(isnull(c2.counts_dt,NULL)) FROM counts c2 WHERE c2.property =
> > counts.property AND c2.unit = counts.unit AND c2.cntitem_id =
> > counts.cntitem_id AND c2.cnttype_id = counts.cnttype_id)
> >
> > Any help would be appreciated.
> > Thanks in advance,
> > Pauline
>
>

Anders
Tue Feb 15 12:36:12 CST 2005


"Pauline Pearce" <PaulinePearce@discussions.microsoft.com> wrote in message
news:8D802CA6-354F-49DF-84AF-F2542FA9522D@microsoft.com...
> Sorry, should have said this is for SQL Server data. I have a similar
select
> for VFP which works fine - but it just has 'c2.counts_dt' instead of
> 'MAX(isnull(c2.counts_dt,NULL))' in the sub-select.
>

Pauline
It's not possible to stop the subquery from returning a null when none of
the filters fit.
You should be able to pick up the NULL returned by the subquery and convert
it this way:
... WHERE counts_dt= ISNULL(SELECT MAX(counts_dt
FROM Counts C2 WHERE counts_dt ,... AND .. AND) , C1.counts_dt)

-Anders