Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddb
Starting Member
1 Post |
 Posted - 2002-04-09 : 12:05:58
|
| I inherited query that runs OK in sql7 and does not return any data in sql2000. I suspect that *= operator produces this problem.Is it possible to rewrite this query with the 'left join' syntax? 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]=Convert(char(1),0) and l.Store_Rim=f.Store_Rim and l.Company=f.Company and l.[Status Code]=Convert(char(1),0) and n.[Status Code]=Convert(char(1),0) and o.[Status Code]=Convert(char(1),0) and p.[Status Code]=Convert(char(1),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]=Convert(char(1),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]='bayramd' group by d.store_rim,b.item_id,b.item_desc,l.manual,l.vol_grouporder by d.Store_Rim , b.Item_Id , b.Item_Desc , ISNULL(l.Manual,l.Vol_Group) |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-04-09 : 13:00:38
|
| Yes you can rewrite this. Check BOL for syntax. Topic shold be From. |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-09 : 13:09:56
|
| ddb,the old-school *= syntax is not ANSI compliant. you should use the ANSI-standard [right|left|full] OUTER JOIN syntax.setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
*= operator in sql2000