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

avatar image

siera_gld
1k 82 88 93

(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

avatar image

Oleg
16.9k 3 7 28

(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

avatar image

Fatherjack ♦♦
43.7k 79 97 117

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.

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:

x35
x12
x4

asked: Feb 16, 2011 at 12:12 PM

Seen: 2086 times

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

Copyright 2016 Redgate Software. Privacy Policy