insert into ...select..有子查询不能用在存储过程里面吗?

[coldljy]

语句:
insert into RESM_SHOULDACCOUNT_DEBT_V(SUPPLIER_ID,SUPPLIER,pre_ACCOUNTED,ACCOUNTED_DR,ACCOUNTED_CR,ACCOUNTED)
select
rapd.vendor_id
,rapd.vendor_name
,((select nvl(sum(rrapd.ACCOUNTED_DR),0)
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_CR is null
and rrapd.TRANSACTION_TYPE= 'RECEIVE'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
)
-
((select nvl(sum(rrapd.ACCOUNTED_CR),0)
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_DR is null
and rrapd.TRANSACTION_TYPE= 'RETURN TO VENDOR'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
)
...


PS/SQL Developer中提示(单独运行是没问题的):
Compilation errors for PROCEDURE RESM.GET_SHOULDACCOUNT_DEBT

Error: PLS-00103: 出现符号 "SELECT"在需要下列之一时：
(-+modnotnullothers
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentexistsmaxminpriorsqlstddev
sumvarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>
Line: 9
Text: ,((select nvl(sum(rrapd.ACCOUNTED_DR),0)

Error: PLS-00103: 出现符号 "-"在需要下列之一时：
;returnreturningandor
Line: 17

[三杯倒]

insert into RESM_SHOULDACCOUNT_DEBT_V(SUPPLIER_ID,SUPPLIER,pre_ACCOUNTED,ACCOUNTED_DR,ACCOUNTED_CR,ACCOUNTED)
select
rapd.vendor_id,rapd.vendor_name,a.aa-b.bb
from rapd, ((select nvl(sum(rrapd.ACCOUNTED_DR),0) aa
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_CR is null
and rrapd.TRANSACTION_TYPE= 'RECEIVE'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
) a,
((select nvl(sum(rrapd.ACCOUNTED_CR),0) bb
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_DR is null
and rrapd.TRANSACTION_TYPE= 'RETURN TO VENDOR'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
) b

[三杯倒]

insert into RESM_SHOULDACCOUNT_DEBT_V(SUPPLIER_ID,SUPPLIER,pre_ACCOUNTED,ACCOUNTED_DR,ACCOUNTED_CR,ACCOUNTED)
select
t.vendor_id,t.vendor_name,a.aa-b.bb
from resm.resm_ap_po_detail t, ((select nvl(sum(rrapd.ACCOUNTED_DR),0) aa
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_CR is null
and rrapd.TRANSACTION_TYPE= 'RECEIVE'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
) a,
((select nvl(sum(rrapd.ACCOUNTED_CR),0) bb
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_DR is null
and rrapd.TRANSACTION_TYPE= 'RETURN TO VENDOR'
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
) b

[LGQDUCKY]

既然写在存储过程里，何必要写在一起呢？
分开写不是清晰点吗？

[beckhambobo]

insert into RESM_SHOULDACCOUNT_DEBT_V(SUPPLIER_ID,SUPPLIER,pre_ACCOUNTED,ACCOUNTED_DR,ACCOUNTED_CR,ACCOUNTED)
select
rapd.vendor_id
,rapd.vendor_name
,((select sum(decode(rrapd.TRANSACTION_TYPE,'RECEIVE',rrapd.ACCOUNTED_DR))-sum(decode(rrapd.TRANSACTION_TYPE,'RETURN TO VENDOR',rrapd.ACCOUNTED_DR))
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_CR is null
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
)
...

[beckhambobo]

insert into RESM_SHOULDACCOUNT_DEBT_V(SUPPLIER_ID,SUPPLIER,pre_ACCOUNTED,ACCOUNTED_DR,ACCOUNTED_CR,ACCOUNTED)
select
rapd.vendor_id
,rapd.vendor_name
,((select sum(decode(rrapd.TRANSACTION_TYPE,'RECEIVE',rrapd.ACCOUNTED_DR))-sum(decode(rrapd.TRANSACTION_TYPE,'RETURN TO VENDOR',rrapd.ACCOUNTED_DR))
from resm.resm_ap_po_detail rrapd
where rrapd.SUPPLIER_ID = rapd.vendor_id
and rrapd.org_id = rapd.org_id
and rrapd.ACCOUNTED_CR is null
and rrapd.gl_date < TRUNC(to_date('2003-9-1'),'mm')
)
...