Home » SQL & PL/SQL » SQL & PL/SQL » Can you join these 2 queries into 1?
Can you join these 2 queries into 1? [message #271691] Tue, 02 October 2007 09:15 Go to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
Hi,
I have 2 queries I would like to make into 1 big query. I can't seem to put them together maybe you can...
the first query displays what the folder view (tree structure) looks like given a starting parent folder ID.

SELECT LEVEL, dataid, parentid, SUBSTR(LPAD(' ',LEVEL-1)||name ,1,100) "Folder Name" 
FROM  dtree 
WHERE  SUBTYPE IN (0) /* Will just print the folder structure */ 
CONNECT BY PRIOR dataid = parentid 
START WITH dataid=11409
ORDER BY ROWNUM 


Now the second query finds a count of all the documents that have been fetched within 1 year for a given folder data ID

SELECT COUNT(DISTINCT(z.dataid)) 
FROM dtree z, dauditnew b 
WHERE z.SUBTYPE = 144 
AND z.dataid = b.dataid 
AND LOWER(b.auditstr)  = 'fetch' 
AND b.auditdate >= (SELECT SYSDATE-365 FROM dual) 
AND z.parentid = 6120386 


so is there a way to merge these 2 queries?
Thanks,

[Updated on: Tue, 02 October 2007 10:51] by Moderator

Report message to a moderator

Re: Can you join these 2 queries into 1? [message #271707 is a reply to message #271691] Tue, 02 October 2007 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Limit your lines to 80 characters when you post formatted code.

Regards
Michel
Re: Can you join these 2 queries into 1? [message #271709 is a reply to message #271691] Tue, 02 October 2007 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The merge have to be done based on what criteria?

Regards
Michel
Re: Can you join these 2 queries into 1? [message #271716 is a reply to message #271691] Tue, 02 October 2007 11:21 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
The first query finds a list of folders (dataid) in a tree
so as it finds each folder (dataid) I need to perform the second query (on that dataid) and find a count of all the recently modified documents at that particular folder level.
the final result set would look like this:
Level dataid parentid Name dcount
1 12345 2000 folderX 24
2 44444 12345 folderY 11
etc.

so the dataid from query 1 needs to get plugged into the z.parentid of query 2.

Does this make sense?
Sorry, I don't know if I'm explaining this clearly...

Thanks for your response,



[Updated on: Tue, 02 October 2007 11:25]

Report message to a moderator

Re: Can you join these 2 queries into 1? [message #271717 is a reply to message #271716] Tue, 02 October 2007 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add to your second query:
and z.dataid in (<your first query with just select dataid>)

or if you want the tree start at "parentid = 6120386" then move this condition inside the connect by and replace the start with clause with it.
or...
Many possible solutions depending on where you want to start and what dataid you try to find.

Regards
Michel

[Updated on: Tue, 02 October 2007 11:29]

Report message to a moderator

Re: Can you join these 2 queries into 1? [message #271729 is a reply to message #271691] Tue, 02 October 2007 13:00 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
zim327 wrote on Tue, 02 October 2007 10:15

AND b.auditdate >= (SELECT SYSDATE-365 FROM dual) 




This is overkill and another hit on the database and could be simply replaced by sysdate-365
Re: Can you join these 2 queries into 1? [message #271732 is a reply to message #271729] Tue, 02 October 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or better "add_months(sysdate,-12)". Wink

Regards
Michel
Re: Can you join these 2 queries into 1? [message #271734 is a reply to message #271691] Tue, 02 October 2007 13:13 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Try the following. By the way, your order by rownum is totally useless. You are saying order the return data in the order that it is being returned anyway.

SELECT LEVEL, y.dataid, parentid, SUBSTR(LPAD(' ',LEVEL-1)||name ,1,100) "Folder Name" ,nvl(x.cnt,0) dataid_cnt
FROM  dtree y,
(SELECT COUNT(DISTINCT(z.dataid)),z.dataid 
 FROM dtree z, dauditnew b 
 WHERE z.SUBTYPE = 144 
 AND z.dataid = b.dataid 
 AND LOWER(b.auditstr)  = 'fetch' 
 AND b.auditdate >= add_months(trunc(sysdate),-12)
 AND z.parentid = 6120386
 group by z.dataid ) x 
WHERE  SUBTYPE IN (0) /* Will just print the folder structure */ 
and x.dataid(+) = y.dataid
CONNECT BY PRIOR dataid = parentid 
START WITH dataid=11409;

Re: Can you join these 2 queries into 1? [message #272041 is a reply to message #271734] Wed, 03 October 2007 14:26 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
I tried bill b's solution and it complains that it doesn't know
what x.cnt or x.dataid(+) are.
the errors are both ORA 00904: invalid identifier
Any ideas?

Oh and the connect by prior needs to have the same starting id
I accidentally put different id's in the original post. sorry.

here's what I tried
SELECT LEVEL, y.dataid, y.parentid, SUBSTR(LPAD(' ',LEVEL-1)||y.name ,1,180) "Folder Name" ,
 NVL(x.cnt,0) dataid_cnt
FROM  DTREE y,
(SELECT COUNT(DISTINCT(z.dataid)) 
 FROM DTREE z, DAUDITNEW b 
 WHERE z.SUBTYPE = 144 
 AND z.dataid = b.dataid 
 AND LOWER(b.auditstr)  = 'fetch' 
 AND b.auditdate >= ADD_MONTHS(TRUNC(SYSDATE),-12)
 CONNECT BY PRIOR z.dataid = z.parentid
 START WITH z.dataid = 6120386
 GROUP BY z.dataid ) x 
WHERE  SUBTYPE IN (0) /* Will just print the folder structure */ 
AND x.dataid(+) = y.dataid
CONNECT BY PRIOR y.dataid = y.parentid 
START WITH y.dataid=6120386;
Re: Can you join these 2 queries into 1? [message #272042 is a reply to message #271691] Wed, 03 October 2007 14:29 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
SORRY FOR THE TYPO


SELECT LEVEL, y.dataid, y.parentid, SUBSTR(LPAD(' ',LEVEL-1)||y.name ,1,180) "Folder Name" ,
 NVL(x.cnt,0) dataid_cnt
FROM  DTREE y,
(SELECT COUNT(DISTINCT(z.dataid)) CNT,Z.DATAID
 FROM DTREE z, DAUDITNEW b 
 WHERE z.SUBTYPE = 144 
 AND z.dataid = b.dataid 
 AND LOWER(b.auditstr)  = 'fetch' 
 AND b.auditdate >= ADD_MONTHS(TRUNC(SYSDATE),-12)
 CONNECT BY PRIOR z.dataid = z.parentid
 START WITH z.dataid = 6120386
 GROUP BY z.dataid ) x 
WHERE  SUBTYPE IN (0) /* Will just print the folder structure */ 
AND x.dataid(+) = y.dataid
CONNECT BY PRIOR y.dataid = y.parentid 
START WITH y.dataid=6120386;



Re: Can you join these 2 queries into 1? [message #272050 is a reply to message #272042] Wed, 03 October 2007 16:04 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
sorry it doesn't work, the query just never responds
???
Re: Can you join these 2 queries into 1? [message #272052 is a reply to message #271691] Wed, 03 October 2007 16:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
so enable SQL_TRACE to see where it is spinning it wheels.

generating & posting an EXPLAIN_PLAN might allow folks to make an educated guess at where the problem might be.

Stating "the query just never responds" is less than helpful.
Re: Can you join these 2 queries into 1? [message #272319 is a reply to message #272052] Thu, 04 October 2007 09:32 Go to previous message
zim327
Messages: 15
Registered: October 2007
Junior Member
Ok, I managed to create an explain plan (I never heard of one before) and the query was calling the dtree and dauditnew tables about 22 times!
So I rewrote the query into this behemoth:

SELECT     LEVEL
         , y.dataid
         , y.parentid
         , SUBSTR (LPAD (' ', LEVEL - 1) || y.NAME, 1, 190) "Folder Name"
         , NVL (x.cnt, 0) "Docs modified w/i
 1 year"
FROM       DTREE y
         , (SELECT   z.parentid
                   , k.dataid
                   , COUNT (DISTINCT (z.dataid)) cnt
            FROM     DTREE z
                   , DTREE k
                   , DAUDITNEW b
            WHERE    z.SUBTYPE = 144
            AND      z.dataid = b.dataid
            AND      LOWER (b.auditstr) = 'fetch'
            AND      b.auditdate >= ADD_MONTHS (TRUNC (SYSDATE), -12)
            AND      k.dataid = z.parentid
            AND      z.parentid IN (SELECT     dataid
                                    FROM       DTREE y
                                    WHERE      y.SUBTYPE IN (0)     
                                    CONNECT BY PRIOR y.dataid = y.parentid
                                    START WITH y.dataid = 6120386)
            GROUP BY z.parentid
                   , k.dataid) x
WHERE      SUBTYPE IN (0)      
           AND x.dataid(+) = y.dataid
CONNECT BY PRIOR y.dataid = y.parentid
START WITH y.dataid = 6120386


and it actually worked! It takes about 2 mins but I think I can work with this.
Thanks to all for your help! I learned a great deal.

[mod-edit]applied formatting.

[Updated on: Fri, 05 October 2007 01:29] by Moderator

Report message to a moderator

Previous Topic: MEMBER OF operator
Next Topic: How to get count of substition variables in SQLPLUS
Goto Forum:
  


Current Time: Sat Dec 10 09:20:22 CST 2016

Total time taken to generate the page: 0.10882 seconds