meera78
asked on
Analytical functions
I am trying to execute this insert statement
insert into ACA_INSTANCE (
ACA_INSTANCE_ID, PID, FILTER_ID, CLIENT_ID, PROVIDER_ID, INSERTEDBY, INSERTDATETIME, RUNID)
select
ACA_INSTANCE_ID * -1,PID * -1 as PID, FILTER_ID,100 as CLIENT_ID, PROVIDER_ID, 500020, sysdate, 100
from ACA_INSTANCE
where pid in (select pid * -1 from membership where runid=100) and client_id = 500006
The issue here is I have unique constraint violation
The unique constraint is on the combination of
(PID,FILTER_ID,RUNID)
Since in the above insert statement, I included runid as 100
I was checking the combination of PID, FILTER_ID and I found duplicates
Here is the statement where I found duplicates
select count(*),
pid*-1 as pid,FILTER_ID
from ACA_INSTANCE
where client_id = 500006
group by pid,FILTER_ID
having count(*) > 1
order by pid, filter_id
Now, I wanted to use analytical functions inside the insert statement to select the first record of the existing records. Can you please suggest asap
Immediate response is appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Missed a comma.
aca_instance_id, pid, filter_id, client_id, provider_id, insertedby, insertdatetime, runid
aca_instance_id, pid, filter_id, client_id, provider_id, insertedby, insertdatetime, runid
ASKER
Sorry, can you tell me where you missed a comma!
I added it.
between: client_id, provider_id
between: client_id, provider_id
ASKER
I think I got it!, thanks so much for the quick response.
insert into ACA_INSTANCE
(ACA_INSTANCE_ID, PID, FILTER_ID, CLIENT_ID, PROVIDER_ID, INSERTEDBY, INSERTDATETIME, RUNID)
select
aca_instance_id, pid, filter_id, client_id, provider_id, insertedby, insertdatetime, 100
from (
select
ACA_INSTANCE_ID * -1 as aca_instance_id,
PID * -1 as PID,
FILTER_ID,
100 as CLIENT_ID,
PROVIDER_ID,
500020 as insertedBy,
sysdate as insertdatetime,
100 as runid,
row_number() over(partition by pid, client_id order by pid,client_id) myRownum
from ACA_INSTANCE
where pid in (select pid * -1 from membership where runid=100) and client_id = 500006
) where myrownum=1
ASKER
Perfect!
ASKER
"Not enough values"
Open in new window