Avatar of meera78
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
Oracle Database

Avatar of undefined
Last Comment
meera78

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
meera78

Looks like it is working but it is giving me weird error

"Not enough values"


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, runid 
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

Open in new window

slightwv (䄆 Netminder)

Missed a comma.

aca_instance_id, pid, filter_id, client_id, provider_id, insertedby, insertdatetime, runid
ASKER
meera78

Sorry, can you tell me where you missed a comma!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

I added it.

between: client_id, provider_id
ASKER
meera78

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

Open in new window

ASKER
meera78

Perfect!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.