Help with Date comparisons

From: Larry Pettit <larry.pettit_at_ps.net>
Date: Tue, 11 Jan 2000 08:35:41 -0700
Message-ID: <QXHe4.388$7y.5835@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 Tue Jan 11 2000 - 09:35:41 CST