Hi all,
I connect my BOT server to a remote DB server(Oracle) and trying to execute the below query using execute query activity. I’m getting an error like
ERROR [42S02] [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
Please advice me
My query is
select b.CMPNAMEEN,a.INVOICENO,to_char(a.OPENDATE,'DD/MM/YYYY'),f.INVOTITLE,f.telno,f.MOBILEPHONE,f.CONTEMAIL,a.PONO,g.COSTCENTER,
n.consultant,c.lastname||’ ‘||c.firstname as name,a.SALYYMM,
a.SALACCNO,a.SALACCNAME,(1-2*a.adcode)a.AFTERBILLINGAMOUNT,a.MASFLAG,h.MASNO
From bi501m a
left join cu002m b on a.TOPBCLIENTID=b.seqno
left join as001m c on a.candseqno=c.candseqno
left join bi503m d on a.INVOICENO=d.invoiceno
left join ct007m e on a.PRCTNO=e.prctno and a.sbctseqno=e.sbctseqno
left join cu002d f on e.PERSONIINVNO=f.SEQNO
left join cu013D g on e.CCSEQNO=g.SEQNO
left join bi507m h on a.INVOICENO=h.INVOICENO
left join
(
select prctno,sbctseqno,listagg(to_char(sbctname),‘,’) within group (order by sbctname) as consultant
from ct007md group by prctno,sbctseqno
) n on a.prctno=n.prctno and a.sbctseqno=n.sbctseqno
where a.opendate>=to_date(‘2018-01-16’,‘YYYY-MM-DD’) and a.opendate<=to_date(‘2018-03-15’,‘YYYY-MM-DD’)
and d.INVOICENOTYPE like ‘%013%’ and a.AFTERBILLINGAMOUNT!=‘0’
union all
select b.CMPNAMEEN,a.INVOICENO,to_char(a.OPENDATE,‘DD/MM/YYYY’),f.INVOTITLE,f.telno,f.MOBILEPHONE,f.CONTEMAIL,a.PONO,g.COSTCENTER,
n.consultant,c.lastname||’ '||c.firstname as name,a.salyymm,
a.SALACCNO,a.SALACCNAME,(1-2a.adcode)a.AFTERTLAMT,a.MASFLAG,h.MASNO
From bi501m a
left join cu002m b on a.TOPBCLIENTID=b.seqno
left join as001m c on a.candseqno=c.candseqno
left join bi503m d on a.INVOICENO=d.invoiceno
left join ct007m e on a.PRCTNO=e.prctno and a.sbctseqno=e.sbctseqno
left join cu002d f on e.PERSONIINVNO=f.SEQNO
left join cu013D g on e.CCSEQNO=g.SEQNO
left join bi507m h on a.INVOICENO=h.INVOICENO
left join
(
select prctno,sbctseqno,listagg(to_char(sbctname),‘,’) within group (order by sbctname) as consultant
from ct007md group by prctno,sbctseqno
) n on a.prctno=n.prctno and a.sbctseqno=n.sbctseqno
where a.opendate>=to_date(‘2018-01-16’,‘YYYY-MM-DD’) and a.opendate<=to_date(‘2018-03-15’,‘YYYY-MM-DD’)
and d.INVOICENOTYPE not like ‘%013%’ and a.AFTERtlamt!=‘0’
–order by INVOICENO,name,SALACCNO
union all
select b.CMPNAMEEN,a.INVOICENO,to_char(a.INVOICEDATE,‘DD/MM/YYYY’),f.INVOTITLE,f.telno,f.MOBILEPHONE,f.CONTEMAIL,a.PONO,g.COSTCENTER,
n.consultant,NULL,NULL,
NULL,a.REMARKNAME,(1-2h.adcode)*a.tlamt,a.MASFLAG,i.MASNO
From bi001m a
left join cu002m b on a.TOPBCLIENTID=b.seqno
–left join as001m c on a.candseqno=c.candseqno
left join bi503m d on a.INVOICENO=d.invoiceno
left join ct007m e on a.PRCTNO=e.prctno and a.sbctseqno=e.sbctseqno
left join cu002d f on e.PERSONIINVNO=f.SEQNO
left join cu013D g on e.CCSEQNO=g.SEQNO
left join pr001m h on a.SALACCNO=h.SALACCNO
left join bi507m i on a.INVOICENO=i.INVOICENO
left join
(
select prctno,sbctseqno,listagg(to_char(sbctname),’,‘) within group (order by sbctname) as consultant
from ct007md group by prctno,sbctseqno
) n on a.prctno=n.prctno and a.sbctseqno=n.sbctseqno
where a.INVOICEDATE>=to_date(‘2018-01-16’,‘YYYY-MM-DD’) and a.INVOICEDATE<=to_date(‘2018-03-15’,‘YYYY-MM-DD’)
and a.SALACCNO!=‘test’
–and d.INVOICENOTYPE not like ‘%013%’ and a.AFTERtlamt!=‘0’
union all
select b.CMPNAMEEN,a.INVOICENO,to_char(a.INVOICEDATE,‘DD/MM/YYYY’),f.INVOTITLE,f.telno,f.MOBILEPHONE,f.CONTEMAIL,a.PONO,g.COSTCENTER,
n.consultant,NULL,NULL,
NULL,a.REMARKNAME,a.tlamt,a.MASFLAG,i.MASNO
From bi001m a
left join cu002m b on a.TOPBCLIENTID=b.seqno
–left join as001m c on a.candseqno=c.candseqno
left join bi503m d on a.INVOICENO=d.invoiceno
left join ct007m e on a.PRCTNO=e.prctno and a.sbctseqno=e.sbctseqno
left join cu002d f on e.PERSONIINVNO=f.SEQNO
left join cu013D g on e.CCSEQNO=g.SEQNO
left join pr001m h on a.SALACCNO=h.SALACCNO
left join bi507m i on a.INVOICENO=i.INVOICENO
left join
(
select prctno,sbctseqno,listagg(to_char(sbctname),’,') within group (order by sbctname) as consultant
from ct007md group by prctno,sbctseqno
) n on a.prctno=n.prctno and a.sbctseqno=n.sbctseqno
where a.INVOICEDATE>=to_date(‘2018-01-16’,‘YYYY-MM-DD’) and a.INVOICEDATE<=to_date(‘2018-03-15’,‘YYYY-MM-DD’)
and a.SALACCNO=‘test’
order by INVOICENO