Error in Syntax with field relating to a Stored Procedure.

I am getting the following error with the Code below. The Msg 170, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 222
Line 222: Incorrect syntax near 'Terr_id'.

I am clueless as to what to look for. Anythoughts are appreciated.


SELECT

      SS.PerType,
      CM.CM_SELLZONE,
      @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,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort,
      SS.Terr,
      SS.Item_no,
      Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
      sum(isnull(SS.[Gross_Sales],0))  AS Gross_Sales,
      sum(isnull(SS.[Cost],0))  AS Cost
      
      --,sum(isnull(SS.[Prior_Gross_Sales],0))  AS Prior_Gross_Sales,sum(isnull(SS.[Prior_Cost],0))  AS Prior_Cost
      
      FROM #SalesSummary AS SS
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                  ON PD.PRD_PRODUCT = SS.Prod_cat
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                         ON CM.CM_CUSTKEY = SS.Cus_No
      INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
      LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no

WHERE Rpt_Territory.ActiveFlag = 'Y'
GROUP BY
       SS.PerType,
     CM.CM_SELLZONE,
       Rpt_Territory.Terr_name,
       Rpt_Territory.Report_Sort,
       SS.Terr,
       SS.Item_no,
          Rpt_TerritoryAdopted.Terr_id
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
There aren't 222 lines here. It will be hard to fix.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In addition to the above, what is after the last line in the original question?
If the syntax is not correct, this could cause a compile error that would mention Terr_id.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Here is the full code.....I noticed there is no END statement or Final Select that pulls the SS.......or SalesSummary Table........could that be it.  I am not used to working on other peoples code and I am a novice. I do simple SPs that just pull all the data together. I am greatful for your help.


USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_RB]    Script Date: 10/02/2013 08:59:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- for testing

-- drop table #SalesSummary
-- 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

----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*'
BEGIN
  set @Cus_no_Start = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End   = right('000000000000' + @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
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select
   @CurrentWeek       = CurrentWeek,
   @NumberOfWeeks       = NumberOfWeeks,
   @CurrToDate_Period       = CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary
(
      --[data_source] varchar(12),--  what database is the data coming from  macola or rams?
      PerType varchar(18) NULL,
      Terr char(2) NULL,
      Item_no varchar(15),
      Gross_Sales decimal(38,2) NULL,
      Cost decimal(38,2) NULL,
      prod_cat Varchar (12),
      Cus_No Varchar (12)
      
      
)


----------------------------------------------------
-- insert for MTD Current and Prior
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2)
BEGIN
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN cost_amt ELSE 0 END)
   
       
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no

------------------------------------------------------
---- insert for MTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,

      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN cost_amt ELSE 0 END)
   
   
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no
------------------------------------------------------
---- insert for YTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '3 - YTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN cost_amt ELSE 0 END)
   
   
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
      WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no

INSERT INTO #SalesSummary
SELECT

      '3 - YTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN cost_amt ELSE 0 END)
   
   
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
   OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
   OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no
----------------------------------------------------
-- final select
----------------------------------------------------
if (@DB_Include = 1 or @DB_Include = 2)
BEGIN
SELECT

      SS.PerType,
      CM.CM_SELLZONE,
      @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,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort,
      SS.Terr,
      SS.Item_no,
      Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
      sum(isnull(SS.[Gross_Sales],0))  AS Gross_Sales,
      sum(isnull(SS.[Cost],0))  AS Cost
      
      --,sum(isnull(SS.[Prior_Gross_Sales],0))  AS Prior_Gross_Sales,sum(isnull(SS.[Prior_Cost],0))  AS Prior_Cost
      
      FROM #SalesSummary AS SS
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                  ON PD.PRD_PRODUCT = SS.Prod_cat
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                         ON CM.CM_CUSTKEY = SS.Cus_No
      INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
      LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no

WHERE Rpt_Territory.ActiveFlag = 'Y'
GROUP BY
       SS.PerType,
     CM.CM_SELLZONE,
       Rpt_Territory.Terr_name,
       Rpt_Territory.Report_Sort,
       SS.Terr,
       SS.Item_no,
          Rpt_TerritoryAdopted.Terr_id
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I noticed there is no END statement or Final Select that pulls the SS.......or SalesSummary Table........could that be it.
For starters, yes, every BEGIN must have an END.

I think John Lennon said that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
And every End has a Begining .....Billy Joel Said that.

I put END in at the bottom now I get a syntax error at END.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Tried this too.......Incorrect syntax near '#SalesSummary'.

END

         select * from #SalesSummary
0
SStoryCommented:
As I look through at your BEGIN END, the second one has no END. Neither does the last one.
All BEGIN statements must have an END
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That was it. Thank you.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your code.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.