0

Hi

I have an issue in store procedure. Error is
Msg 4104, Level 16, State 1, Procedure W_usp_SO_Table, Line 38
The multi-part identifier "dbo.SVendorMaster.SVCode" could not be bound.

Insert table is working fine. But update command is prompt the error

Pls advice me

Maideen

Here is SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[W_usp_SO_Table]
As
Begin
TRUNCATE TABLE [dbo].[SOMasterTEMP]

INSERT INTO [dbo].[SOMasterTEMP] ( svcode, svname, mvcode, mvname, distcode, distname, routecode, routename, TransportM, 
                                  area, seqno, mon, tue, wed, thu, fri, sat, sun, holi, edition, rate, svstatus, mvstatus, 
                                  diststatus, routestatus, type)

SELECT      dbo.SVendorMaster.SVCode, dbo.SVendorMaster.SVName, dbo.SVendorMaster.MVCode, dbo.MVendorMaster.MVName, 
            dbo.SVendorMaster.DistCode, dbo.DistributorMaster.DistName, dbo.DistributorMaster.RouteCode, dbo.RouteMaster.RouteDesc, 
            dbo.RouteMaster.TransporterM, dbo.RouteMaster.Area, dbo.SVendorMaster.SeqNo, dbo.SVendorMaster.Mon, dbo.SVendorMaster.Tue, 
            dbo.SVendorMaster.Wed, dbo.SVendorMaster.Thu, dbo.SVendorMaster.Fri,dbo.SVendorMaster.Sat, dbo.SVendorMaster.Sun, 
            dbo.SVendorMaster.Holi, dbo.MVendorMaster.Edition, dbo.MVendorMaster.Rate,dbo.SVendorMaster.AStatus AS SVStatus, 
            dbo.MVendorMaster.AStatus AS MVStatus, dbo.DistributorMaster.Astatus AS DistStatus,dbo.RouteMaster.AStatus AS RouteStatus, 
            dbo.MVendorMaster.Type
FROM        dbo.RouteMaster INNER JOIN
            dbo.DistributorMaster ON dbo.RouteMaster.RouteCode = dbo.DistributorMaster.RouteCode INNER JOIN
            dbo.SVendorMaster INNER JOIN
            dbo.MVendorMaster ON dbo.SVendorMaster.MVCode = dbo.MVendorMaster.MVCode ON 
            dbo.DistributorMaster.DistCode = dbo.SVendorMaster.DistCode
WHERE       (dbo.SVendorMaster.AStatus = N'A') AND (dbo.MVendorMaster.AStatus = N'A') 
            AND (dbo.DistributorMaster.Astatus = N'A') AND (dbo.RouteMaster.AStatus = N'A')


/*  update qty based on days*/

Declare @DOW NVARCHAR(20)

SET @Dow='SELECT DATEName(dw,GETDATE()) as DaysName'

SELECT dbo.SVendorMaster.svcode, dbo.SVendorMaster.MON,dbo.SVendorMaster.TUE,dbo.SVendorMaster.WED,dbo.SVendorMaster.THU,
       dbo.SVendorMaster.FRI, dbo.SVendorMaster.SAT,dbo.SVendorMaster.SUN,dbo.SVendorMaster.HOLI 
       FROM [dbo].[SVendorMaster] INNER JOIN dbo.SOMasterTEMP ON dbo.SVendorMaster.svcode=dbo.SOMasterTEMP.svcode

IF @DOW ='Monday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Mon WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW ='Tuesday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Tue WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Wednesday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.wed WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW  = 'Thursday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.thu WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW ='Friday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.fri WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Saturday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.sat WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Sunday'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.sun WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Holi'
    UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.holi WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode



END
2
Contributors
1
Reply
12
Views
2 Years
Discussion Span
Last Post by hiren2927
0

You are updating while simultaneously joining two tables.
So this is what you could try

UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Mon
FROM dbo.SOMasterTEMP,dbo.SVendorMaster
WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.