SQLTeam.com | Weblogs | Forums

All of a sudden getting an error on old query

So all of a sudden one of my query is not working - no change to the query and DB for the past year. Thanks in advance.

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '
' to data type int.

Completion time: 2021-05-26T10:58:53.0967882-05:00

Query:

/****** Script for SelectTopNRows command from SSMS ******/
;With Orders as
(
Select left(soh.[CreatedDate],4) as Year
,substring(soh.[CreatedDate], 10, 2) as Month
,soi.[ItemCategory]
,count([Order No]) as EdiOrders
,soh.[SalesDocType]
,count(soi.[Item]) as Lines
,soh.[CreatedBy]
From [dbo].[T-SOHeader] soh left join [dbo].[T-SOItem] soi
on soh.[SalesDoc] = soi.[SalesDoc]
Left join [View_EDIorders] edi -- EDI sanitized view that has no duplicates. Has to be rewritten each month!
on cast(soh.[SalesDoc] as float) = [Order No]
Where soh.[CreatedDate] like '202104%'
Group by left(soh.[CreatedDate],4)
,substring(soh.[CreatedDate], 10, 2)
,soi.[ItemCategory]
,soh.[SalesDocType]
,soh.[CreatedBy]
)

, GM as
(Select
left([CalYearMonth],2) as Month
,right([CalYearMonth],4) as Year
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,sum(cast([Sell] as float)) as NetSales
,sum(cast([GM] as float)) as GrossMargin
,[termination date]
,[Hire Date]
FROM [Business Analytics].[dbo].[T-GrossMargins_AM_CSR] gm left join [dbo].[HR_SAP_ID]
on [CreatedBy] = [SAPuserID]
Left join [dbo].[HREmployee]
on [FileNumber] = [File Number]
Where [OrderType] not in ('ZRE','ZCR','ZKR', 'ZDR') --exclude "Returns","Credit Memo Request","Consignment Returns","Debit Memo Requests"
and right([Home Department Code],2)=05
and [CalYearMonth] like '04/2021'
Group by
left([CalYearMonth],2)
,right([CalYearMonth],4)
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,[Termination Date]
,[Hire Date]
)

SELECT gm.Month
,gm.Year
,[OrderType]
,[OrderTypeDescr]
,[ItemCat]
,[ItemCatDescr]
,gm.[CreatedBy]
,[PayrollName]
,[Job Title Description]
,[Home Department Code]
,gm.NetSales
,gm.GrossMargin
,orders.Lines
,orders.[EdiOrders]
,[termination date]
,[Hire Date]
FROM GM left join Orders
on GM.[OrderType] = orders.SalesDocType
and GM.[ItemCat] = orders.ItemCategory
and GM.month = orders.Month
and GM.Year = Orders.Year
and GM.CreatedBy = Orders.[CreatedBy]
Order by [Year],[Month] asc

yes code might not changed at all in years, but data always comes in. Since you provided absolutely zero table definition pretty much impossible to help you.

please provide each table DDL along with column data types.

from the looks of it a lot of your table columns have the incorrect data types so now it is coming to bite you. so one of those columns looks like it is defined as varchar but you are putting int values in it. but now something is trying to interact with one of those not well defined columns with a blank value where it is expecting an "integer" value.

1 Like

hi

that could mean several things

one possiblity is DATA has changed

how do you debug

select
col1,
col2,
col3

first run col1
select col1

all ok then add col2

then try col3

select col1,col2,col3

now error

means dig into col3

whats the data type .. length .. and the data thats coming in is matching
if col3 is varchar(3) and data is 'aaaaaaaaaaaaaa' which more than 3 then error ..

thats the idea

good luck

pain.. i.. t.. a... .. this kind of stuff