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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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!
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
,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]%')