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

Ok, Just figured it out! for the
ORA-01790: expression must have same datatype as corresponding expression
Error, just use TO_NUMBER(NULL) etc. etc. To force the field to match the datatype of the corresponding field. Duh.

In article <37901EBE.50D6_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> 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."
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jul 31 1999 - 22:03:30 CDT