Response is inline.
In article <7mngi1$h73$1_at_nnrp1.deja.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)
You may add the dummy columns with "NULLS". IE:
=> SELECT D.LOGICAL_NAME,
added=> NULL, NULL, NULL, NULL, NULL, NULL
=> FROM HFS_DEVICE D WHERE D.LOGICAL_NAME NOT IN
=> (SELECT P.LOGICAL_NAME FROM HFS_PROBLEM P)
HTH
James
>
> 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.
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Jul 16 1999 - 10:26:33 CDT
