Issue "Error converting data type varchar to numeric."
-
Hello,
I am not sure why but I have a difficult time selecting fields that have a value 'X' in one of the fields.
I have a field wf.SMP_FLAG where I should have only two values ' ' (blank) and 'X' (letter X). I am not sure what kind of data type it is in the system, but I am constantly getting an error "Error converting data type varchar to numeric."
This field is defined in CTE when in one of the tables I type 'X' AS 'SMP_FLAG' and in the next CTE I am selecting all the fields that are of 'X' type. I am getting an error:
Error converting data type varchar to numeric.
When I am selecting WHERE wf.SMP_FLAG = 'X' (in the next SELECT statement)
I am getting an error " Error converting data type varchar to numeric. "
I tried to change it to such queries
-- WHERE wf.SMP_FLAG = 'X' -- Error converting data type varchar to numeric.
-- WHERE CAST(wf.SMP_FLAG as char) = 'X' -- Error converting data type varchar to numeric.
-- WHERE CAST(wf.SMP_FLAG AS numeric) = 'X' -- Error converting data type varchar to numeric.
-- WHERE CONVERT(VARCHAR(50), wf.SMP_FLAG) = 'X' -- Error converting data type varchar to numeric.
When I
TRY_CAST(wf.SMP_FLAG as float) = 'X' I am not getting errors but everything is blank as if there were no 'X' (but they were in the previous CTE).
Still no luck.
Also tried with CASE statement and no luck.
WHERE (CASE WHEN wf.SMP_FLAG = '' THEN 0
WHEN wf.SMP_FLAG = 'X' THEN 1
ELSE 0
END) = 1
Maybe someone may have an idea.
-
post both the table definition AND the FULL SQL you are executing.
if you are querying views put the table definition of all involved tables within those views (and subviews if applicable)
-
@frederico I am sorry I don't have documentation for the table.
If you want that's the code.
,WF_SMP AS (
--FILTERED TO 4 WEEKS ONLY(!)
SELECT DEAGY --AS 'Agency Code'
,DECHN --AS 'Chain Code'
,DESTR --AS 'Store Number'
,DEORD# --AS 'Order Number'
,DEWHS# AS 'Whse Code'
,DATEADD( DAY , 7 - DATEPART(WEEKDAY, CAST(DEWEDT AS CHAR(12))), CAST (CAST(DEWEDT AS CHAR(12)) AS DATE )) AS 'WEEK_I8YYMD'
,DATEADD( DAY , 371 - DATEPART(WEEKDAY, CAST(DEWEDT AS CHAR(12))), CAST (CAST(DEWEDT AS CHAR(12)) AS DATE )) AS 'LY_WEEK_I8YYMD'
--, dateadd(year, -1, DEWEDT)
-- LY_WEEK_I8YYMD -- line 835(?) ,
, 'X' AS SMP_FLAG -- line 836 (?)
,WHALPH AS 'Alpha Code Designation'
,SGLDC,
SGLDPT
,CMNAM AS 'Chain Name'
,CMDNAM AS 'Display Name'
,CMSCHN AS 'Sales Master Name'
, CMCHNL AS 'Dft Store Channel'
, CMSTYP AS 'Dft Store Type'
, DEWEDT AS 'Week Ending Date'
, WHDNAM --AS 'DC_NAME'
,SUM(DEQTY) AS 'Total Quantity'
,SUM(DEQTY*DERPRC) AS 'DERPRC_EXT'
,SUM(DEQTY*DERSP) AS 'DERSP_EXT'
,SUM(DEQTY*DEACST) AS 'DEACST_EXT'
FROM [REPIT].SMPDTA.SMPDTLM sm
LEFT OUTER JOIN [REPIT].LEVYDTA.WHSWHSM wh
ON sm.DEWHS#=wh.WHWHS#
LEFT OUTER JOIN [REPIT].LEVYDTA.LDSSGEN ld
ON sm.DEAGY=ld.SGAGY and sm.DECHN=ld.SGCHN and sm.DESTR=ld.SGSTR
LEFT OUTER JOIN [REPIT].LEVYDTA.LDSCHNM lds
ON sm.DEAGY=lds.CMAGY and sm.DECHN=lds.CMCHN
WHERE DEIO='O'
AND DESDTE between @Last5WeekDATE and @LWDATE
AND DECHN NOT IN ( 'C0', 'C3' , 'C4' , 'C8')
--AND DEORD#='5027487' AND DESTR='2230'
GROUP BY DEAGY ,DECHN,DESTR ,DEORD# ,DEWHS# , DEWEDT -- line 834 (?) -- LY_WEEK_I8YYMD -- line 835(?) ,
,WHALPH ,SGLDC, SGLDPT,CMNAM ,CMDNAM ,CMSCHN , CMCHNL , CMSTYP , DEWEDT, WHDNAM
-- ORDER BY DEAGY ,DECHN,DESTR ,DEORD# ,DEWHS#
)
--, WF_AR_MC AS (
-- 871 - 921
SELECT chn.CMSCHN ,AR_LOAD, S1WHS#, AR_AGY, S1CHAN, AR_STORE,AR_LOAD, S1WHS# , AR_ORD, S1SCDR, S1SDAT, S1UPSP, SHPNAM
,AR_WEEK_I8YYMD ,S1SCDA, REC_SHIPPER_NAME, ACT_SHIPPER_NAME, REC_SHIPPER_SCAC, ACT_SHIPPER_SCAC,
REC_STYPE, ACT_STYPE, S1SSTR ,UPSS_MODE,DSDC_MODE ,MULT_USE_FLAG
,CMSCHN
--,SUM(S1FRAC) AS 'S1FRAC', SUM(WEIGHT) AS 'WEIGHT', SUM(S1CTN#) AS 'S1CTN#', SUM(S1BKRT) AS 'S1BKRT'
, TRY_CAST((S1FRAC) as float) AS 'S1FRAC' -- I need SUM
, TRY_CAST((WEIGHT) as float) AS 'WEIGHT' -- I need SUM
, TRY_CAST((S1CTN#) as float) AS 'S1CTN#' -- I need SUM
, TRY_CAST((S1BKRT) as float) AS 'S1BKRT' -- I need SUM
--, SMP_FLAG
--, TRY_CAST(wf.SMP_FLAG AS varchar)
--,CONVERT(VARCHAR(50),SMP_FLAG)
FROM WF_AR ar
LEFT OUTER JOIN WF_SMP wf
ON ar.AR_AGY = wf.DEAGY and ar.S1CHAN=wf.DECHN and ar.AR_STORE=wf.DESTR and ar.AR_ORD=wf.DEORD#
LEFT OUTER JOIN WF_CHNM chn
ON ar.AR_AGY =chn.CMAGY and ar.S1CHAN=chn.CMCHN
--WHERE TRY_CAST(wf.SMP_FLAG as varchar) = 'X'
-- WHERE wf.SMP_FLAG = 'X' -- Error converting data type varchar to numeric.
-- WHERE CAST(wf.SMP_FLAG as char) = 'X' -- Error converting data type varchar to numeric.
-- WHERE CAST(wf.SMP_FLAG AS numeric) = 'X' -- Error converting data type varchar to numeric.
-- WHERE CONVERT(VARCHAR(50), wf.SMP_FLAG) = 'X' -- Error converting data type varchar to numeric.
-- where AR_ORD='5146492'
GROUP BY chn.CMSCHN , AR_LOAD, S1WHS#, AR_AGY, S1CHAN, AR_STORE, AR_LOAD, S1WHS#, AR_ORD, S1SCDR, S1SDAT, S1UPSP, SHPNAM
,AR_WEEK_I8YYMD, S1SCDA, REC_SHIPPER_NAME, ACT_SHIPPER_NAME, REC_SHIPPER_SCAC, ACT_SHIPPER_SCAC, REC_STYPE, ACT_STYPE, S1SSTR
,UPSS_MODE ,DSDC_MODE, MULT_USE_FLAG
--,CMSCHN
,S1FRAC, WEIGHT, S1CTN#, S1BKRT
--916 cannot run
--,SMP_FLAG
--) -
I am using SSMS. Wf is an alias to a CTE called WF_SMP
-
Wf is an alias to a CTE called WF_SMP
WF_SMP is defined: 'X' AS SMP_FLAG
I am basically trying to get all the rows from CTE called 'Wf'.
-
This should give you the data type of the column:
SELECT TOP (1) wf.SMP_FLAG
INTO #temp
FROM WF_SMP wf
EXEC tempdb.sys.sp_help #temp
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
It keeps loading for a long time. I don't see any result coming up from this.
-
It is varchar if I understand correctly
-
That's some really bad code to troubleshoot, Jeremy. Someone not familiar with the tables would have a heluva time trying to figure what which columns come from which tables and if someone adds a duplicate column to one of the tables, your code will crash an burn with an error saying that it couldn't figure out which table the column was supposed to come from. Don't say it won't or can't happen. I've seen it happen way too many times.
You have aliases on your tables... you need to add the correct aliases to your columns.
Also, this little snippet of your code will be a heart breaker if they ever change DESDTE to contain times on the dates...
AND DESDTE between @Last5WeekDATE and @LWDATE
BETWEEN equates the code above to the following...
AND DESDTE >= @Last5WeekDATE and DESDTE <= @LWDATE
That means that if DESDTE has or could someday have times as a part of the date, you be missing most of that last date. Even if this code doesn't and can't have that issue, you're forming a really bad habit by using BETWEEN.
You need to explicitly write the code as follows to make sure that it's bullet proof even if the DESDTE column doesn't currently have a time included in the dates...
AND DESDTE >= @Last5WeekDATE and DESDTE < DATEADD(dd,1,@LWDATE)
... and that's one of the very few supposed "Best Practices" that actually IS a "Best Practice"... in fact, it should be labeled as an "Only Way to do it Right" so that people don't think that they can violate it.
You also form display aliases (like the following) in your CTE but then you don't use them in the outer query.
,CMSCHN AS 'Sales Master Name'
That means that you MUST use the display name in the outer query.
Save your self some heart ache... save the display stuff for the final query that's going to display stuff. 😉
Personally, I'd also get out of the habit of using quoted strings as column aliases. It makes it difficult to read when you have actual quoted strings. Use brackets on your [display names] for the column aliases instead.
And, finally, your code has the following snippet in the CTE.
, 'X' AS SMP_FLAG -
That means that it'll NEVER be a blank as you say it could be. It's just not ever going to happen. Since that's a constant, that should also only be in the outer query.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@jeff how can I then correct it?
I need to filter WHERE SMP_FLAG ='x'.
It is constant only for WF_SMP CTE. But later when I add other tables, I start getting more rows and I need to filter down only to those that are in WF_SMP.
P/s INNER JOIN creates the same error.
WF_SMP AS (
--FILTERED TO 4 WEEKS ONLY(!)
SELECT DEAGY --AS 'Agency Code'
,DECHN --AS 'Chain Code'
,DESTR --AS 'Store Number'
,DEORD# --AS 'Order Number'
,DEWHS# AS 'Whse Code'
,DATEADD( DAY , 7 - DATEPART(WEEKDAY, CAST(DEWEDT AS CHAR(12))), CAST (CAST(DEWEDT AS CHAR(12)) AS DATE )) AS 'WEEK_I8YYMD'
,DATEADD( DAY , 371 - DATEPART(WEEKDAY, CAST(DEWEDT AS CHAR(12))), CAST (CAST(DEWEDT AS CHAR(12)) AS DATE )) AS 'LY_WEEK_I8YYMD'
--, dateadd(year, -1, DEWEDT)
-- LY_WEEK_I8YYMD -- line 835(?) ,
-- line 836 (?)
, 'X' AS 'SMP_FLAG'
,WHALPH AS 'Alpha Code Designation'
,SGLDC,
SGLDPT
,CMNAM AS 'Chain Name'
,CMDNAM AS 'Display Name'
,CMSCHN AS 'Sales Master Name'
, CMCHNL AS 'Dft Store Channel'
, CMSTYP AS 'Dft Store Type'
, DEWEDT AS 'Week Ending Date'
, WHDNAM --AS 'DC_NAME'
,SUM(DEQTY) AS 'Total Quantity'
,SUM(DEQTY*DERPRC) AS 'DERPRC_EXT'
,SUM(DEQTY*DERSP) AS 'DERSP_EXT'
,SUM(DEQTY*DEACST) AS 'DEACST_EXT'
FROM [REPIT].SMPDTA.SMPDTLM sm
LEFT OUTER JOIN [REPIT].LEVYDTA.WHSWHSM wh
ON sm.DEWHS#=wh.WHWHS#
LEFT OUTER JOIN [REPIT].LEVYDTA.LDSSGEN ld
ON sm.DEAGY=ld.SGAGY and sm.DECHN=ld.SGCHN and sm.DESTR=ld.SGSTR
LEFT OUTER JOIN [REPIT].LEVYDTA.LDSCHNM lds
ON sm.DEAGY=lds.CMAGY and sm.DECHN=lds.CMCHN
WHERE DEIO='O'
AND DESDTE between @Last5WeekDATE and @LWDATE
AND DECHN NOT IN ( 'C0', 'C3' , 'C4' , 'C8')
--AND DEORD#='5027487' AND DESTR='2230'
GROUP BY DEAGY ,DECHN,DESTR ,DEORD# ,DEWHS# , DEWEDT -- line 834 (?) -- LY_WEEK_I8YYMD -- line 835(?) ,
,WHALPH ,SGLDC, SGLDPT,CMNAM ,CMDNAM ,CMSCHN , CMCHNL , CMSTYP , DEWEDT, WHDNAM
-- ORDER BY DEAGY ,DECHN,DESTR ,DEORD# ,DEWHS#
) --,
-- WF_AR_MC AS (
-- 871 - 921
SELECT CMSCHN ,AR_LOAD, S1WHS#, AR_AGY, S1CHAN, AR_STORE, AR_ORD, S1SCDR, S1SDAT, S1UPSP, SHPNAM
,AR_WEEK_I8YYMD ,S1SCDA, REC_SHIPPER_NAME, ACT_SHIPPER_NAME, REC_SHIPPER_SCAC, ACT_SHIPPER_SCAC,
REC_STYPE, ACT_STYPE, S1SSTR ,UPSS_MODE,DSDC_MODE ,MULT_USE_FLAG
, TRY_CAST((S1FRAC) as float) AS 'S1FRAC' -- I need SUM
, TRY_CAST((WEIGHT) as float) AS 'WEIGHT' -- I need SUM
, TRY_CAST((S1CTN#) as float) AS 'S1CTN#' -- I need SUM
, TRY_CAST((S1BKRT) as float) AS 'S1BKRT' -- I need SUM
--, SMP_FLAG
--, TRY_CAST(wf.SMP_FLAG AS varchar)
--,CONVERT(VARCHAR(50),SMP_FLAG)
FROM WF_AR ar
LEFT OUTER JOIN WF_SMP wf
ON ar.AR_AGY = wf.DEAGY and ar.S1CHAN=wf.DECHN and ar.AR_STORE=wf.DESTR and ar.AR_ORD=wf.DEORD#
LEFT OUTER JOIN WF_CHNM chn
ON ar.AR_AGY =chn.CMAGY and ar.S1CHAN=chn.CMCHN
WHERE try_cast(SMP_FLAG as int) ='x'
GROUP BY CMSCHN ,AR_LOAD, S1WHS#, AR_AGY, S1CHAN, AR_STORE, AR_ORD, S1SCDR, S1SDAT, S1UPSP, SHPNAM
,AR_WEEK_I8YYMD ,S1SCDA, REC_SHIPPER_NAME, ACT_SHIPPER_NAME, REC_SHIPPER_SCAC, ACT_SHIPPER_SCAC,
REC_STYPE, ACT_STYPE, S1SSTR ,UPSS_MODE,DSDC_MODE ,MULT_USE_FLAG , CMSCHN
,S1FRAC, WEIGHT, S1CTN#, S1BKRT
--) - JeremyU wrote:
@Jeff how can I then correct it?
I need to filter WHERE SMP_FLAG ='x'.
Then use THAT instead of what you have in the code you posted!!! 😉 Here's what you have in the code you posted above... see anything there that might be causing the issue?
WHERE try_cast(SMP_FLAG as int) ='x'
That will qualify as FALSE until the end of time because your SMP_FLAG creation in the CTE is and always will be something other than an INT and so the TRY_CAST will ALWAYS return a NULL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff I used that as well. It doesn't work. Let me know if you have other ideas. Thank you though.
-
The problem does NOT appear to have anything to do with the creation of the SMP_FLAG or the assignment of the letter 'X' to it or the use of it in any of the code that you've posted.
--===== Notice that this works correct on the simple assignment
-- (although I hate the quoted alias assignment)
SELECT 'X' AS 'SMP_FLAG'
,*
FROM sys.objects
;
--===== This also works fine.
WITH WF_SMP AS
(
SELECT 'X' AS 'SMP_FLAG'
,*
FROM sys.objects
)
SELECT *
FROM WF_SMP
;
--===== And this also works fine...
WITH WF_SMP AS
(
SELECT 'X' AS 'SMP_FLAG'
,*
FROM sys.objects
)
SELECT *
FROM WF_SMP
WHERE SMP_FLAG = 'X'
;What may be an issue is that you may have a problem in how you defined that column in the code in a different CTE prior to the partial code that you've posted. You may have defined it as numeric or int or something other than what 'X' can be converted to. Go look for that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have a different kind of issue now. I identified that the problem was in a join ar.AR_STORE=wf.DESTR.
I need to create a join on this field ar.AR_STORE=wf.DESTR
I don't know but I cannot create a join on a trimmed (leading '0' removed from the beginning only).
substring(AR_STORE, patindex('%[^0]%',AR_STORE), 10) AS 'AR_STORE', DESTR,
I need to make a join
FROM WF_AR ar
LEFT OUTER JOIN WF_SMP wf
ON ar.AR_AGY = wf.DEAGY and ar.S1CHAN=wf.DECHN and substring(AR_STORE, patindex('%[^0]%',AR_STORE), 10)=wf.DESTR
and ar.AR_ORD=wf.DEORD#
However, if I do it like that I am getting an error "Error converting data type varchar to numeric.". If I remove this join on those 2 fields, everything works again. Do you know how it can be fixed?
-
Can you CAST(ar.AR_STORE AS INT) to drop the preceding zeros and allow the join?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply