DCOracle syntax question
Andy Todd
andy47 at halfcooked.com
Sun Feb 17 20:34:05 EST 2002
"Jim St.Cyr" <jstcyr at mediaone.net> wrote in news:Wvmb8.659$jj4.576986
@typhoon.ne.ipsvc.net:
> I have a query of the form
>
> select * from table where key = 'key' and date = (select max(to_char
(date))
> from table where key = 'key')
>
> Through SQL*Plus this executes fine. When executing through DCOracle I
get
> an empty result. I've tried both tag = 'query' and cursor.execute
(query).
> I think that either the subquery or the inline quotes is throwing things
off
> but am at a loss.
>
> Any pointers would be appreciated.
>
> Thanks.
>
> Jim
>
>
>
Jim,
You are being confused by data type conversions. The problem (probably)
lies in your sub-query;
AND date = (select max(to_char(date)) from table where key = 'key')
If the column 'date' is actually a DATE datatype column then comparing it
to a to_char'ed version of 'date' will not necessarily equate. What you are
seeing is that SQL*Plus plays nice and manages to match the values in
'date' successfully but whatever other tool you are using is much stricter
and doesn't.
Do you *need* to have that 'to_char' in your sub-query? I can't see any
need for it. If you re-write your query as;
SELECT *
FROM table
WHERE key = 'key'
AND date = (select max(date)
from table
where key = 'key')
everything should be fine. Of course if the column 'date' is not a DATE
datatype column then my advice is invalid ...
Regards,
Andy
--
Smiggins Hole 2010 - Unleash the Mongrel
More information about the Python-list
mailing list