Home » SQL & PL/SQL » SQL & PL/SQL » how to update statment if difference between them is greater than 0.1 (oracle 10g)
how to update statment if difference between them is greater than 0.1 [message #432863] Thu, 26 November 2009 22:01 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

I want to do sum of the charges amount should be updated to the net_amount of invoice table if the difference between them is greater than 0.1;i have written below query to suffice the requirement.but i am getting ora-00933:sql command not properly ended.Is there other way to write this update statement;



UPDATE INVOICE_HEADER INV
   SET INV.NET_AMOUNT = (SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                           FROM CHARGE jc
                          WHERE JC.INVOICE_ID = INV.INVOICE_ID)

 WHERE EXISTS (SELECT 'x'
          FROM CHARGE jc
         WHERE JC.INVOICE_ID = INV.INVOICE_ID
         GROUP BY INV.INVOICE_ID, INV.NET_AMOUNT) HAVING
 ABS(INV.NET_AMOUNT - (NVL((SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                                   FROM CHARGE jc
                                  WHERE JC.INVOICE_ID = INV.INVOICE_ID),
                                 0))) > 0.1;



CREATE TABLE INVOICE_HEADER
(
  INVOICE_ID         NUMBER NOT NULL PRIMARY KEY,
  NET_AMOUNT         NUMBER NULL
);
 
 INSERT INTO INVOICE_HEADER(INVOICE_ID,NET_AMOUNT) VALUES(1,1000.1);
 INSERT INTO INVOICE_HEADER(INVOICE_ID,NET_AMOUNT) VALUES(2,400.34);
 INSERT INTO INVOICE_HEADER(INVOICE_ID,NET_AMOUNT) VALUES(3,500.67);
 
 

 
CREATE TABLE CHARGE
(
  ID                   NUMBER NOT NULL   PRIMARY KEY,
  INVOICE_ID           NUMBER NOT NULL   REFERENCES INVOICE_HEADER (INVOICE_ID),
  CHARGE_AMOUNT        NUMBER NULL  
);
 
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(1,1,100);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(2,1,700);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(3,1,500);

INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(4,2,200);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(5,2,200.34);
 
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(6,3,12.56);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(7,3,300);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(8,3,100);
INSERT INTO CHARGE(ID,INVOICE_ID ,CHARGE_AMOUNT) VALUES(9,3,120);


Re: how to update statment if difference between them is greater than 0.1 [message #432864 is a reply to message #432863] Thu, 26 November 2009 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
UPDATE INVOICE_HEADER INV
   SET INV.NET_AMOUNT = (SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                           FROM CHARGE jc
                          WHERE JC.INVOICE_ID = INV.INVOICE_ID)
 WHERE EXISTS (SELECT 'x'
          FROM CHARGE jc
         WHERE JC.INVOICE_ID = INV.INVOICE_ID
         GROUP BY INV.INVOICE_ID, INV.NET_AMOUNT) HAVING
 ABS(INV.NET_AMOUNT - (NVL((SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                                   FROM CHARGE jc
                                  WHERE JC.INVOICE_ID = INV.INVOICE_ID),
                                 0))) > 0.1;


Is above any better?
Re: how to update statment if difference between them is greater than 0.1 [message #432865 is a reply to message #432863] Thu, 26 November 2009 22:20 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
same error is coming.
Re: how to update statment if difference between them is greater than 0.1 [message #432866 is a reply to message #432865] Thu, 26 November 2009 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://tahiti.oracle.com is down for me now so I can't RTFM
  1  UPDATE INVOICE_HEADER INV
  2	SET INV.NET_AMOUNT = (SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
  3				FROM CHARGE jc
  4			       WHERE JC.INVOICE_ID = INV.INVOICE_ID)
  5   WHERE EXISTS (SELECT 'x'
  6	       FROM CHARGE jc
  7	      WHERE JC.INVOICE_ID = INV.INVOICE_ID
  8	      GROUP BY INV.INVOICE_ID, INV.NET_AMOUNT)
  9  HAVING ABS(INV.NET_AMOUNT - (NVL((SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
 10					FROM CHARGE jc
 11				       WHERE JC.INVOICE_ID = INV.INVOICE_ID),
 12*				      0))) > 0.1
SQL> /
HAVING ABS(INV.NET_AMOUNT - (NVL((SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
*
ERROR at line 9:
ORA-00933: SQL command not properly ended

I don't think HAVING clause is valid on UPDATE statement
Re: how to update statment if difference between them is greater than 0.1 [message #432868 is a reply to message #432866] Thu, 26 November 2009 22:50 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
UPDATE INVOICE_HEADER INV
   SET INV.NET_AMOUNT = (SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                           FROM CHARGE jc
                          WHERE JC.INVOICE_ID = INV.INVOICE_ID)
 WHERE EXISTS (SELECT 'x'
          FROM CHARGE jc
         WHERE JC.INVOICE_ID = INV.INVOICE_ID
         GROUP BY INV.INVOICE_ID, INV.NET_AMOUNT) 
AND ABS(INV.NET_AMOUNT - (NVL((SELECT SUM(NVL(JC.CHARGE_AMOUNT, 0))
                                   FROM CHARGE jc
                                  WHERE JC.INVOICE_ID = INV.INVOICE_ID),
                                 0))) > 0.1;
Previous Topic: Tuning order by clause
Next Topic: By Setting the Parameter in Seconds can we stop the execution of the program
Goto Forum:
  


Current Time: Wed Dec 07 03:21:35 CST 2016

Total time taken to generate the page: 0.10519 seconds