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