x

String or Binary data would be truncated

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
more ▼

asked Feb 16, 2011 at 12:12 PM in Default

siera_gld gravatar image

siera_gld
1k 78 84 85

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Feb 16, 2011 at 01:02 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Feb 16, 2011 at 12:41 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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, 2011 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, 2011 at 12:59 PM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x29
x21
x11

asked: Feb 16, 2011 at 12:12 PM

Seen: 1684 times

Last Updated: Feb 16, 2011 at 12:33 PM