String or Binary data would be truncated

Question

I am running into this error when I try to load the hist.prc from the inner join at the bottom of the script. What is it about the rownumber partition that makes it a string and is there a way to cast it as money?

SELECT
 p.ID
,p.[NAME]
,p.BUSINESS_SEGMENT__C
,P.ACCOUNT__C
,act.ACCOUNTNUMBER
,act.NAME
,NULL
,ref.EM_ITEM_NUM
,ref.SELL_DSCR
,p.COMP_NDC_NUM__C
,prd.NDC__C
,REF.DM_RNKNG_CD 
,ptyp.PRC_TYP_DSCR 
,hist.PRC 
,cur.PRC
,cur.PRC - HIST.PRC
,NULL
,ISNULL(((CUR.PRC) - (HIST.PRC)) /NULLIF((HIST.PRC),0),0)
,cur.PRC_EFF_DT 
,p.ISSUE_CLOSE_DATE__C 
,p.CREATEDDATE 
,p.PRICING_TEAM_MEMBER__C 
,usr.[name] 
,usr.[USERNAME] 
,p.COPY_EMAIL_TO__C
FROM
G2_SFDC.dbo.GXPILS__C p
LEFT  JOIN G2_SFDC.dbo.ACCOUNT act
ON p.ACCOUNT__C = act.ID
LEFT JOIN   G2_SFDC.dbo.PRODUCT2 prd
ON prd.ID = p.MCKESSON_OS_ITEM__C
LEFT JOIN REFERENCE.dbo.T_IW_EM_ITEM ref
ON prd.NDC__C = ref.NDC_NUM
JOIN  G2_SFDC.dbo.[USER]usr
ON p.CREATEDBYID = USR.id
LEFT JOIN GEPRS_PRICE.dbo.T_PRC cur
ON REF.EM_ITEM_NUM = cur.EM_ITEM_NUM
AND cur.PRC_CURR_CD = 'C'
JOIN(SELECT EM_ITEM_NUM,
 PRC,
 PRC_TYP_ID,
 ROW_NUMBER() OVER (PARTITION BY EM_ITEM_NUM ORDER BY PRC_END_DT DESC) RN
 FROM   GEPRS_PRICE.dbo.T_PRC 
 WHERE  PRC_CURR_CD = 'H') hist
ON ref.EM_ITEM_NUM = hist.EM_ITEM_NUMv
AND hist.PRC_TYP_ID =cur.PRC_TYP_ID
AND hist.RN = 1v
LEFT JOIN GEPRS_PRICE.dbo.T_PRC_TYP ptyp
ON ptyp.PRC_TYP_ID = cur.PRC_TYP_ID

Answer 1

0

This problem has nothing to do with the row numbering.

If hist.prc is money then the string or binary data woudl be truncated is simply caused the fact that the corresponding column in your temp table is not money, but is something else instead. Money is mapping directly to decimal(19, 4), and therefore, if your temp table's column is decimal of a smaller precision and/or scale then this error will inevitably occur. You should either try to get by by turning the ansi_warnings off or better still by making sure that the column in question of your temp table is not defined as int which is essentially a decimal(10, 0) or a decimal of a smaller than money precision and/or scale.

Oleg

answered Feb 16 '11 at 01:02 PM

Oleg
15.4k ● 1 ● 4 ● 24

(comments are locked)

Answer 2

0

Does this query execute successfully with the Row_Number() and And hist.RN=1v lines commented out?

ROW_NUMBER() returns an INT.

What do you mean by when you `load' the hist.prc?

you can use CONVERT(decimal(12,4), prc) in your inner select if you need the data type to change. I fear you may find that one or more rows in the T_PRC table may not convert successfully though. I would check that column for valid values.

answered Feb 16 '11 at 12:41 PM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

I am trying to load this intire scrript into a temp table for use in a sproc. the prc is declared as money and failiing when I try to load it. I have commented out all of the fields and the magic error seems to be occurring on this hist.prc field

Feb 16 '11 at 12:53 PM siera_gld

If I run the query as a straight query it runs fine - but I've got more data to load (sales from another table) so I want to do it in stages

Feb 16 '11 at 12:59 PM siera_gld

(comments are locked)

String or Binary data would be truncated

Follow this question

Related Questions