Q
Problem solve Get help with specific problems with your technologies, process and projects.

UNION-compatible columns

I have two SQL statements (shown below). They both have label and id fields. However, because the second select...

uses a count on a column to find the number of processors, if I create a single view from them I think the count will then be incorrect.

select
    COMPUTER.LABEL,
    COMPUTER.ID,
    COMPUTER.OS_NAME,
    SWARE_SIG.SWARE_DESC,
from
    MATCHED_SWARE,SWARE_SIG,COMPUTER
where
    MATCHED_SWARE.ID = COMPUTER.ID
and
    MATCHED_SWARE.SWARE_SIG_ID 
       = SWARE_SIG.SWARE_SIG_ID

       
select COMPUTER.LABEL,
    COMPUTER.ID,
    PROCESSOR.PROCESSOR_ID,
    count(*) as NUM_PROCESSOR
from
    COMPUTER,PROCESSOR
where
    COMPUTER.ID = PROCESSOR.ID
group by
    COMPUTER.LABEL,
    COMPUTER.ID,
    PROCESSOR.PROCESSOR_ID

How can I create a single query that returns both sets of information in one report?

The easiest way to combine those two queries is with a UNION of their result sets. In order to make the combined results usable, it's important to:

  1. include some column which identifies, for each row, the SELECT that produced it

  2. ensure that each SELECT produces the same number of columns

  3. ensure that all columns of each SELECT are UNION-compatible

Here is a UNION that should work for you:

select COMPUTER.LABEL
     , COMPUTER.ID
     , 'Operating System'     as rowtype
     , COMPUTER.OS_NAME       as rowvalue1
     , SWARE_SIG.SWARE_DESC   as rowvalue2
  from COMPUTER
inner
  join MATCHED_SWARE
    on COMPUTER.ID = MATCHED_SWARE.ID 
inner  
  join SWARE_SIG
    on MATCHED_SWARE.SWARE_SIG_ID 
         = SWARE_SIG.SWARE_SIG_ID
union all

select COMPUTER.LABEL
     , COMPUTER.ID
     , 'Processor         '   as rowtype
     , PROCESSOR.PROCESSOR_ID as rowvalue1
     , cast(count(*) as varchar) as rowvalue2
  from COMPUTER
inner
  join PROCESSOR
    on COMPUTER.ID = PROCESSOR.ID
group 
    by COMPUTER.LABEL
     , COMPUTER.ID
     , PROCESSOR.PROCESSOR_ID

The result set that you get back from the UNION will have a column called "rowtype" which will distinguish which SELECT the row came from. This is often necessary but occasionally superfluous (as in this case, most likely).

UNION-compatible means that you must align numbers with numbers, strings with strings, and datetimes with datetimes. In this example, the column in the result set called "rowvalue2" will have string values, hence it is necessary to CAST the numeric count as VARCHAR.

This was last published in May 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close