[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Nested query

Posted on 2006-11-01
3
Medium Priority
?
266 Views
Last Modified: 2008-03-10
I need the result of the two queries combined to give me one output with one list

   SELECT distinct substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-SAC'
     ORDER BY 1

returns:
AFD
E0X
INV
MIS
MXO
OSX
RSS
SAC
SCH
SER
SGA
SHD
STC
STD
STH
STX
SZH
USC
VER
XIO
ZBD


   SELECT distinct substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-INS'
     ORDER BY 1

retruns :
INS
0
Comment
Question by:rapace3
3 Comments
 
LVL 18

Expert Comment

by:rbrooker
ID: 17854631
Hi,

try :
SELECT *
FROM (SELECT DISTINCT substr(PARENT, 3) division
      FROM wwglph2.wwgl_dv_tree
      CONNECT BY PRIOR child = PARENT
      START WITH PARENT = 'D-SAC'
      UNION ALL
      SELECT DISTINCT substr(PARENT, 3) division
      FROM wwglph2.wwgl_dv_tree
      CONNECT BY PRIOR child = PARENT
      START WITH PARENT = 'D-INS')
ORDER BY 1

or
( not sure if this one will work )

SELECT DISTINCT substr(PARENT, 3) division
FROM wwglph2.wwgl_dv_tree
CONNECT BY PRIOR child = PARENT
START WITH PARENT IN ('D-SAC', 'D-INS')
ORDER BY 1

:)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 17854985
SELECT substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-SAC'
UNION
 SELECT substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-INS'
     ORDER BY 1

0
 

Accepted Solution

by:
misbah_ra earned 500 total points
ID: 17856798

Select division from
(
SELECT distinct substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-SAC'
)
union all
Select division from
(
SELECT distinct substr(parent,3) division
         FROM wwglph2.wwgl_dv_tree
   CONNECT BY prior child = parent
   START WITH parent  = 'D-INS'
)
ORDER BY 1
--You can also use union instead of union all depending on ur requirement
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question