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  |
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);
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 19:01:25 CST 2025
|