Link to home
Start Free TrialLog in
Avatar of lburnsinmagnolia
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
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 not like '%[^0-9]%')
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)
Avatar of lburnsinmagnolia
lburnsinmagnolia

ASKER

Worked like a charm!