asked on

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

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

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

RUA Volunteer2?

ASKER

Should this be a new question.....??

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

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

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

Thanks for all your help