Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lnt_sql
Starting Member
6 Posts |
 Posted - 2010-05-27 : 00:50:09
|
| Hi, I have already read two posts here with similar cases as mine, but neither of them had the exact case.The code has been working fine for me, for the last 1 year. Last week onwards, it has been failing with the error.The column involved is of varchar(30),sometimes having an 'R' at the end. If you see the code, you can see that I have removed the 'R', checked with ISNUMERIC() and then only converted. And I checked the dataset that is causing the error, and no records are there were an extra character comes up in that field. Please give some hints, as this issue has already caused lots of data loss, due to package failures.The problem occurs only for the PONumber field. If the convert is removed, it works fine. Also, if converted to decimal, it works fine. Please note that the data doesn't contain any decimal points.SELECT POI.PurchaseOrderItemID AS FourthPurchaseOrderItemID, CONVERT(bigint,POH.PONumber) AS PONumber, CONVERT(bigint,POI.LineNumber) AS LineNumber, PartNumber, POI.LineItemOrderedQuantity, POIFJ.FourthPurchaseOrderItemID AS MappedFourthPurchaseOrderItemID, POIFJ.JDEPurchaseOrderItemID, POIFJ.PurchaseOrderItemFourthJDEID AS FourthPurchaseOrderItemFourthJDEID, NULL AS ReturnedPO FROM PURCHASE_ORDER POH INNER JOIN PURCHASE_ORDER_ITEM POI ON POH.PurchaseOrderID = POI.PurchaseOrderID LEFT JOIN PURCHASE_ORDER_ITEM_FOURTH_JDE POIFJ ON POI.PurchaseOrderItemID = POIFJ.FourthPurchaseOrderItemID WHERE POIFJ.JDEPurchaseOrderItemID IS NULL AND ISNUMERIC(POH.PONumber) = 1 UNION ALL SELECT POI.PurchaseOrderItemID AS FourthPurchaseOrderItemID, CONVERT(bigint,LEFT(POH.PONumber, LEN(POH.PONumber)-1)) AS PONumber, CONVERT(bigint,POI.LineNumber) AS LineNumber, PartNumber, POI.LineItemOrderedQuantity, POIFJ.FourthPurchaseOrderItemID AS MappedFourthPurchaseOrderItemID, POIFJ.JDEPurchaseOrderItemID, POIFJ.PurchaseOrderItemFourthJDEID AS FourthPurchaseOrderItemFourthJDEID, POH.PONumber AS ReturnedPO FROM PURCHASE_ORDER POH INNER JOIN PURCHASE_ORDER_ITEM POI ON POH.PurchaseOrderID = POI.PurchaseOrderID LEFT JOIN PURCHASE_ORDER_ITEM_FOURTH_JDE POIFJ ON POI.PurchaseOrderItemID = POIFJ.FourthPurchaseOrderItemID WHERE POIFJ.JDEPurchaseOrderItemID IS NULL AND ISNUMERIC(POH.PONumber) = 0 AND ISNUMERIC(LEFT(POH.PONumber, LEN(POH.PONumber)-1)) = 1 ORDER BY PONumber, LineNumber, PartNumber, LineItemOrderedQuantity |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-27 : 02:04:03
|
To find the bad data use this because isnumeric isn't reliable:select * from PURCHASE_ORDER where ltrim(rtrim(PONumber)) like '%[^0-9]%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Error converting varchar to bigint