RE: "Merge into" wierdness

From: Ken Naim <ken_at_clubmom.com>
Date: Fri, 10 Dec 2004 16:31:31 -0500
Message-ID: <EA052B73AC607549BCB8BFEF4747DA2103C0@cmiechange>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 10 2004 - 15:55:59 CST