Блог

Следующая функция переходит в бесконечный цикл..

     function ftCustomerCatalog(comno varchar2,cpls varchar2, cuno varchar2) return tblCustomerCatalog pipelined 
is
  c sys_refCursor;
  r recCustomerCatalog;
  sq varchar2(3000);
begin

  sq:='select  
        a.comno
        ,a.t$cpls
        ,coalesce(a.t$cuno,b.t$cuno) as t$cuno
    ,a.t$cpgs,a.t$item
        ,a.t$Upcd
        ,a.t$dsca
        ,a.t$wght
        ,a.t$ship
      ,coalesce(b.t$stdt,c.t$stdt,d.t$stdt,e.t$stdt,f.t$stdt) as T$STDT
      ,coalesce(b.t$tdat,c.t$tdat,d.t$tdat,e.t$tdat,f.t$tdat) as t$tdat
      ,coalesce(b.t$qanp,c.t$qanp,d.t$qanp,e.t$qanp,f.t$qanp) as t$qanp
      ,a.t$pric
      ,coalesce(b.t$disc,c.t$disc,d.t$disc,e.t$disc,f.t$disc) as t$disc
      ,coalesce(b.source,c.source,d.source,e.source,f.source) as Source 
      from  table(edi.ftPlBaseCatalog(:comno,:cpls,:cuno))  a
        left join table(edi.ft30ciDiscounts(:comno,:cpls,:cuno)) b on a.t$item=b.T$item and a.t$cuno=b.t$cuno
        Left Join table(edi.ft31CPGDiscounts(:comno,:cpls,:cuno))  c on a.t$cpgs=c.t$cpgs
        left Join table(edi.ft31Cdiscounts  (:comno,:cpls,:cuno)) d on d.t$cpgs is null
        left join table(edi.ft33plpgDiscounts(:comno,:cpls)) e on d.t$disc is null and a.t$cpgs=e.t$cpgs
        left join table(edi.ft33PlDiscount(:comno,:cpls)) f on e.t$disc is null 

       Order by A.T$CPGS, a.t$item';
  Open c for SQ using

      comno,cpls,cuno
      ,comno,cpls,cuno
      ,comno,cpls,cuno
      ,comno,cpls,cuno
      ,comno,cpls
      ,comno,cpls
      ;
   LOOP
    fetch c into r;
    exit when c%notfound;
    pipe row(r);
  END LOOP;
  close c;

end;
 

Однако, если я выполню его таким образом через сервер MS Sql 2008 R2, он будет работать нормально

 set quoted_identifier off
--/*
select  
        a.comno
        ,a.t$cpls
        ,t$cuno=coalesce(a.t$cuno,b.t$cuno),a.t$cpgs,a.t$item
        ,a.t$Upcd
        ,a.t$dsca
        ,a.t$wght
        ,a.t$ship

      ,t$stdt=coalesce(b.t$stdt,c.t$stdt,d.t$stdt,e.t$stdt,f.t$stdt)
      ,t$tdat=coalesce(b.t$tdat,c.t$tdat,d.t$tdat,e.t$tdat,f.t$tdat)
      ,t$qanp=coalesce(b.t$qanp,c.t$qanp,d.t$qanp,e.t$qanp,f.t$qanp)
      ,a.t$pric
      ,t$disc=coalesce(b.t$disc,c.t$disc,d.t$disc,e.t$disc,f.t$disc)
      ,SOURCE=coalesce(b.source,c.source,c.source,d.source,e.source,f.source) 

      from openQuery(hades,"select * from table(edi.ftAllPlCatalogs('010','145')) where t$cuno='000164'") a
left join  openQuery(hades,"select * from table(edi.ft30ciDiscounts('010','145','000164'))") b on a.t$item=b.T$item and a.t$cuno=b.t$cuno
Left Join OpenQuery(hades,"select * from table(edi.ft31CPGDiscounts('010','145','000164'))") c on a.t$cpgs=c.t$cpgs
left Join openQuery(hades,"select * from table(edi.ft31Cdiscounts  ('010','145','000164'))") d on d.t$cpgs is null
left join openQuery(hades,"select * from table(edi.ft33plpgDiscounts('010','145'))") e on d.t$disc is null and a.t$cpgs=e.t$cpgs
left join openQuery(hades,"select * from table(edi.ft33PlDiscount('010','145'))") f on e.t$disc is null 
order by A.T$CPGS, a.t$item
--*/
--select * from edi.dbo.edicatalog where t$cuno='000164'
 

Что не так?