I get two seperate errorsInsert Error: Column name or number of supplied values does not match table definition

Getting the following error from the code attached.

Msg 213, Level 16, State 4, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 82
Insert Error: Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 4, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 112
Insert Error: Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 4, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 142
Insert Error: Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 4, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 174
Insert Error: Column name or number of supplied values does not match table definition.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 353
The column prefix 'CM' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 353
The column prefix 'CM' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 353
The column prefix 'CM' does not match with a table name or alias name used in the query.
SP-Rpt-SalesSummaryAdopted-sproc.xlsx
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.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In line 91 of the XLSX a comma is missing, so you have a value of 'Macula' with a column name of '2 - MTD Current'. Same for all similar SQLs, as indicated by the error message (add 9 to each line number to match those in the XLSX).
0
Aneesh RetnakaranDatabase AdministratorCommented:
the error messages are very clear

there is no table referred by 'CM'  in this query, you should change this

SELECT DISTINCT                                                      
                                                      
      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,                                                
      SSItem_no,                                                 
      Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,                                                
      sum(isnull(Rpt_Territory.[Gross_Sales],0))  AS Gross_Sales,                                                
      sum(isnull(Rpt_Territory.[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 SS                                                
            INNER JOIN Rpt_Territory                                           
                  ON Rpt_Territory.Terr_id = SS.Terr                                    
            LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted                                           
                  ON Rpt_Territory.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            


The first error. you are inserting 7 columns to a table that has only 6 columns
            'RAMS'                                          
            '1 - Weekly',                                          
            CM.CM_SELLZONE,                                          
            SUBSTRING(ID.ID_DESC, 3, 2) AS Terr,                                          
            OD.OD_PRODUCT as Item_no,                                          
            Gross_Sales = SUM(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
                                                      AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrWeekBeginDate AND @CurrWeekEndDate)
                                             THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)            
                                             ELSE 0 END),            
            Cost = SUM(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')
                                                      AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrWeekBeginDate AND @CurrWeekEndDate)
                                             THEN      COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)      
                                             ELSE 0 END)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>INSERT INTO #SalesSummary
For starters, you're playing with fire when you have an INSERT statement that doesn't explicitly spell out the table columns, as it becomes dependant on the SELECT clause matching exactly what the table definition is.  If anyone adds/deletes a column from the table, these statements will then throw an error.

This alone might flush out the problem.

Also make sure all of the Item_No columns that are being inserted is also a varchar, as that's how it's defined in the temp table.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw  #SalesSummary  is defined with six columns, and the INSERT beginning at line 354 has way more. The columns in the INSERT and SELECT have to match.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I think I almost have it. I have corrected many of the fields and number of fields in each Query match. Here is the latest code error relating to the bottom of the code. Line 411.

Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 411
Invalid column name 'Terr'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 411
Invalid column name 'item_no'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 411
Invalid column name 'item_no'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 411
Invalid column name 'Gross_Sales'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 411
Invalid column name 'Cost'.

I do not understand what is wrong with those sections. I cannot really identify the error.
SP-Rpt-SalesSummaryAdopted-sproc.xlsx
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Should this be a new question.....??
0
Aneesh RetnakaranDatabase AdministratorCommented:
the error message itself is clear and pls don't put the query on excel sheet,
there is no SS column on #salesSummary, you have commented out that column on your insert queries  (--SUBSTRING(ID.ID_DESC, 3, 2) AS Terr,      )


You need to ensure that you are joining to the proper table here  (INNER JOIN Rpt_Territory       )
Seems like that table doesn't contain the fields Item_no, Gross_Sales and Cost
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Here is what I did with the last part of the SP. The attached also shows what the Rpt_Territory has in it. It has the Start_Item_no and the End_Item_no in it. Could that be causing the problem. It was suggested I put SS in front of the code for Gross Sales and Cost. That worked now I only have the three errors.

Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 420
Invalid column name 'Terr'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 420
Invalid column name 'item_no'.
Msg 207, Level 16, State 3, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 420
Invalid column name 'item_no'.


SP Statement.........if there is a better way to get the data to you please let me know I will do that in the future.

if (@DB_Include = 1 or @DB_Include = 2)
BEGIN

INSERT INTO #SalesSummary

SELECT DISTINCT

      SS.data_source,
      SS.PerType,
      SS.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,
      SS.Item_no,
      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 SS
            INNER JOIN Rpt_Territory
                  ON Rpt_Territory.Terr_id = SS.Terr
            LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
                  ON Rpt_Territory.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no

WHERE Rpt_Territory.ActiveFlag = 'Y'
GROUP BY
       SS.PerType,
     SS.CM_SELLZONE

          
          
END



         select * from #SalesSummary
Rpt-territory-table.png
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
#SalesSummary doesn't contain a column TERR, and Rpt_Territory no ITEM_NO .
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:
Thanks for all your help
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.