Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested query

Posted on 2006-11-01
3
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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