Re: Help with Date comparisons

1. Why are you including all these slashes ? Take them out.
2. What is the storage/format of the INVOICE_DATE ?
3. Which language are you running Oracle in ? Or more precisely, which date format does it expect ?

John David Birch, Norway

Larry Pettit <larry.pettit_at_ps.net> wrote in message news:rXHe4.387$7y.5768_at_news.uswest.net...
> I'm getting the following error and can't figure what's happening. It
goes
> across a database
> link.
>
>
> SQL> select * from vm_clist
> 2 where
> 3 INVOICE_DATE >= to_date('07/01/1999','mm/dd/yyyy')
> 4 AND INVOICE_DATE < to_date('08/01/1999','mm/dd/yyyy')
> 5
> SQL> /
> INVOICE_DATE >= to_date('07/01/1999','mm/dd/yyyy')
> *
> ERROR at line 3:
> ORA-02070: database NEWJDE does not support some function in this context
>
>
> Here's some background information.
>
> There are three databases.
>
> Database A
> Database NEWJDE, new copy from a customer
> Database JDE, old copy from a customer
>
> Something has changed in the NEWJDE database that won't allow a date
> comparison in
> a view on a Julian date. If I take the f42119 table which contains the
> invoice date
> column and copy the data to JDE, it works fine.
>
> The above sql statement was executed in Database A on the following view.
>
> create or replace view vm_clist
> as
> select distributor_branch_seq,
> jde_distributor_seq,
> to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,
> sdlitm product_number,
> sdapum uom,
> sdsoqs quantity
> from
> scor.m_distributor_branch,
> proddta.f42119_at_newjde.ores.ps.net,
> proddta.f0101_at_newjde.ores.ps.net
> where abac04 = 'C' and
> jde_distributor_seq = aban8 and
> sdivd <> 0 and
> sdshan = aban8;
>
> If I run the following script in NEWJDE, it works fine. There's just
> something that changed
> in the newjde date format.
>
> select
> to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,
> sdlitm product_number,
> sdapum uom,
> sdsoqs quantity
> from
> proddta.f42119,
> proddta.f0101
> where abac04 = 'C' and
> sdivd <> 0 and
> sdshan = aban8 and
> to_date(to_char(1900000 + sdivd),'yyyyddd') >=
> to_date('07/01/1999','mm/dd/yyyy')
> AND to_date(to_char(1900000 + sdivd),'yyyyddd') <
> to_date('08/01/1999','mm/dd/yyyy')
>
> Any suggestions?
>
> Thanks for your help.
>
>
>
Received on Wed Jan 12 2000 - 06:25:00 CST