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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Migrating Stored procedure from SQL 2000

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 44
The 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 ON

Select
M.LotNo ,
Max(M.TransactionDate) as Date,M.DyeingMill as DyeingMill,
Sum(M.Quantity) as SentMeters,Sum(M.Pcs) as SentPcs, M.GreyQuality
Into #Temp1
From
IssueForDyeing M
Where
M.DyeingMill = Case When @Mill <> '' then @Mill else M.DyeingMill End
Group By M.DyeingMill,M.LotNo,M.GreyQuality


Select
T.LotNo,
Sum(T.GreyRecPcs) as ReceivedPcs,Sum(T.GreyRecMtrs) as ReceivedMtrs, T.GreyQuality
Into #Temp2
From
ReceiveDyeingTransaction T
Where
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 BalPcs
Into #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 Status
from
#Temp3 temp , AcFile C , GreyQuality G
Where
temp.DyeingMill = C.ActCode And
temp.GreyQuality = G.GreyCode
Order By temp.LotNo,GreyQuality

GO




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;


Go to Top of Page
   

- Advertisement -