troubleshooting Question

Invalid Column Name SQL Server Stored Procedure Error

Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
6 Comments1 Solution2682 ViewsLast Modified:
In the code below there is an error of Invalid Column Name in the Stored Procedure in our SQL. It breaks at this line below...."[CurrWeekBeginDate] int Null
    )"

Can anyone identify what the problem is.....? Please advise. Thank you.


USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_TEST_sproc_RAMS_RB]    Script Date: 09/12/2013 18:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_TEST_sproc_RAMS_RB]
      (
            @Cus_no_Start            varchar(12),
            @Cus_no_End                  varchar(12),
--            @Rams_Cus_no_Start      varchar(12),
--            @Rams_Cus_no_End      varchar(12),
            @DB_Include                  int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS

      ) AS

-- for testing
-- drop table #weeklytemp
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'
-- end testing
/* note:
8 columns
WW = col 1 report_sort = 10
EW = col 2 report_sort = 20
OR = col 3 report_sort = 30
AK = col 4 report_sort = 40
MW = col 5 report_sort = 185
NC = col 6 report_sort = 50
MI = col 7 report_sort = 110
FS = col 8 report_sort = 70
*/
----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*' and @Cus_no_End <> '*'  
BEGIN
  set @Cus_no_Start                  = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End                  = right('000000000000' + @Cus_no_End,12)
END

/***************************************************************************
-- Taking out the selection for RAMS customers
If @Rams_Cus_no_Start <> '*' and @Rams_Cus_no_End <> '*'
BEGIN
  set @Rams_Cus_no_Start      = right('000000000000' + @Rams_Cus_no_Start,12)
  set @Rams_Cus_no_End            = right('000000000000' + @Rams_Cus_no_End,12)
END
*****************************************************************************/

-- print @Cus_no_Start
-- print @Cus_no_End
----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrFromDate             = CurrFromDate,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate             = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK)

declare @CurrentWeek decimal(8,0),
            @NumberOfWeeks int,
            @CurrToDate_Period tinyint,
            @CurrToDateYTD_FiscalYear smallint
declare @CurrToDate_pSDt datetime,
            @CurrToDate_pSDt7 datetime

select
      @CurrentWeek                         = CurrentWeek,
      @NumberOfWeeks                         = NumberOfWeeks,
      @CurrToDate_Period                   = CurrToDate_Period,
      @CurrToDateYTD_FiscalYear      = CurrToDateYTD_FiscalYear,
      @CurrToDate_pSDt                  = CurrToDate_PeriodStartDate,
      @CurrToDate_pSDt7                  = CurrToDate_PeriodStartDate+(7*vwFiscalDates.NumberOfWeeks-1)

from dbo.vwFiscalDates  WITH (NOLOCK)


----------------------------------------------------------------------------------------------------
--  below is pulling data from macola into temp table weeklytemp
CREATE TABLE #weeklytemp (
      [CurrWeekEndDate] [int] NULL ,
      [CurrToDate] [int] NULL ,
      [CurrToDateYTD] [int] NULL ,
      [CurrentWeek] [decimal](8, 0) NULL ,
      [NumberOfWeeks] [int] NULL ,
      [CurrToDate_Period] [tinyint] NULL ,
      [CurrToDateYTD_FiscalYear] [smallint] NULL ,
      [Terr_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Report_Sort] [int] NULL ,
      [Terr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustNo] [varchar] (24)       COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,      
      [TerrAdopted] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Adopted_Sort] [int] NULL ,
      [WeekNumber] [int] NULL ,
      [Week1_EndDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TICKET_NUMBER] varchar (10) NULL,
      [Item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [RETURN_REASON_CODE] varchar (6) null,
      [FINAL_PRICE] decimal (38,2) null,
      [TOTAL_DELIV_QUANTITY] int null,
      [Week_Sls_Amt] [decimal](38, 2) NULL ,
      [Week_Cost_Amt] [decimal](38, 2) NULL,
      [Date_id] [datetime] null,
      [qty_to_ship] varchar (10) null,
      [inv_dt] datetime,  
      [prod_cat] int, --left([prod_cat],2) int, --as ProdCatNo,
      
    [CurrWeekBeginDate] int Null
    )
   
   -- @PriorWeekBeginDate [Int] Null,
   -- @PriorWeekEndDate [Int] Null
      --   end pulling data into temp table
      
      
--------------------------------------------------------
--RAMS Section
-------------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both Macola and RAMS data
BEGIN

insert  into #weeklytemp

      SELECT
            @CurrWeekEndDate as CurrWeekEndDate,
            @CurrToDate as CurrToDate,
            @CurrToDateYTD as CurrToDateYTD,
            @CurrentWeek as CurrentWeek,
            @NumberOfWeeks as NumberOfWeeks,
            @CurrToDate_Period as CurrToDate_Period,
            @CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
            ID.ID_DESC as Terr_name,
--            Right(ID.IDVALUE,4) as Rpt_Sort,
            RptTerr.Report_Sort,
            SUBSTRING(ID.ID_DESC, 3, 2) AS Terr,
            OH.OH_CUSTOMER_NUMBER,
            CM.CM_FULLNAME,
            Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
            Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
            Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
            FLOOR((CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt,112))/7+1) AS WeekNumber,
            CONVERT(char(8), @CurrToDate_pSDt+(7*FLOOR(CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt)/7+1)-1),112) AS Week1_EndDate,
            OD_TICKET_NUMBER,
            OD.OD_PRODUCT as Item_no,
            OD.OD_RETURN_REASON_CODE,
            OD.OD_FINAL_PRICE,
            OD.OD_TOTAL_DELIV_QUANTITY,
            Week_Sls_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                      AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) -- Sale or BuyBack
                                             THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                             ELSE 0 END),
            Week_Cost_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001'))                                                                                                                                                                   
                                             THEN      COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                             ELSE 0 END),
           OH.OH_delivery_date,
           OD_TOTAL_DELIV_QUANTITY,
           OH.OH_Delivery_Date,
         PD.PRD_A_GLTYPE ,  --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
            CurrWeekBeginDate
            --[PriorWeekBeginDate] ,
            --[PriorWeekEndDate]
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  --WITH NOLOCK
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  --WITH NOLOCK
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM.CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as ID  --WITH NOLOCK
                  ON CM.CM_SELLZONE = ID.IDVALUE
            INNER JOIN dbo.Rpt_Territory as RptTerr on RptTerr.Terr_id  = SUBSTRING(ID.ID_DESC, 3, 2) --WITH NOLOCK
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD       --WITH  (NOLOCK)
                  ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER                                                
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD -- WITH (NOLOCK)
                  ON PD.PRD_PRODUCT = OD.OD_PRODUCT      
            
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted  WITH (NOLOCK)
                  ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no
            
            WHERE
                  --(CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrToDate_pSDt AND @CurrToDate_pSDt7)      
                   OH.OH_SALE_TYPE = 'O'
                  AND (ID.ID_TITLE = N'Sale Zone')
END

----------------------------------------------------
--End RAMS Section
----------------------------------------------------


----------------------------------------------------
-- final select
----------------------------------------------------
select * from #weeklytemp
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros