HOW DO I SOLVE SQL CONVERSION ERROR IN VIEWS-CodePudding

I have this small error in SQL View:

SELECT CASE
 WHEN CustomerID > 1 THEN CustomerPIN
 WHEN SupplierID > 1 THEN SupplierPIN
END AS FullNames

CustomerPIN and SupplierPIN are varchar fields in the table

Everything is fine when the result is null, empty or alphabets, but when I pick a valid numeric PIN, it returns a Conversion failed when converting the varchar value '123' to int

PIN can have string all literal text 'P002' or all numeric text '0023' or 'P_001', but all are varchar data types whichever the case. iT

What am I missing to complete this? I think I should somehow wrap the result but can't guess it!

I looked at this but can't seem to get it still... SQL Conversion Error in view

Thanks.

CodePudding user response：

I think @HoneyBadger is on the right track, though I couldn't reproduce the error. You should tell us how you're using this result downstream and try to pinpoint the exact line that gives the error.

Your code does work as far as what you showed us, I can generate some fake data in the format you described and the CASE works without error. I'll post it below, and maybe you can play with it a bit to see if you can reproduce your error in this portable example?

with cteData as ( --Set up sample data to test
    SELECT * FROM (VALUES 
        (CONVERT(INT, 123) --Force cast into the desired data types, replace the types with yours!
            , CONVERT(varchar(50), 'C123'), CONVERT(INT, 456), CONVERT(varchar(50), 'S456'))
        --Begin the "real" data to test
        , (100, '100', NULL, '400'), (120, '', NULL, NULL) 
        , (NULL, 'CNull', 457, 'S457'), (124, 'C124', NULL, 'SNULL') 
        , (NULL, '120', 400, '401') , (103, '133', NULL, NULL) 
        , (130, 'C130', 0, '') 
    ) tblSample(CustomerID, CustomerPIN, SupplierID, SupplierPIN)
), cteTestCase as(
    SELECT * , 
        CASE
            WHEN CustomerID > 1 THEN CustomerPIN
            WHEN SupplierID > 1 THEN SupplierPIN
        END AS FullNames
    FROM cteData
)
SELECT * 
FROM cteTestCase as T
--Play with a WHERE clause to try to reproduce your error and refine it!
--WHERE FullNames = '100'

CodePudding user response：

I got it to work this way, thanks to the answer by Robert Sheahan and others:

SELECT CASE
  WHEN CustomerID > 1 THEN Convert(VarChar(50), CustomerPIN)
  WHEN SupplierID > 1 THEN Convert(VarChar(50), SupplierPIN)
END AS FullNames

It looks like that conversion has to be forced, regardless that CustomerPIN and SupplierPIN are varchar datatypes.

Thanks