Home » SQL & PL/SQL » SQL & PL/SQL » Need distinct on 1 table
Need distinct on 1 table [message #247341] Mon, 25 June 2007 11:20 Go to next message
richardl13
Messages: 4
Registered: June 2007
Location: Florida
Junior Member
I have the following sql and I would like to only pull the distinct records from the Den table. Currently the acctbalance is coming back to high(doubled, tripled, etc..) Thanks in advance.

SELECT NVL(D.DCPCODE, 'NA') DCPCODE,
NVL(D.DINSKEYCODE,'NA') DINSKEYCODE,
NVL(I.INSNAME,'NA') INSNAME,
NVL(D.DBILLSTATUS, 'NA') DBILLSTATUS,
NVL(D.DDOCCODE,0) DDOCCODE,
NVL(DA.DOCNAME,'NA') DOCNAME,
sum(A.ACCTBALANCE)
FROM DEN D
JOIN ACC A ON D.DCPCODE = A.ACCTCPCODE AND D.DACCOUNT = A.ACCTCODE
LEFT JOIN DOC DA ON D.DDOCCODE = DA.DOCCODE AND D.DCPCODE = DA.DOCCPCODE
LEFT JOIN INS I ON D.DINSKEYCODE = I.INSCODE
LEFT JOIN REM R ON D.DCPCODE = R.REMCPCODE AND D.DACCOUNT = R.REMACCTCODE AND R.REMTEXT LIKE '%PNF%'
WHERE (D.DENIAL1 in ('147','170','171','173')
OR D.DENIAL2 in ('147','170','171','173')
OR D.DENIAL3 in ('147','170','171','173')
OR D.DENIAL4 in ('147','170','171','173'))
AND A.ACCTBALANCE > 0
AND R.REMCPCODE IS NULL
AND R.REMACCTCODE IS NULL
GROUP BY D.DCPCODE, D.DINSKEYCODE, I.INSNAME, D.DBILLSTATUS, D.DDOCCODE, DA.DOCNAME
Re: Need distinct on 1 table [message #247343 is a reply to message #247341] Mon, 25 June 2007 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Then perhaps use should use "DISTINCT" in the SELECT.
Re: Need distinct on 1 table [message #247347 is a reply to message #247341] Mon, 25 June 2007 11:33 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Posting formatted EXPLAIN and tables/indexes structures may help.

Michael
Re: Need distinct on 1 table [message #247349 is a reply to message #247343] Mon, 25 June 2007 11:36 Go to previous messageGo to next message
richardl13
Messages: 4
Registered: June 2007
Location: Florida
Junior Member
Adding distinct doesn't help since it has a group by. It returns the correct number of records, but my sum isn't right because the DEN table is returning more then 1.

This does seem like it should be easy. I am stumped.
Re: Need distinct on 1 table [message #247358 is a reply to message #247349] Mon, 25 June 2007 11:58 Go to previous messageGo to next message
richardl13
Messages: 4
Registered: June 2007
Location: Florida
Junior Member
It is Oracle 9.2

I can't tell what the indexes are. The data is pulled from an application service provider. All I am able to do is query the database.

The DEN table has a many to one relationship with the ACC table. If I query up just the DEN table I will get duplicates.
Re: Need distinct on 1 table [message #247403 is a reply to message #247341] Mon, 25 June 2007 15:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Then perhaps use should use "DISTINCT" in the SELECT.

Something like
SELECT DISTINCT <column_list> FROM (<your query>);
or
SELECT <column list>
FROM (SELECT DISTINCT <columns used from den>
 FROM den
 WHERE <conditions on den>) d
<join other tables>
WHERE <other conditions>
GROUP BY <your columns>;


[Update: added second code example]

[Updated on: Mon, 25 June 2007 15:12]

Report message to a moderator

Re: Need distinct on 1 table [message #247412 is a reply to message #247341] Mon, 25 June 2007 15:21 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
If the relationship is N:1 then why do you really need SUM?

Michael

[Updated on: Mon, 25 June 2007 15:21]

Report message to a moderator

Re: Need distinct on 1 table [message #247647 is a reply to message #247341] Tue, 26 June 2007 11:48 Go to previous message
richardl13
Messages: 4
Registered: June 2007
Location: Florida
Junior Member
Thanks guys,

Flyboys suggestion for the SELECT DISTINCT in the FROM clause worked.
Previous Topic: Table Join on Same Table
Next Topic: Any way to parameterize the following code
Goto Forum:
  


Current Time: Sat Dec 03 11:54:19 CST 2016

Total time taken to generate the page: 0.10353 seconds