hi Chiranjeevi

thanks for ur help but i can't eliminate the use of distinct as i want to select distinct rows from each the tables with particular combination which i select and after selecting the distinct combination from both tables i have to union them so please suggest some other solution if u can thanks once again.

instead of using distinct use group by....

its faster..

group by is pre condition where as distinct is post condition so groupy by is expctd to b much faster and the diffrence is quite visible if u hav lots of data

create a view of the above query without using distinct clause and then execute query by using view with distinct clause and group by function.

If you are using the UNION keyword then there is no need of using the distinct keyword in your query or else you can use UNION ALL keyword

or

Below I am sending the modified query

In your query which column will filter more then that column you have to place in the last position less filter before that last column and which one will filter least that column you have to place in the first position in the where clause.

SELECT DISTINCT
A.BUSINESS_UNIT
A.CUST_ID
A.ASOF_DT
FROM PS_ITEM_ACTIVITY A
WHERE A.PO_LINE 0
AND A.BUSINESS_UNIT '1100G'
AND A.ENTRY_REASON <> 'CLEAR'
AND A.ASOF_DT > '01-JAN-1900'
AND A.USER1 ' '
AND A.ITEM LIKE 'OA '

UNION

SELECT DISTINCT
A.BUSINESS_UNIT
A.CUST_ID
A.ASOF_DT
FROM PS_PENDING_ITEM A
WHERE A.PO_LINE 0
AND A.BUSINESS_UNIT '1100G'
AND A.ENTRY_REASON <> 'CLEAR'
AND A.ASOF_DT > '01-JAN-1900'
AND A.USER1 ' '
AND A.POSTED_FLAG 'N'
AND ERROR_FLAG ' '
AND A.ITEM LIKE 'OA '

Thanks & Regards
NTR