CODEWITHSUNDEEP
db2query-optimization
A Programmer
A Programmer

Reputation: 378

Can any one help me to rewrite the below query in db2

Here is the query

select *
FROM 
WEBSS.P0022_INVENTORY_ACTIVITY_LOG I LEFT OUTER JOIN WEBSS.P0029_LOCATION L2 ON I.INVT_XFER_LOC_ID = L2.LOC_ID,
WEBSS.P0029_LOCATION L
WHERE 
I.LOC_ID = L.LOC_ID 
UNION ALL 
select *
FROM 
WEBSS.P0022_INVENTORY_ACTIVITY_LOG I LEFT OUTER JOIN WEBSS.P0029_LOCATION L2 ON I.LOC_ID = L2.LOC_ID,
WEBSS.P0029_LOCATION L
WHERE 
I.INVT_XFER_LOC_ID = L.LOC_ID;

Can you plz tell me hw can i use the second query in effective manner.

When you look at the query u can find there is a small change in the condition.. so for that do we really need to go for two queries nwith union all..

I need to tune the second query ie..any possible way to rewrite the second query to make good performance.. any help on this?

Thanks in advance.

Upvotes: 0

Views: 89

Answers (1)

JJschk
JJschk

Reputation: 431

I am not an expert in db2, but sql statements are generally the same

  • UNION ALL returns also duplicates
  • use only Union in order not to return duplicates
  • if you do not add "()" in the union it will be completed it will do the unions in the order you write them http://msdn.microsoft.com/en-us/library/ms180026.aspx
  • why do you select from WEBSS.P0029_LOCATION L and not use also a left join? I would use two left joins and is it

Upvotes: 1

Related Questions