Hi,

I'm working on fine tuning a complex db2 query which consists of multiple select statments combined by UNIONs and i need to avoid UNIONs

Below is an example of my tables and part of my query.

Your help is appreciated and my advance thanks!!!


TRANSACTION Table
-------------------
TRANS_NO
TRANS_DATE
TRANS_LOC
TRANS_AMT
TRANS_ENO
TRANS_DEPT

EMPLOYEE Table
---------------
EMP_NO
EMP_NAME
EMP_DOB
EMP_ADDRESS


EMPLOYEE_TEMP Table
----------------------
ETEMP_NO
ETEMP_NAME
ETEMP_DOB
ETEMP_ADDRESS

I want to combine the above 3 tables. TRANSACTION table is the main table which refers details from either EMPLOYEE or EMPLOYEE_TEMP table.

TRANSACTION table first needs to do a equi-join (TRANS_ENO = EMP_NO) with EMPLOYEE table to get the employee name (EMP_NAME). If the match is not found in EMPLOYEE table, TRANSACTION table has to do another equi-join (TRANS_ENO = ETEMP_NO) with EMPLOYEE_TEMP table.

And if the Employee name is not found in both the tables, "Employee Name not found" should be returned for that particular record/row.

Pls. note that UNIONs should not be used.

The final result should be sorted on "Employee name" column

Sample result (result sorted by EMP_Name)
------------
TRANS_NO TRANS_DATE TRANS_LOC TRANS_AMT EMP_NAME
4444 1/1/2009 NJ $100 AAAA
1111 5/1/2009 CA $900 BBBB
2222 3/1/2009 PA $600 Emp Name not found
3333 8/1/2009 NY $200 ZZZZ

Can you explain why?

You could achieve the desired result by using outer joins and the COALESCE function.

UNIONs degrated the performance of my query

actually unions do not degrade theperformance of the query it is how well the query is written and the existence of proper index structures that will decide the performance.
Besides all of that I think this should be a very simple query like:
Select value(emp.emp_no,temp.emp_no,'employee not found')
from transaction tr
left outer join employee emp
on tr.TRANS_ENO = emp.EMP_NO
left outer join employee_temp temp
on tr.TRANS_ENO = temp.EMP_NO
where......

Dave Nance

My question is if additional condition would be effective for performance.