We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Nested query

rapace3
rapace3 asked
on
Medium Priority
283 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
Comment
Watch Question

Top Expert 2006

Commented:
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

:)

Commented:
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


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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.