Prev: Trouble with UTL_DBWS / SOAPACTION_URI
Next: some errors when using impdp
From: anil4yesu on 28 Feb 2007 16:16


The following SQL works well without the last line, once I add the
last line, I get ORA-01858.
I am not sure why it works in the SELECT but not in the WHERE clause.

We have Oracle 9.2.0.7.

select msg, source, msg_type, svr_cd,
to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10), 'mm/dd/yyyy')
from hotlist
where fnctn = 'ROUTE'
and source = 'SRF Data'
and (sysdate - 30) > to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10),
'mm/dd/yyyy')

Any suggestions/thoughts are appreciated

Thanks

From: Maxim Demenko on 28 Feb 2007 17:40
anil4yesu(a)gmail.com schrieb:
>
> The following SQL works well without the last line, once I add the
> last line, I get ORA-01858.
> I am not sure why it works in the SELECT but not in the WHERE clause.
>
> We have Oracle 9.2.0.7.
>
> select msg, source, msg_type, svr_cd,
> to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10), 'mm/dd/yyyy')
> from hotlist
> where fnctn = 'ROUTE'
> and source = 'SRF Data'
> and (sysdate - 30) > to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10),
> 'mm/dd/yyyy')
>
> Any suggestions/thoughts are appreciated
>
> Thanks
>

Probably because there are values which don't result in proper date
format 'mm/dd/yyyy'.
You can try one of both approaches:

select /*+ ORDERED_PREDICATES */ msg, source, msg_type, svr_cd,
to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10), 'mm/dd/yyyy')
from hotlist
where fnctn = 'ROUTE'
and source = 'SRF Data'
and (sysdate - 30) > to_date(substr(msg,instr(msg,'Eff Dt:') +
8,10),'mm/dd/yyyy')

or

select /*+ NO_MERGE */ msg, source, msg_type, svr_cd,
to_date(substr(msg,instr(msg,'Eff Dt:') + 8,10), 'mm/dd/yyyy')
from
(select * from
hotlist
where fnctn = 'ROUTE'
and source = 'SRF Data')
where (sysdate - 30) > to_date(substr(msg,instr(msg,'Eff Dt:') +
8,10),'mm/dd/yyyy')


Best regards

Maxim
 | 
Pages: 1
Prev: Trouble with UTL_DBWS / SOAPACTION_URI
Next: some errors when using impdp