view & database link

From: Larry Pettit <larry.pettit_at_ps.net>
Date: Tue, 11 Jan 2000 16:11:08 -0700
Message-ID: <KCOe4.124$GX5.2519@news.uswest.net>

I have a view that does a union across a database link. It compiles fine, but errors out on execution;
With the following;

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> /

           AND INVOICE_DATE < to_date('08/01/1999','mm/dd/yyyy')
                                                               *

ERROR at line 4:
ORA-01848: day of year must be between 1 and 365 (366 for leap year) ORA-02063: preceding line from JDE

If I break the view apart and run it separately as two views where the union is it works fine.

Anybody have any suggestions?
Thanks,

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,

                sdlnty type,
                sdlttr cancel
 from
         scor.m_distributor_branch,
         proddta.f42119_at_jde.ores.ps.net,
         proddta.f0101_at_jde.ores.ps.net
 where abac04 = 'C' and
         jde_distributor_seq = aban8 and

  sdivd <> 0 and
   sdlttr not in ('980', '984') and
  sdlnty in ('C','S') and
  sdshan = aban8

      union

 select distributor_branch_seq,
  jde_distributor_seq,
  to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,   sdlitm product_number,
  sdapum uom,
  sdsoqs quantity,

                sdlnty type,
                sdlttr cancel
 from
         scor.m_distributor_branch,

  proddta.f42119_at_jde.ores.ps.net,
  proddta.f0101_at_jde.ores.ps.net
 where abac04 = 'C' and

           jde_distributor_seq = aban8 and   sdivd <> 0 and

                sdlttr not in ('980', '984') and

  sdlnty = 'D' and
  sdshan = aban8

create or replace view vm_clist1
  as
 select distributor_branch_seq,
  jde_distributor_seq,
  to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,   sdlitm product_number,
  sdapum uom,
  sdsoqs quantity,

                sdlnty type,
                sdlttr cancel
 from
         scor.m_distributor_branch,

  proddta.f42119_at_jde.ores.ps.net,
  proddta.f0101_at_jde.ores.ps.net
 where abac04 = 'C' and

           jde_distributor_seq = aban8 and   sdivd <> 0 and

                sdlttr not in ('980', '984') and

  sdlnty in ('C','S') and
  sdshan = aban8;

 create or replace view vm_clist2
  as
 select distributor_branch_seq,
  jde_distributor_seq,
  to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,   sdlitm product_number,
  sdapum uom,
  sdsoqs quantity,

                sdlnty type,
                sdlttr cancel
 from
         scor.m_distributor_branch,

  proddta.f42119_at_jde.ores.ps.net,
  proddta.f0101_at_jde.ores.ps.net
 where abac04 = 'C' and

           jde_distributor_seq = aban8 and   sdivd <> 0 and

                sdlttr not in ('980', '984') and

  sdlnty = 'D' and
  sdshan = aban8; Received on Tue Jan 11 2000 - 17:11:08 CST