lburnsinmagnolia
asked on
Compare a numeric value to a varchar w/o getting an error
I have the following query
select si.OrderID, SO.ORDERDATE, QtyOrdered
,si.ProductID
, si.LineCost, si.ProductDesc, si.BrandID, SI.CaseSellingPrice
fROM SAMSDW.storis.OrderItem SI
JOIN SAMSDW.storis.Orders SO ON
SO.OrderID = SI.OrderID
WHERE SI.OrderID = '300119'
and si.ProductID not in (select itemid from INVENTORY where itemid = CASE WHEN ISNUMERIC(SI.PRODUCTID) = 1 THEN CONVERT(NUMERIC(18,0), SI.PRODUCTID) ELSE 0 END)
ITEMID is a numeric(18,0) in INVENTORY and PRODUCTID is varchar(50) in SAMSDW. Sometimes, the SAMSDW table will be a number, most of time it will not. I merely want to test the 2 values by converting/casting the PRODUCTID if it's numeric. But I crash no matter what.
Is there any way to get around the error?
Larry
select si.OrderID, SO.ORDERDATE, QtyOrdered
,si.ProductID
, si.LineCost, si.ProductDesc, si.BrandID, SI.CaseSellingPrice
fROM SAMSDW.storis.OrderItem SI
JOIN SAMSDW.storis.Orders SO ON
SO.OrderID = SI.OrderID
WHERE SI.OrderID = '300119'
and si.ProductID not in (select itemid from INVENTORY where itemid = CASE WHEN ISNUMERIC(SI.PRODUCTID) = 1 THEN CONVERT(NUMERIC(18,0), SI.PRODUCTID) ELSE 0 END)
ITEMID is a numeric(18,0) in INVENTORY and PRODUCTID is varchar(50) in SAMSDW. Sometimes, the SAMSDW table will be a number, most of time it will not. I merely want to test the 2 values by converting/casting the PRODUCTID if it's numeric. But I crash no matter what.
Is there any way to get around the error?
Larry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Starting at SQL Server 2012 there is TRY_CAST() and TRY_CONVERT() which do not error but return NULL if the conversion can't be performed.
e.g.
TRY_CONVERT(numeric(18, 0), SI.PRODUCTID)
e.g.
TRY_CONVERT(numeric(18, 0), SI.PRODUCTID)
ASKER
Worked like a charm!
,si.ProductID
, si.LineCost, si.ProductDesc, si.BrandID, SI.CaseSellingPrice
fROM SAMSDW.storis.OrderItem SI
JOIN SAMSDW.storis.Orders SO ON
SO.OrderID = SI.OrderID
WHERE SI.OrderID = '300119'
and si.ProductID not in (select itemid from INVENTORY where itemid not like '%[^0-9]%')