Re: Oracle gurus...please help with UNION query.

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 17 Jul 1999 14:12:14 +0800
Message-ID: <37901EBE.50D6@yahoo.com>

mitch23_at_hotmail.com wrote:
>
> Hi guys,
>
> I'm trying to do a UNION statement with two queries that do not have
> matching select statements..meaning not all the fields from the first
> select also exist in the second select. I read somewhere that the
> requirement that the field lists from both statements match can be
> overcome by using dummy field names...can anyone give me an idea how
> this would be done?
>
> BTW, Here's the statement I'm trying to run
>
> SELECT C1.*, D1.* FROM (SELECT B1.* FROM (SELECT A.LOGICAL_NAME FROM
> HFS_PROBLEM A WHERE A.CATEGORY = 'INVENTORY LOCATION PROBLEM') A1,
> (SELECT B.logical_name, b.category FROM HFS_PROBLEM B) B1 WHERE
> A1.LOGICAL_NAME(+) = B1.LOGICAL_NAME AND A1.LOGICAL_NAME IS NULL) C1,
> (SELECT D.LOGICAL_NAME DEVICELN, D.TYPE, D.LOCATION_CODE, D.SUBTYPE,
> D.SERIAL_NO_ FROM HFS_DEVICE D WHERE D.ISTATUS = 'MISSING') D1 WHERE
> D1.DEVICELN = C1.LOGICAL_NAME
>
> UNION
>
> SELECT D.LOGICAL_NAME FROM HFS_DEVICE D WHERE D.LOGICAL_NAME NOT IN
> (SELECT P.LOGICAL_NAME FROM HFS_PROBLEM P)
>
> So I need to add 6 more dummy fields to the second statement to avoid
> this error:
>
> ORA-01789: query block has incorrect number of result columns
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Just use "null" to pad the number of columns...

select col1, col2, col3
from table1
union
select null, xyz, null
from table2
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Sat Jul 17 1999 - 01:12:14 CDT