Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America asked on

Invalid Column Name SQL Server Stored Procedure Error

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
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Lawrence Barnes

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RUA Volunteer2?

It is a 2008 Server....so I do not think we need it.....right...?
I am surprised we never got an error before.
Lawrence Barnes

Ok.  So then it is the repeated column name from above and this also aligns with your error message.

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)
Lawrence Barnes

Yes...since you have SQL 2008 the declare as you have it is fine.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
RUA Volunteer2?

Gosh how do coders ever figure out they have duplicate rows like that. There has to be a tool that will help find anomalies in the code like duplicates or something.....any ideas on simple tools.
By the way thank you sir......!
PortletPaul

:) a text editor that supports sorting?

and/or declare in alpha order (can make life easier in the long run)

this sorted list by Notepad++
   @CurrFromDate             = CurrFromDate,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorFromDate             = PriorFromDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDate             = PriorToDate
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,

Open in new window

but the point is taken - a great IDE would at least warn you.