Home » SQL & PL/SQL » SQL & PL/SQL » Hi Experts,
Hi Experts, [message #235486] Sun, 06 May 2007 17:51 Go to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hello,

I am in small confusion. I was given a work to verify the logic. I have to test whether this logic is correct or do i need to change the logic. I need to write a small query to find duplicate vales on our test instance. Please tell me the query to return duplicate values based on the below mentioned logic. You can identify the unique,primary and other columns in the query.

I want a query to return a duplicate values.

SELECT COUNT (*)
INTO v_cnt
FROM DUAL
WHERE EXISTS
(SELECT 'Y'
FROM tbl1 cs
WHERE cs.payor_id = g_PAYOR_ID
AND cs.center_id = g_CENTER_ID
AND cs.member_id = v_subscriber_id
AND cs.claim_id = rx_rec.claim_id
AND cs.claim_line_id = v_claim_line_id)
OR EXISTS
(SELECT 'Y'
FROM tbl1 cs
WHERE cs.payor_id = GV_RRD_PAYOR_ID
AND cs.center_id = GV_CENTER_ID
AND cs.member_id = v_subscriber_id
AND cs.date_of_service = rx_rec.date_of_service
AND cs.date_pd = rx_rec.date_pd
AND cs.amt_pd = rx_rec.amt_pd
AND cs.amt_co_pay = rx_rec.copay_amt
AND cs.ndc_code = rx_rec.ndc_code
AND cs.provider_id = rx_rec.provider_id
AND cs.supply = rx_rec.supply)
OR EXISTS
(SELECT 'Y'
FROM tbl2
WHERE C.payor_id = g_PAYOR_ID
AND C.center_id = g_CENTER_ID
AND C.member_id = v_subscriber_id
AND C.date_of_service = rx_rec.date_of_service
AND C.date_pd = rx_rec.date_pd
AND C.amt_pd = rx_rec.amt_pd
AND C.amt_co_pay = rx_rec.copay_amt
AND C.ndc_code = rx_rec.ndc_code
AND C.provider_id = rx_rec.provider_id
AND C.supply = rx_rec.supply);


Thank you very much in advance
Re: Hi Experts, [message #235489 is a reply to message #235486] Sun, 06 May 2007 20:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please tell me the query to return duplicate values based on the below mentioned logic.

Is that YOUR job; not ours

>You can identify the unique,primary and other columns in the query.
Sorry, I can't but why does it matter.

Please read & FOLLOW the #1 Sticky Post at the top of this forum.
Re: Hi Experts, [message #235500 is a reply to message #235486] Sun, 06 May 2007 23:38 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I was given a work to verify the logic.
SELECT COUNT(*) FROM DUAL returns only 0 or 1. Seems to me you use it in some PL/SQL LOOP, as INTO is not valid in SQL query and G_*, V_*, GV_* and RX_REC variables(?) are used, however I can only guess what they contain. You did not provide 'logic', just a single SELECT statement taken from middle of your program without any description.

>You can identify the unique,primary and other columns in the query.
It seems quite useless as the uniqueness of unique and primary key is guaranteed by database (no duplicates possible).

You can get all 'duplicate' rows by this query. If you want it over more tables, just put their UNION into FROM clause.
Previous Topic: Showing duplicates under multiple columns
Next Topic: order by problem
Goto Forum:
  


Current Time: Sat Dec 10 03:00:16 CST 2016

Total time taken to generate the page: 0.04472 seconds