Extra Column in a UNION

[informer30]

Hi All,

I have a union query and in the first select statement I want to a coloumn as a reference which has no reference to the 2nd select statement...

How do I do that?

Select DUMMY.Ref, sum (AMOUNT) as AMOUNT
FROM
(select (SUBSTR (transfer_ref, 4, 10)) Ref,
(AMOUNT*-1)as AMOUNT
from Employee.Reference
union
select to_char((request_id)) as Ref,
AMOUNT as AMOUNT
from Old_Employee.Reference
)DUMMY
group by Ref
having sum (AMOUNT) <> 0

Many Thanks

 

[SantaMufasa]

I want to a coloumn as a reference which has no reference to the 2nd select statement...

I believe that you left out an important, clarifying word, above, that would help us better understand what you want to do. In any case, it appears to me that both of your UNION SELECTs have correlating columns, so I don't see a problem at this point. Can you clarify for us, please?


Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[informer30]

Hi Santa,

I want to add a coloumn as a reference which has no reference to the 2nd select statement...is that possible?

cheers

 

[sxschech]

You can add a field in your first Select Statement and then in the second Select Statement create a field with NULL. You would put in the actual col name you want to use for AdditionalCol.

Select AdditionalCol, DUMMY.Ref, sum (AMOUNT) as AMOUNT
FROM
(select AdditionalCol, (SUBSTR (transfer_ref, 4, 10)) Ref,
        (AMOUNT*-1)as AMOUNT
from   Employee.Reference
union
select NULL AS AdditionalCol, to_char((request_id)) as Ref,
       AMOUNT as AMOUNT
from Old_Employee.Reference
)DUMMY
group by AdditionalCol, Ref
having sum (AMOUNT) <> 0

If you only want a value and not an actual col, then you would put it in single quote --> 'Reference Data' AS AdditionalCol