Home » SQL & PL/SQL » SQL & PL/SQL » Calculating and Inserting Value from Same Table
| Calculating and Inserting Value from Same Table [message #148119] |
Tue, 22 November 2005 19:00  |
mymot
Messages: 225
Registered: July 2005
|
Senior Member |
|
|
HI,
I have got bit similar requirement raised in the same forum,
I have got the following table and i would like to first calcualte value based on available values in table pp and then want to insert those 2 caculated values into table pp(same table) against corresponding column (group BY aname,did,timestamp)
create table pp(jur varchar2(100),aname varchar2(100),timestamp date,did number,id number,value varchar2(1000));
insert into pp values('J1','A2',SYSDATE,11,1,33);
insert into pp values('J1','A2',SYSDATE,11,2,3);
insert into pp values('J1','A2',SYSDATE,11,3,1);
insert into pp values('J1','A2',SYSDATE,11,4,3);
insert into pp values('J1','A2',SYSDATE,11,5,43);
insert into pp values('J2','A1',SYSDATE,11,1,6);
insert into pp values('J2','A1',SYSDATE,11,2,93);
insert into pp values('J2','A1',SYSDATE,11,3,0);
insert into pp values('J2','A1',SYSDATE,11,4,13);
insert into pp values('J2','A1',SYSDATE,11,5,8);
insert into pp values('J3','A2',SYSDATE,11,1,68);
insert into pp values('J3','A2',SYSDATE,11,2,13);
insert into pp values('J3','A2',SYSDATE,11,3,0);
insert into pp values('J3','A2',SYSDATE,11,4,0);
insert into pp values('J3','A2',SYSDATE,11,5,19);
select max(decode(id,1,value)) * max(decode(id,3,value)) / NULLIF (SUM(decode(id,3,value)),0) into NT1 from pp group by aname,timestamp,did;
NT1 for A2= (33 * 1) + (68 * 0 )/ (1 + 0 )
NT1 for A1....
NT1 for ......
select max(decode(id,2,value)) * max(decode(id,4,value)) / NULLIF (SUM(decode(id,4,value)),0) into NT2 from pp group by aname,timestamp,did;
NT2 for A1 = (93 * 13) + ( 13)
NT2 for A2....
NT2 FOR A3....
INSERT INTO PP
NT1,NT2..
where pp.aname = NT1 value's aname
and pp.timestamp = NT1 value's timestamp
and pp.did = NT1 value's did;
same for NT2.
I have done following things
I was expecting below error as query is using group BY (that's what i want so no problem with that) and Merger into function will try update multiple rows ( that's what i want but not happening),
because GROUP BY will get value based on ANAME,timestamp,did and i want update value with id=4 in table pp with same ANAME,TIMESTAMP,DID available with GROUP BY
if it works then in single query i could do cal and update then i dont need to do join operation.
MERGE INTO PP using
(select null JUR,aname,timestamp,did, null id,max(decode(id,2,value)) * max(decode(id,4,value)) / NULLIF (SUM(decode(id,4,value)),0) v4
FROM pp group by aname,timestamp,did order by aname,timestamp,did ) QQ
on (pp.aname = pp.aname
and pp.did = pp.did
and pp.timestamp = pp.timestamp
and pp.sid = 4)
when matched then update set value = QQ.v4
when not matched then insert (JUR,aname,timestamp,did,id,value)
values (QQ.JUR,QQ.aname,QQ.timestamp,QQ.did,QQ.ID,QQ.v26);
ERROR -ORA-30926 Unable to get set of stable rows in the source table.
Group By will give us NT1,NT2 for each name and i would like update values in pp with NT1 and NT2 where it matches with ANAME,TIMESTAMP,DID of pp with NT1 and NT2 ANAME,TIMESTAMP,DID.
Thanks
[Updated on: Wed, 23 November 2005 01:04] Report message to a moderator |
|
|
|
| Re: Calculating and Inserting Value from Same Table [message #148239 is a reply to message #148119] |
Wed, 23 November 2005 04:19  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Before all -
MERGE INTO PP using
(select null JUR,aname,timestamp,did, null id,max(decode(id,2,value)) * max(decode(id,4,value)) / NULLIF (SUM(decode(id,4,value)),0) v4
FROM pp group by aname,timestamp,did order by aname,timestamp,did ) QQ
on (pp.aname = pp.aname
and pp.did = pp.did
and pp.timestamp = pp.timestamp
and pp.sid = 4)
when matched then update set value = QQ.v4
when not matched then insert (JUR,aname,timestamp,did,id,value)
values (QQ.JUR,QQ.aname,QQ.timestamp,QQ.did,QQ.ID,QQ.v26);
is the COMPLETELY WRONG statement (meanwhile every time you send private messages to posters you make a lot of syntax/structure mistakes - didn't you think to control yourself carefully before posting ?).
And this is fairly bad idea to use varchar2 column for keeping of
numbers.
After all, is it what you need ?
SQL> select aname,timestamp,did,
2 max(decode(id,2,value)) * max(decode(id,4,value)) / NULLIF (SUM(decode(id,4,value)),0) v4
3 FROM pp group by aname,timestamp,did
4 /
ANAME TIMESTAM DID V4
---------- -------- ---- ----------
A1 23.11.05 11 93
A2 23.11.05 11 3
SQL> select aname,timestamp,did,value from pp where id = 4;
ANAME TIMESTAM DID VALUE
---------- -------- ---- ----------
A2 23.11.05 11 3
A1 23.11.05 11 13
A2 23.11.05 11 0
SQL> MERGE INTO PP using
2 (select null JUR,aname,timestamp,did, null id,max(decode(id,2,value)) * max(decode(id,4,value)
) / NULLIF (SUM(decode(id,4,value)),0) v4
3 FROM pp group by aname,timestamp,did) QQ
4 on (pp.aname = qq.aname
5 and pp.did = qq.did
6 and pp.timestamp = qq.timestamp
7 and pp.id = 4)
8 when matched then update set value = QQ.v4
9 when not matched then insert (JUR,aname,timestamp,did,id,value)
10 values (QQ.JUR,QQ.aname,QQ.timestamp,QQ.did,QQ.ID,QQ.v4);
3 rows merged.
SQL> select aname,timestamp,did,value from pp where id = 4;
ANAME TIMESTAM DID VALUE
---------- -------- ---- ----------
A2 23.11.05 11 3
A1 23.11.05 11 93
A2 23.11.05 11 3
In 10G this syntax works:
SQL> MERGE INTO PP using
2 (select null JUR,aname,timestamp,did, null id,max(decode(id,2,value)) * max(decode(id,4,value)
) / NULLIF (SUM(decode(id,4,value)),0) v4
3 FROM pp group by aname,timestamp,did) QQ
4 on (pp.aname = qq.aname
5 and pp.did = qq.did
6 and pp.timestamp = qq.timestamp
7 )
8 when matched then update set value = QQ.v4 where pp.id = 4
9 /
3 rows merged.
Rgds.
[Updated on: Wed, 23 November 2005 05:06] Report message to a moderator |
|
|
|
 Re: Calculating and Inserting Value from Same Table [message #148373 is a reply to message #148239] |
Wed, 23 November 2005 18:56 |
mymot
Messages: 225
Registered: July 2005
|
Senior Member |
|
|
Thanks,
Below SQL query gives incorrect Result
SQL > select * from pp;
JUR ANAME TIMESTAMP DID ID VALUE
--------------------------------------------------------------
J1 A2 24-Nov-2005 02:36:09 AM 11 1 33
J1 A2 24-Nov-2005 02:36:09 AM 11 2 3
J1 A2 24-Nov-2005 02:36:09 AM 11 3 1
J1 A2 24-Nov-2005 02:36:09 AM 11 4 3
J1 A2 24-Nov-2005 02:36:09 AM 11 5 43
J2 A1 24-Nov-2005 02:36:09 AM 11 1 6
J2 A1 24-Nov-2005 02:36:09 AM 11 2 93
J2 A1 24-Nov-2005 02:36:09 AM 11 3 0
J2 A1 24-Nov-2005 02:36:09 AM 11 4 13
J2 A1 24-Nov-2005 02:36:09 AM 11 5 8
J3 A2 24-Nov-2005 02:36:09 AM 11 1 68
J3 A2 24-Nov-2005 02:36:09 AM 11 2 13
J3 A2 24-Nov-2005 02:36:09 AM 11 3 10
J3 A2 24-Nov-2005 02:36:09 AM 11 4 10
J3 A2 24-Nov-2005 02:36:09 AM 11 5 19
SQL >select aname,timestamp,did, max(decode(id,2,value)) * max(decode(id,4,value)) / NULLIF (SUM(decode(id,4,value)),0) v4
FROM pp group by aname,timestamp,did
ANAME TIMESTAMP DID V4
------------------------------------
A1 24-Nov-2005 02:36:09 AM 11 93
A2 24-Nov-2005 02:36:09 AM 11 6.92
V4 should be ANAME = A2, DID =11, JUR = J1,J3
(3 * 3) + (13 * 10)/(3+10) = 10.69 ===> Expected
Actual is = 6.92 ????????? what is the problem over here??
V4 should be ANME = A1 ,DID = 11 JUR = J2
( 93 * 13) / 13 = 93 ==> Correct
Output for A2 is different then expected?
Thanks
[Updated on: Wed, 23 November 2005 21:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| |
  |
Goto Forum:
Current Time: Sun Aug 03 15:28:36 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
