asked on

Trying to troubleshoot .....one line of SQL at a time.....

Trying to check one line of code at a time in the Query below. I am trying to see where the SELECT breaks down in dumping to the TEMP table called WeeklyTemp.

Can someone identify where I keep going wrong here. I either get the following errors.
String or binary data would be truncated.

USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_MAC_RAMS]    Script Date: 10/17/2013 10:05:46 ******/
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  


----------------------------------------------------------------------------------------------------
--  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] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustNo] [varchar] (30) 	COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	
	[TerrAdopted] [Varchar] (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] 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  = Terr
		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

ASKER CERTIFIED 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

That is what I was trying to do and it gives me the error of :

Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_MAC_RAMS, Line 53
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_MAC_RAMS, Line 145
Incorrect syntax near the keyword 'BEGIN'.

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

I am going through it again with the " -- " in each row.

If it comes back and none of the rows fail and it looks like it is alright but when run I get the
"String or binary data would be truncated. "
What do you suggest I try next...?

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

I could not get past the error code
Incorrect syntax near the keyword 'declare'
Incorrect syntax near the keyword 'BEGIN'

I thought there was a quicker way but I would have increased the size of the column but in running with it commented out still brought back the error.

Aneesh

your original code is already attached to this post, use that or else you can post your current code here . I wish I have remote access to your PC, that would be easier

RUA Volunteer2?

ASKER

Found it it was PRD_DESC1.....should have been 40 characters wide instead of 32. Thanks for the support.

RUA Volunteer2?

ASKER

Persistance and waiting for the query to finish. Thanks
One row at a time. I am glad there were not 100 rows. Whew

Aneesh

even if with 100 columns, you have to do the same approach, probably wont do it one at a time, instead will go for half at a time

PortletPaul

>>instead will go for half at a time
yep,
do top half, if that's ok,
it's in the bottom half, do half of that, etc.

if the top half is NOT ok, then do top quarter, if that's OK
it's in the next quarter, do half of that, etc

much faster, it's a boolean approach :)