RE: "Merge into" wierdness
- From: Ken Naim <ken@xxxxxxxxxxx>
- To: "'davewendelken@xxxxxxxxxxxxx'" <davewendelken@xxxxxxxxxxxxx>, Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 10 Dec 2004 16:31:31 -0500
You forgot to include that column in your outermost select statement.
So try this query and let me know.
Ken
MERGE INTO laydown l_to
USING
(SELECT l_src_start.*,l_over_under.qty_over_under, unittypeid
FROM laydown l_src_start
,(SELECT l_ou_temp.activityid, l_ou_temp.paiid
,l_ou_temp.orgid, l_ou_temp.unittypeid
,SUM(l_ou_temp.qty) qty_over_under
FROM ( SELECT l_ou.activityid ,l_ou.paiid
,l_ou.orgidfrom orgid ,l_ou.unittypeid
,l_ou.qty * -1 qty
FROM laydown l_ou
WHERE l_ou.analysisversionid = 1
AND l_ou.scenarioid = 3
AND l_ou.orgidfrom IS NOT NULL
UNION ALL
SELECT l_ou.activityid ,l_ou.paiid
,l_ou.orgid ,l_ou.unittypeid
,l_ou.qty
FROM laydown l_ou
WHERE l_ou.analysisversionid = 1 AND l_ou.laydownid
IN (247)
) l_ou_temp
GROUP BY 1,2,3,4
) l_over_under
WHERE l_src_start.laydownid IN (247)
AND l_src_start.analysisversionid = 1
AND l_src_start.activityid = l_over_under.activityid (+)
AND l_src_start.paiid = l_over_under.paiid (+)
AND l_src_start.orgid = l_over_under.orgid (+)
AND l_src_start.unittypeid = l_over_under.unittypeid (+)
) l_src
ON ( l_src.activityid = l_to.activityid
AND l_src.paiid = l_to.paiid
AND l_to.orgid = 16
AND l_to.scenarioid = 3
AND l_to.analysisversionid = 1
AND l_to.orgidfrom = l_src.orgid
/* barfs on the next line */
AND l_to.unittypeid = l_src.unittypeid
)
WHEN MATCHED THEN
UPDATE
SET
qty = DECODE(SIGN(l_src.qty_over_under),-1,qty,NVL(qty,0) +
NVL(l_src.qty_over_under,0))
,activityremark = l_src.activityremark
,unittypeid = l_src.unittypeid
WHEN NOT MATCHED THEN
INSERT
(laydownid,analysisversionid,scenarioid ,paiid
,orgid,activityid ,qty
,activityremark ,orgidfrom
)
VALUES
(laydown_seq.NEXTVAL,1,3 ,l_src.paiid
,16,l_src.activityid
,DECODE(SIGN(l_src.qty_over_under),-1,0,l_src.qty_over_under)
,l_src.activityremark ,l_src.orgid
)
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
