Better way to combine columns using UNION

[bsterner]

Wondering if someone has a better alternative to the following code. The goal is to combine the record data for 3 columns into a single one.

SELECT QUEUE
FROM
(
SELECT QUEUE AS QUEUE
FROM MYSCHEMA.MYTABLE QUEUE1
UNION
SELECT SUPERVISOR AS QUEUE
FROM MYSCHEMA.MYTABLE QUEUE2
UNION
SELECT MANAGER AS QUEUE
FROM MYSCHEMA.MYTABLE QUEUE3
)
AS QUEUES

 

[AlexCuse]

This might do slightly better:

    SELECT QUEUE AS QUEUE
    FROM MYSCHEMA.MYTABLE QUEUE1
    UNION
    SELECT SUPERVISOR AS QUEUE
    FROM MYSCHEMA.MYTABLE QUEUE2
    UNION
    SELECT MANAGER AS QUEUE
    FROM MYSCHEMA.MYTABLE QUEUE3

You don't need the outer bits.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Even simpler:
SELECT QUEUE
FROM MYSCHEMA.MYTABLE
UNION
SELECT SUPERVISOR
FROM MYSCHEMA.MYTABLE
UNION
SELECT MANAGER
FROM MYSCHEMA.MYTABLE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bsterner]

Ok, so I purposefully omitted the entire block of code because the goal was really to find an alternative to using 3 separate select statements (which means 3 separate passes on the table) with the 2 UNIONS. Here's the code in its entirety.

SELECT QUEUE
FROM
(
SELECT QUEUE
FROM MYSCHEMA.MYTABLE R1
UNION
SELECT SUPERVISOR AS QUEUE
FROM MYSCHEMA.MYTABLE R2
UNION
SELECT MANAGER AS QUEUE
FROM MYSCHEMA.MYTABLE R3
)
AS QUEUES
WHERE QUEUES.QUEUE NOT IN
(SELECT QUEUE_NAME
FROM QUEUE_SCHEMA.QUEUE_TABLE)

Is there a more efficient/clever way to accomplish this using JOINS, etc...

Is there a way to get rid of the surrounding SELECT statement?

Thanks for the prompt responses.

 

[PHV]

Perhaps this ?

SELECT R.QUEUE
  FROM MYSCHEMA.MYTABLE R
  LEFT JOIN QUEUE_SCHEMA.QUEUE_TABLE Q ON R.QUEUE = Q.QUEUE_NAME
 WHERE Q.QUEUE_NAME IS NULL
UNION SELECT SUPERVISOR
  FROM MYSCHEMA.MYTABLE R
  LEFT JOIN QUEUE_SCHEMA.QUEUE_TABLE Q ON R.SUPERVISOR = Q.QUEUE_NAME
 WHERE Q.QUEUE_NAME IS NULL
UNION SELECT MANAGER
  FROM MYSCHEMA.MYTABLE R
  LEFT JOIN QUEUE_SCHEMA.QUEUE_TABLE Q ON R.MANAGER = Q.QUEUE_NAME
 WHERE Q.QUEUE_NAME IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

the UNIONs are necessitated by the fact that you have three columns with the same domain in the same row

you can remove the outer SELECT by adding the WHERE clause to each SELECT in the UNION

r937.com | rudy.ca

 

[bsterner]

Thanks folks. Just needed a confirmation of my suspicions.

 

[fredericofonseca]

And note that depending on the number of rows returned by each individual select versus the ones that get rejected, using the "where" in each one may decrease significantly the number of rows selected and increase performance, specially if the columns are indexed.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com