OraFAQ Forum: SQL & PL/SQL » Oracle SQL help needed

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL help needed (Oracle 10 g) (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle SQL help needed [message #607778]

Tue, 11 February 2014 15:45

Mike_Fennell
Messages: 3
Registered: February 2014
Location: London

Junior Member

Hi Forum

I was wondering if anyone could help me with the following ?
I am trying to run this SQL
Select d1.DataID,(Select Distinct Count(SubType) From DTree Where ParentID = d1.DataID And Subtype = 136) As Document_Count
From DTree d1
Where d1.DataID IN (Select DataID From DTree Where SubType = 136
Start With DataID = %1
Connect By Prior DataID = ParentID)
And d1.OwnerID != -11131

Now the above runs perfectly and brings me back all the results but what i would like to do is just return the results where Count(Subtype) in the first line in bold is > 0

Is this possible ?

Regards
Mike.

Report message to a moderator

Re: Oracle SQL help needed [message #607789 is a reply to message #607778]

Wed, 12 February 2014 00:03

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

select dataid, document_count
from ( <your query goes here> )
where document_count > 0

Report message to a moderator

Re: Oracle SQL help needed [message #607811 is a reply to message #607789]

Wed, 12 February 2014 04:09

Mike_Fennell
Messages: 3
Registered: February 2014
Location: London

Junior Member

Hi Littlefoot
Thanks for your reply, i tried your suggestion using the following
SELECT DataID, Document_Count
From (Select Distinct Count(SubType) From DTree
Where ParentID = d1.DataID And SubType = 136 AS Document_Count
FROM DTREE d1
WHERE d1.DATAID IN (SELECT DataID FROM DTree Where Subtype = 136
Start With DataID = %1
Connect By Prior DataID = ParentID)
AND d1.OwnerID != -11131)
Where Document_Count > 0

But i keep getting a missing right parentesis error and not sure where as i have double checked my statement ...

Report message to a moderator

Re: Oracle SQL help needed [message #607812 is a reply to message #607811]

Wed, 12 February 2014 04:16

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What you posted is not what I said. <your query goes here> means what it says: your complete query goes in there, not some new, different query but the original one.

Report message to a moderator

Re: Oracle SQL help needed [message #607813 is a reply to message #607812]

Wed, 12 February 2014 04:23

Mike_Fennell
Messages: 3
Registered: February 2014
Location: London

Junior Member

HI Littlefoot

My apologies i eventually figured that out after i posted the reply, in my divine brilliance i had been trying new ways and just did not amend the sql back to the original one.

What you suggeste is now running and working as i want it.

Thanks very much for your help it was starting to get on my nerves , i now see i was just looking at it in a wrong way.

Regards
Mike.

Report message to a moderator

Re: Oracle SQL help needed [message #607814 is a reply to message #607813]

Wed, 12 February 2014 04:27

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Mike_Fennell wrote on Wed, 12 February 2014 11:23

... in my divine brilliance

LOL! We all have those moments (my own being rather frequent).

Report message to a moderator

Previous Topic:	string leteral updation
Next Topic:	Constraints., (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:21:54 CDT 2024