ERPstuff - Oracle EBS Community

hi master
Sir
I have many duplicate record in my table

I use this query

STDCODE COUNT(*)
-------------------- ----------------------
021-00-11133 1
021-00-11327 1
021-00-11329 2
021-00-11400 3
021-00-11401 1
021-00-11473 2
021-00-11490 1
021-00-11498 2
021-00-11521 1
021-00-11529 2
021-00-11531 2
021-00-11535 1
021-00-11548 1
021-00-11558 2

But sir I need only one record for every student
Such as


STDCODE COUNT(*)
-------------------- ----------------------
021-00-11133 1
021-00-11327 1
021-00-11329 1
021-00-11400 1
021-00-11401 1
021-00-11473 1
021-00-11490 1
021-00-11498 1
021-00-11521 1
021-00-11529 1
021-00-11531 1
021-00-11535 1
021-00-11548 1
021-00-11558 1

And in last total student

How I avoid the duplicate record and show only one record for every etudend from query
Please give me idea

Thank

It's very easy you have to Create Formula Column and write a query in it
function COUNTFormula return NUMBER is
TOTAL NUMBER(25);
begin
SELECT COUNT(DISTINCT NO)
INTO TOTAL
FROM Tables Means Copy only the tables names from Query and as well as the conditions
WHERE A = A
AND B = B
AND C = C
AND B.NO BETWEEN _TO AND _FROM;
RETURN TOTAL;
end;

100% Work I recently used this function if you do not under stud do not hesetate to reply.

Thanks

Cheers

Najm

Select A1.*, A1.rowid
from Tab1 A1
where exists (Select 'x' from Tab1 A2
where A1.L_NO = A2.L_NO
and A1.ROWID <> A2.ROWID)

Use This Query To Elimanate The Duplicate Records

-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)