Oracle gurus...please help with UNION query.

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. Received on Fri Jul 16 1999 - 09:45:09 CDT