HOW DO I SOLVE SQL CONVERSION ERROR IN VIEWS

Question

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.

If the column is a `varchar` then you should be comparing with a varchar `CustomerID > '1'`. `>` will obviously be a string comparision. — Stu, May 31 '22 at 12:13
Note that `'1' > '0'`... I suppose you want `WHEN CustomerID <> '' THEN CustomerPIN`, to avoid NULL and empty. — jarlh, May 31 '22 at 12:31
However, I'd add a constraint to prohibit empty values - such should be NULL. — jarlh, May 31 '22 at 12:33
@Stu and @jarlh, OP said CustomerPIN and SupplierPIN are varchar, they said nothing about CustomerID or SupplierID. I suspect the error comes when querying the view with `WHERE FullNames = 123` instead of `WHERE FullNames = '123'`. Although the conditions on the ID's are unusual — HoneyBadger, May 31 '22 at 13:01
try_convert, also its a good practice to add a default condition as ELSE. in addition, its a varchar, so you need to parse it to get part which would be converted to int, also to cover null you can use coalesce(COLUMN, '') != '' , in this case null or empty would be ignored https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver16 — Power Mouse, May 31 '22 at 13:26
Sorry, CustomerID and SupplierID are int data type. CustomerPIN and SupplierPIN are varchar. Some I am not mixing up the datatypes in any way. My result should be varchar, but I am getting this datatype conversion error.... — Hannington Mambo, May 31 '22 at 15:02
What is the error you are getting? Cut and paste it verbatim. — Andy Lester, May 31 '22 at 17:02

score 1 · Accepted Answer · answered May 31 '22 at 13:30

1

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'

answered May 31 '22 at 13:30

Robert Sheahan

2,100
1
10
12

"I couldn't reproduce the error": Try `WHERE FullNames = 123` (as I suggested in my other comment) – HoneyBadger May 31 '22 at 15:04
Yes @HoneyBadger, I did and it gives the error in the opposite direction - it gives "Can't convert 'C123' to INT", not "Can't convert '123' to INT" which is what the OP described. So maybe the OP mis-transcribed the error, or maybe it's something else, but I wasn't able to reproduce the exact error cited. I agree with your line of reasoning (and mentioned it and upvoted your comment), hopefully he'll get back to us and explain. – Robert Sheahan May 31 '22 at 15:21
I wish I could add any further info: The table simply has CustomerID int, SupplierID int, CustomerPIN varchar and SupplierPIN varchar. CustomerID is always 1 where SupplierID is > 1, and SupplierID is always 1 when CustomerID > 1. Everytime CustomerID > 1, I want to pick the CustomerPIN, and everytime the SupplierID > 1, I want to pick the SupplierPIN. That simple (so it seems). But I am getting the error exactly as described above. PIN values are random, they can be alphabets, numerals or alphanumeric, but stored as varchar – Hannington Mambo May 31 '22 at 15:28
@HanningtonMambo, if you run just my sample, does it give an error? If not, can you modify either the data or the WHERE clause to reproduce the exact error? – Robert Sheahan May 31 '22 at 15:53
@HanningtonMambo you can easily give more information: the rest of the view could be helpful, and especially how you use it. Sample data would go a long way too. – HoneyBadger May 31 '22 at 16:25

score 1 · Answer 2 · answered May 31 '22 at 16:46

1

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

answered May 31 '22 at 16:46

Hannington Mambo

998
2
13
28

Congratulations! And thanks for posting back what worked, I'm sure it will help future SO users – Robert Sheahan May 31 '22 at 17:32

HOW DO I SOLVE SQL CONVERSION ERROR IN VIEWS

2 Answers2