String or binary data would be truncated. Microsoft SQL Server 2008

When I run this code I get the MACOLA data back but not the RAMS data back. I cannot seem to figure out what I did wrong. Also I am getting the
I keep getting the following error with the code below. Problem is the only thing that was changed from the SQL Statement was the last 3 lines of the FROM clause under the RAMS section. Before that the code worked perfectly.

The ERROR:
Msg 8152, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_MAC_RAMS, Line 146
String or binary data would be truncated.

Can anyone see the problem here or tell me where i need to look to possibly find it. I took out the TEMP table and the RAMS data returns records. So I know or "THINK" (which could be deadly for me) that it is in the TEMP table at the top. Again the trouble is the code was working just fine up until I changed the From Clause. Your thoughts appreciated.

USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_MAC_RAMS]    Script Date: 10/15/2013 10:17:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_MAC_RAMS]  
 
	(
		@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,
   @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
  (
    [data_source] varchar(12),--  what database is the data coming from  macola or rams?
	[RAMS_ID] VARCHAR (6) Null,
	[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] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ID_Title] [Varchar] (40),
	[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] (24) 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] int,   
	[prod_cat] Varchar (12), --left([prod_cat],2) int, --as ProdCatNo,
	[prod_desc] varchar (32),
    [CurrWeekBeginDate] int null,
    [PriorWeekBeginDate] int Null,
    [PriorWeekEndDate] Int Null,
    [CurrFromDate] Int Null,
    [PriorFromDate] Int Null,
    [PriorToDate] Int Null,
    [CurrFromDateYTD] Int Null,
    [PriorFromDateYTD] Int Null,
    [PriorToDateYTD] Int Null,
    [qty_return_to_stk] Int null
    )
   
