Union Query

[informer30]

Hi All,

I have 2 select statements, where I have placed a union....The part I do not understand is the top 4 rows returned are bringing back a references that do not exist in either of the individual select statements.

For e.g. when I run ref = 2 in the individual sql statements I cannot get the row back it does not exist, however when I union these rows are appearing...

Any Ideas?


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]

Informer30,

Would you please run each query individually for us, then run the UNION query, followed, in each case, by the results you receive? (i.e., just copy and paste the results of a SQL*Plus session in which you run the three SELECTs.)

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]

Hey Santa,

my fault, I was not using the substring function to look for the references...hence they where not appearing...

Thanks