The following query runs fine in SQL Server but cracks when run in Db2. Is there anything I can do to optimise it? Any help would be appreciated.
select i.itemkey, ix.itemname, ix.itemshortname, ix.color, ix.itemsize, ip.price, ip.saleprice, ip.sale_price_start, ip.sale_price_end, i.departmentid, i.collectionid, i.categoryid, i.subcategoryid, i.brandid, i.itemtype, i.rcd_stat,d.departmentname, b.brandname, cx.collectionname, cg.description as categoryname, scg.description as subcategoryname
from dbadmin.itemmaster i join dbadmin.itemmasterxlate ix on ( i.itemkey = ix.itemkey and i.retailerid = ix.retailerid and ix.languageid = 0 and translate( ix.itemname ) like '%CRUST%' )
join dbadmin.itemprice ip on ( i.itemkey = ip.itemkey and i.retailerid = ip.retailerid and ip.regionid = 0 )
left outer join dbadmin.brand b on ( i.brandid is not null and i.brandid = b.brandid )
join dbadmin.departmentxlate d on ( i.departmentid = d.departmentid and d.languageid = 0 )
left outer join dbadmin.collectionxlate cx on ( i.collectionid is not null and i.collectionid = cx.collectionid and cx.retailerid = 0 and cx.languageid = 0 )
left outer join dbadmin.categoryxlate cg on ( i.categoryid is not null and i.categoryid = cg.categoryid and i.departmentid = cg.departmentid and cg.languageid = 0 )
left outer join dbadmin.subcategoryxlate scg on ( i.subcategoryid is not null and i.categoryid is not null and i.categoryid = scg.categoryid and i.subcategoryid = scg.subcategoryid and i.departmentid = scg.departmentid and scg.languageid = 0 )
left outer join dbadmin.collections c on ( i.collectionid is not null and i.collectionid != 0 and i.collectionid = c.collectionid and i.retailerid = c.retailerid )
join dbadmin.department dept on ( dept.departmentid = i.departmentid )
where i.retailerid = 0 order by dept.sequence, b.brandname, c.displaysequence, i.displaysequence
Please note that if I use a right join for itemprice, its gives me results.
select i.itemkey, ix.itemname, ix.itemshortname, ix.color, ix.itemsize, ip.price, ip.saleprice, ip.sale_price_start, ip.sale_price_end, i.departmentid, i.collectionid, i.categoryid, i.subcategoryid, i.brandid, i.itemtype, i.rcd_stat,d.departmentname, b.brandname, cx.collectionname, cg.description as categoryname, scg.description as subcategoryname
from dbadmin.itemmaster i join dbadmin.itemmasterxlate ix on ( i.itemkey = ix.itemkey and i.retailerid = ix.retailerid and ix.languageid = 0 and translate( ix.itemname ) like '%CRUST%' )
join dbadmin.itemprice ip on ( i.itemkey = ip.itemkey and i.retailerid = ip.retailerid and ip.regionid = 0 )
left outer join dbadmin.brand b on ( i.brandid is not null and i.brandid = b.brandid )
join dbadmin.departmentxlate d on ( i.departmentid = d.departmentid and d.languageid = 0 )
left outer join dbadmin.collectionxlate cx on ( i.collectionid is not null and i.collectionid = cx.collectionid and cx.retailerid = 0 and cx.languageid = 0 )
left outer join dbadmin.categoryxlate cg on ( i.categoryid is not null and i.categoryid = cg.categoryid and i.departmentid = cg.departmentid and cg.languageid = 0 )
left outer join dbadmin.subcategoryxlate scg on ( i.subcategoryid is not null and i.categoryid is not null and i.categoryid = scg.categoryid and i.subcategoryid = scg.subcategoryid and i.departmentid = scg.departmentid and scg.languageid = 0 )
left outer join dbadmin.collections c on ( i.collectionid is not null and i.collectionid != 0 and i.collectionid = c.collectionid and i.retailerid = c.retailerid )
join dbadmin.department dept on ( dept.departmentid = i.departmentid )
where i.retailerid = 0 order by dept.sequence, b.brandname, c.displaysequence, i.displaysequence
Please note that if I use a right join for itemprice, its gives me results.