--------------------------------------------------------
--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 DISTINCT
	    	    
	    'Rams',
		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,
		CUST_ID.ID_DESC as Terr_name,
		CUST_ID.ID_Title as ID_Title, 
		Rpt_TerritoryAdopted.Report_Sort, 
		SUBSTRING(CUST_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,
	     CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
		 @PriorWeekBeginDate AS PriorWeekBeginDate,
		 @PriorWeekEndDate AS PriorWeekEndDate,
		 @CurrFromDate as CurrFromDate,
		 @PriorFromDate AS PriorFromDate,
		 @PriorToDate AS PriorToDate,
		 @CurrFromDateYTD AS CurrFromDateYTD,
		 @PriorFromDateYTD AS PriorFromDateYTD,
		 @PriorToDateYTD AS PriorToDateYTD,
		 qty_return_to_stk = 0
		 
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
				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 CUST_ID  
				ON CM.CM_SELLZONE = CUST_ID.IDVALUE
				AND CUST_ID.ID_TITLE = N'Sale Zone'
		INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD	
				ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER	
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
				ON PD.PRD_PRODUCT = OD.OD_PRODUCT
		INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
				ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
				AND PR_ID.ID_TITLE = N'Product Brand Name'
        -----INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
		LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
						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 (CUST_ID.ID_TITLE = N'Sale Zone')
          
 --GROUP BY   PR_ID.ID_DESC,
            --OD.OD_PRODUCT,
            --CM.CM_SELLZONE,
            --CUST_ID.IDVALUE,
            --CUST_ID.ID_TITLE,
            --PD.PRD_R_BRANDNAME

END

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

---------------------------------------------------------------------------------------------
	---   MACOLA   SECTION
--------------------------------------------------------------------------------------------
	
 
/*If (@DB_Include = 0 or @DB_Include =2 ) -- macola data
-- if @DB_Include = 0  -- macola data
Begin
insert  into #weeklytemp

SELECT DISTINCT
     
      'Macola',
     ARCUSFIL_SQL.Terr, -- correlates with the RAMS ID in RAMS DB
--    Rpt_ReportingDates.CurrWeekEndDate AS CurrWeekEndDate,
--    Rpt_ReportingDates.CurrToDate AS CurrToDate,
--   Rpt_ReportingDates.CurrToDateYTD AS CurrToDateYTD,
      @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,
      '0',
      Rpt_OELINHST_Terr.Report_Sort,
      Rpt_OELINHST_Terr.Terr,
      Rpt_OELINHST_Terr.cus_no ,
      ARCUSFIL_SQL.cus_name,
      Rpt_Territory.Terr_id,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort AS Adopted_Sort,
      '0', -- ARCUSFIL_SQL.weeknumber AS WeekNumber,
      '0', -- ARCUSFIL_SQL.week1_enddate,
      '0', -- ARCUSFIL_SQL.ticket_number,
      Rpt_OELINHST_Terr.item_no,
      '0', -- ARCUSFIL_SQL.OD_RETURN_REASON_CODE,
      '0.00', -- ARCUSFIL_SQL.OD_FINAL_PRICE,
      Rpt_OELINHST_Terr.qty_to_ship,-- dup below needs to match [TOTAL_DELIV_QUANTITY] in table
      Rpt_OELINHST_Terr.sls_amt,-- Weekly_Sales_Amt
      '0.00',-- Weekly_Cost_Amt 
      ' ', -- Date_ID set to Null per Phil Lavene
      Rpt_OELINHST_Terr.qty_to_ship,
      Rpt_OELINHST_Terr.inv_dt, 
      IMITMIDX_SQL.prod_cat,
      IMCATFIL_SQL.prod_cat_desc,
/*    Rpt_ReportingDates.CurrWeekBeginDate AS CurrWeekBeginDate,
      Rpt_ReportingDates.PriorWeekBeginDate AS PriorWeekBeginDate,
      Rpt_ReportingDates.PriorWeekEndDate AS PriorWeekEndDate,
     Rpt_ReportingDates.CurrFromDate AS CurrFromDate,
     Rpt_ReportingDates.PriorFromDate AS PriorFromDate,
     Rpt_ReportingDates.PriorToDate AS PriorToDate,
     Rpt_ReportingDates.CurrFromDateYTD AS CurrFromDateYTD,
     Rpt_ReportingDates.PriorFromDateYTD AS PriorFromDateYTD,
     Rpt_ReportingDates.PriorToDateYTD AS PriorToDateYTD,*/
--  Replacing above with same as in RAMS Select
      @CurrWeekBeginDate AS CurrWeekBeginDate,
      @PriorWeekBeginDate AS PriorWeekBeginDate,
      @PriorWeekEndDate AS PriorWeekEndDate,
      @CurrFromDate as CurrFromDate,
      @PriorFromDate AS PriorFromDate,
      @PriorToDate AS PriorToDate,
      @CurrFromDateYTD AS CurrFromDateYTD,
      @PriorFromDateYTD AS PriorFromDateYTD,
      @PriorToDateYTD AS PriorToDateYTD,
      Rpt_OELINHST_Terr.qty_return_to_stk
     
 

FROM
    { oj (((DATA_04.dbo.Rpt_OELINHST_Terr Rpt_OELINHST_Terr LEFT OUTER JOIN DATA_04.dbo.Rpt_Territory Rpt_Territory ON
        Rpt_OELINHST_Terr.Terr = Rpt_Territory.Terr_Id)
     LEFT OUTER JOIN DATA_04.dbo.ARCUSFIL_SQL ARCUSFIL_SQL ON
        Rpt_OELINHST_Terr.cus_no = ARCUSFIL_SQL.cus_no)
	 INNER JOIN DATA_04.dbo.Rpt_ReportingDates Rpt_ReportingDates ON
        Rpt_OELINHST_Terr.inv_dt >= Rpt_ReportingDates.PriorFromDateYTD
            )
     LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        Rpt_OELINHST_Terr.item_no = IMITMIDX_SQL.item_no}
     INNER JOIN DATA_04.dbo.IMCATFIL_SQL IMCATFIL_SQL ON
             IMITMIDX_SQL.prod_cat = IMCATFIL_SQL.prod_cat
WHERE
     (Rpt_OELINHST_Terr.inv_dt between @CurrFromDateYTD and @CurrToDateYTD or
     Rpt_OELINHST_Terr.inv_dt between @PriorFromDateYTD and @PriorToDateYTD) -- Changed by RB was @PriorFromDate and @PriorToDate
END

*/
----------------------------------------------------
-- final select
----------------------------------------------------
select * from #weeklytemp

Open in new window

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.

virtuadeptCommented:
One or more columns on #weeklytemp is not big enough to store the data being inserted into it. This happens a lot when you are inserting into varchar columns and the temp table varchar has a lower maximum width than the source data. It only shows up when there is an actual row that has a string too long to fit on the destination.

We can probably solve this really quick if you post the before SQL and the after SQL (presuming the above is the after SQL) and we can just compare with a character compare program.
0
DcpKingCommented:
Another way is to copy the copy from line 155 to 240 into another window. Then, at what is currently line 206 (but which will be about 51 then) and which is a set of 3 blank lines, insert this:

into mytest

and then run the query. You'll end up with a new table mytest and you can compare the field sizes with #weeklytemp to discover which on in #weeklytemp is too small

hth

Mike
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I had to declare a bunch of stuff so I tried this approach ......what did I do wrong with this code...? My query before the words "Into Mytest" works.....ow do I see the widths of the fields in the SP. I tried selecting and right mouse clicking to look at properties but do not see how to view the character widths......I must be blind.....?

declare     @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 

set @Cus_no_Start = '1'
set @Cus_no_End = '1999'
set @DB_Include = 2
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,
   @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)



      SELECT DISTINCT
                
          'Rams',
            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,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_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,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
            
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        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 CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no

