Home » SQL & PL/SQL » SQL & PL/SQL » how to write update statement for this (oracle 10g)
how to write update statement for this [message #437371] Mon, 04 January 2010 02:04 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Below are the table and records in the table.

below query updates the AMOUNT and EXCHANGERATE where STATUS is PART_INVOICED and AMOUNT <> 0 and TRANS_AMOUNT = 0;
along with updating these 2 columns i also want to update first detail sequence status with 'INVOICED';

if i put like this JC1.STATUS = 'INVOICED' it will update all DET_SEQ's statues with 'INVOICED' but i want to update only 1st
detail sequence status and remaining detail sequence statuses should not be changed.


CREATE TABLE CHARGES
(
DOCUMENT_ID VARCHAR2(15),
MAIN_SEQ  NUMBER,
DET_SEQ NUMBER,
TRANS_AMOUNT NUMBER(10,2),
AMOUNT NUMBER(10,2),
EXCHANGERATE  NUMBER(10,2), 
STATUS  VARCHAR2(15)
)

INSERT INTO charges values('11753',8,1,3.84,-2.91,65.68,'PART_INVOICED');
INSERT INTO charges values('11753',8,2,0,0.00,65.68,'INVOICED');
INSERT INTO charges values('11753',8,3,-3.84,-268.42,69.90,'ADJUSTMENT');

INSERT INTO charges values('13238',13,1,0.08,0.00,64.32,'PART_INVOICED');
INSERT INTO charges values('13238',13,2,0,0.00,64.32,'INVOICED');
INSERT INTO charges values('13238',13,3,-0.08,-5.61,70.18,'ADJUSTMENT');

UPDATE CHARGES JC1
   set JC1.AMOUNT       = JC1.TRANS_AMOUNT *
                          (SELECT JCC.EXCHANGERATE
                             FROM CHARGES JCC
                            WHERE JCC.DET_SEQ = 1
                              AND JCC.JOB_ID = JC1.DOCUMENT_ID
                              AND JCC.MAIN_SEQ = JC1.MAIN_SEQ),
       JC1.EXCHANGERATE = (SELECT JCC.EXCHANGERATE
                             FROM CHARGES JCC
                            WHERE JCC.DET_SEQ = 1
                              AND JCC.DOCUMENT_ID = JC1.DOCUMENT_ID
                              AND JCC.MAIN_SEQ = JC1.MAIN_SEQ)
 WHERE (JC1.DOCUMENT_ID, JC1.MAIN_SEQ) IN
       (SELECT JC.DOCUMENT_ID, JC.MAIN_SEQ
          FROM CHARGES JC
         WHERE (JC.DOCUMENT_ID, JC.MAIN_SEQ) IN
               (SELECT JCHARGE.DOCUMENT_ID, JCHARGE.MAIN_SEQ
                  FROM CHARGES JCHARGE
                 WHERE JCHARGE.STATUS IN ('PART_INVOICED')
                   AND JCHARGE.DET_SEQ = 1
                   AND EXISTS
                 (SELECT 'X'
                          FROM CHARGES JCHG
                         WHERE JCHG.DOCUMENT_ID = JCHARGE.DOCUMENT_ID
                           AND JCHG.MAIN_SEQ = JCHARGE.MAIN_SEQ
                         GROUP BY JCHG.DOCUMENT_ID, JCHG.MAIN_SEQ
                        HAVING SUM(NVL(JCHG.AMOUNT, 0)) <> 0 and SUM(NVL(JCHG.trans_AMOUNT, 0)) = 0))
         GROUP BY JC.DOCUMENT_ID, JC.MAIN_SEQ
        HAVING COUNT(JC.DET_SEQ) = 3 AND COUNT(DISTINCT JC.EXCHANGERATE) > 1);

Re: how to write update statement for this [message #437372 is a reply to message #437371] Mon, 04 January 2010 02:17 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

Use decode for your 'Status' updation.
Update....
Set JC1.STATUS = decode(DET_SEQ,1,'INVOICED',JC1.STATUS );

Hope this works as per your requirement.

Regds
Sony
Re: how to write update statement for this [message #437375 is a reply to message #437372] Mon, 04 January 2010 02:36 Go to previous message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i didnt get this idea.thanks for the reply.
Previous Topic: compare two num,varchar column in two table
Next Topic: utl_dbws how to
Goto Forum:
  


Current Time: Mon Dec 05 08:46:11 CST 2016

Total time taken to generate the page: 0.06594 seconds