Author |
Topic |
ladak
Starting Member
13 Posts |
Posted - 2014-11-19 : 03:52:04
|
I everyone,I have a stored procedure designed in sql server 2000, when i run this query it shows an error :g 4147, Level 15, State 1, Procedure BalanceLotItems, Line 44The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification,here is syntax of stored procedure :quote: CREATE PROCEDURE BalanceLotItems @Mill as varchar(30)AS SET NOCOUNT ONSelect M.LotNo , Max(M.TransactionDate) as Date,M.DyeingMill as DyeingMill, Sum(M.Quantity) as SentMeters,Sum(M.Pcs) as SentPcs, M.GreyQualityInto #Temp1From IssueForDyeing MWhere M.DyeingMill = Case When @Mill <> '' then @Mill else M.DyeingMill EndGroup By M.DyeingMill,M.LotNo,M.GreyQualitySelect T.LotNo, Sum(T.GreyRecPcs) as ReceivedPcs,Sum(T.GreyRecMtrs) as ReceivedMtrs, T.GreyQuality Into #Temp2From ReceiveDyeingTransaction TWhere DyeingMill=@Mill AND T.DyeingMill+T.LotNo in (Select DyeingMill+LotNo From #Temp1) Group By DyeingMill,LotNo,GreyQuality Select Temp1.*, Isnull(Temp2.ReceivedPcs,0) as ReceivedPcs, Isnull(Temp2.ReceivedMtrs,0) as ReceivedMeters, Temp1.SentMeters - Isnull(Temp2.ReceivedMtrs,0) as BalMeters,Temp1.SentPcs - Isnull(Temp2.ReceivedPcs,0) as BalPcsInto #Temp3 From #Temp1 Temp1, #Temp2 Temp2 Where Temp1.LotNo *= Temp2.LotNo And Temp1.GreyQuality *= Temp2.GreyQuality select temp.LotNo LotNo , Convert(varchar(12),temp.Date,103) Date,Convert(varchar(15),temp.SentMeters) SentMeters, Convert(varchar(15),temp.SentPcs) SentPcs,Convert(varchar(15),temp.ReceivedMeters) ReceivedMeters, Convert(varchar(15),temp.ReceivedPcs) ReceivedPcs,Convert(varchar(15),temp.BalMeters) BalMeters, Convert(varchar(15),temp.BalPcs) BalPcs, temp.DyeingMill,C.ActName as MillName, temp.GreyQuality, G.GreyName, 0 as Statusfrom #Temp3 temp , AcFile C , GreyQuality GWhere temp.DyeingMill = C.ActCode And temp.GreyQuality = G.GreyCode Order By temp.LotNo,GreyQualityGO
here you can see our company's previous programmer used *= in where clause, i have almost 37 stored procedures with same non-ANSI outer join operators ("*=" or "=*"), please guide me easiest way to replace *= with left outer join.Thanks in advance.Ladak |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-11-19 : 08:13:59
|
With the old outer join syntax, the * is against the table where all rows are preserved.Here, as the order of tables in the FROM clause is the same as the order of the tables in the WHERE clause, you have a simple conversion to a LEFT OUTER JOIN:SELECT ...FROM #Temp1 T1 LEFT JOIN #Temp2 T2 ON T1.LotNo = T2.LotNo AND T1.GreyQuality = T2.GreyQuality; |
|
|
|
|
|