I inherited a query that works in SQL 7 and does not work in SQL2000. One of the problem is data conversion and another, I think, is use of *= operator instead of LEFT JOIN. Is there any way to rewrite it with Left Join?
Appreciate your help.
SELECT d.Store_Rim 'Store #',
CEILING((isnull(sum(case when p.[Fixture Id]is not null and
p.Store_Rim is not null then
p.[Model Stock Min] a.[Shelf Capacity] end),0) +
isnull(sum(case when o.[Fixture Id]is null and
o.Store_Rim is not null and p.Store_Rim is null then
o.[Model Stock Min] a.[Shelf Capacity] end),0) +
isnull(sum(case when n.[Fixture Id]is not null and
n.Store_Rim is null and
o.Store_Rim is null and
p.Store_Rim is null then
n.[Model Stock Min] a.[Shelf Capacity] end),0))/100.000000) 'Model Stock Min' ,
CEILING((isnull(sum(case when p.[Fixture Id]is not null and
p.Store_Rim is not null then
p.[Model Stock Max] a.[Shelf Capacity] end),0) +
isnull(sum(case when o.[Fixture Id]is null and o.Store_Rim is not null and
p.Store_Rim is null then o.[Model Stock Max] a.[Shelf Capacity] end),0) +
isnull(sum(case when n.[Fixture Id]is not null and
n.Store_Rim is null and
o.Store_Rim is null and
p.Store_Rim is null then
n.[Model Stock Max] a.[Shelf Capacity] end),0))/100.000000) 'Model Stock Max' ,
b.Item_Id 'Selection #', b.Item_Desc 'Title' ,
ISNULL(l.Manual,l.Vol_Group) 'Volume Group'
from
catman..tblaltastore d,[Store Fixture] f,[Store Demographic] l ,
catman..tblaltatitle b,[Selection Fixture NEW] a,
min_max n,min_max o,min_max p,[Store #Filter] dd
where
d.Company=l.Company and d.Store_Rim=l.Store_Rim and
d.Company='BST' and f.[Status Code]=0 and
l.Store_Rim=f.Store_Rim and l.Company=f.Company and
l.[Status Code]=0 and n.[Status Code]=0 and
o.[Status Code]=0 and p.[Status Code]=0 and
n.Store_Rim is null and o.[Fixture Id] is null and
a.Company=n.Company and
a.Company=o.Company and
a.Company*=p.Company and
a.Upc=n.Upc and
a.Upc=o.Upc and
a.Upc*=p.Upc and
a.[Demographic List Id]=n.[Demographic List Id] and
a.[Demographic List Id]=o.[Demographic List Id] and
a.[Demographic List Id]*=p.[Demographic List Id] and
a.[Fixture Id]=n.[Fixture Id] and
a.[Fixture Id]=p.[Fixture Id] and
(n.[Model Stock Min] is not null or o.[Model Stock Min] is not null or
p.[Model Stock Min] is not null) and b.Upc=a.Upc and a.Company='BST' and
a.[Status Code]=0 and l.Company='BST' and f.[Fixture Id]=a.[Fixture Id] and
a.[Demographic List Id]=l.[Demographic List Id] and
f.Store_Rim=o.Store_Rim and
f.Store_Rim=p.Store_Rim and
d.Store_Rim=dd.filter and dd.[user]='xxx'
group by d.store_rim,b.item_id,b.item_desc,l.manual,l.vol_group
order by
d.Store_Rim , b.Item_Id , b.Item_Desc , ISNULL(l.Manual,l.Vol_Group)