Into Mytest

Open in new window

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.

DcpKingCommented:
Put the "into mytest" just before the line starting the "FROM" part of your query. The syntax is something like

select fields into table from table
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Sorry not a coder yet.....I put it there but not sure on this statement.
"select fields into table from table"
I put the Into Mytest in front of the FROM clause below and I gives me an error there.


declare     @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 

set @Cus_no_Start = '1'
set @Cus_no_End = '1999'
set @DB_Include = 2
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,
   @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)



      SELECT DISTINCT
              
          'Rams',
            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,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_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,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
           
 Into Mytest
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        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 CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no

Open in new window

0
DcpKingCommented:
No problem. I only had a tablet last night: here's what your code should look like - in another SSMS window.  Just copy it out from where it is now to the other window, add in the "into mytest" line in the right place, and run it. You should get a new table (called "mytest") that you can check the structure of, and compare that with the structure of #weeklytemp in your code.

BTW, if you get an error anywhere, please tell us what the error is, as well as saying "it gives an errror" :)

Mike



      SELECT DISTINCT
          'Rams',
            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,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_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,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
           
 Into Mytest
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        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 CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no

Open in new window

0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I have to declare several variables in this so there is more.

Here is the Error I get.
Msg 8155, Level 16, State 1, Line 48
No column was specified for column 1 of 'Mytest'.
Msg 8155, Level 16, State 1, Line 48
No column was specified for column 30 of 'Mytest'.


declare     @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 

set @Cus_no_Start = '1'
set @Cus_no_End = '1999'
set @DB_Include = 2
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,
   @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)

      SELECT DISTINCT
          'Rams',
            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,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_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,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
           
 Into Mytest
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        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 CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no 

Open in new window

0
virtuadeptCommented:
When you use SELECT ... INTO you have to give a name to every element of the select, so that

'Rams',

line on column 1 needs to have a name like

'Rams' as Rams_ID,


That is what that error message is trying to tell you.
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:
All very helpful thank you. You were right I was not aware of the procedures with SELECT .... INTO. Now I know
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.