asked
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
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
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.
