CODEWITHSUNDEEP
sql
Ratha
Ratha

Reputation: 9692

conversion failed string to int using case when then in sql

I use case when in my sql query as follows,

SELECT 
         CASE 
            WHEN Grade = 'Diesel' THEN 4
            WHEN Grade = 'Petrol' THEN 5

       END as Grade
      ,sales

  FROM testTableA as tta

  INNER JOIN testTableB ttb on ttb.productid =  tta.Grade

When I run above I get "Conversion failed when converting the varchar value 'Diesel' to data type int." error. I tried Cast(tta.Grade, int) too but no luck.. What Im doing wrong here?

Edit

testTableA : column grade is varchar.
testTableB: column productid is int

Upvotes: 1

Views: 898

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You would have to return string value because Grade has string value & your ELSE part returning them also & case expression will return only one type :

SELECT (CASE WHEN Grade = 'Diesel' THEN '4'
             WHEN Grade = 'Petrol' THEN '5'
             ELSE Grade 
        END) as Grade,
 . . .

EDIT : Join also cause the conversation issue , so you have to do some conversation 

SELECT ttb.Grade, sales
FROM testTableA as tta CROSS APPLY
     ( VALUES (CASE WHEN tta.Grade = 'Diesel' THEN 4
                    WHEN tta.Grade = 'Petrol' THEN 5
               END)
     ) tt(grade) INNER JOIN
    testTableB ttb
    ON ttb.productid = tt.Grade;

Upvotes: 1

AlexYes
AlexYes

Reputation: 4208

seems you'd like to join on the output of the case statement, then you should be this:

SELECT 
CASE 
    WHEN Grade = 'Diesel' THEN 4
    WHEN Grade = 'Petrol' THEN 5
 END as Grade
,sales
FROM testTableA as tta
INNER JOIN testTableB ttb 
ON ttb.productid =  CASE 
    WHEN tta.Grade = 'Diesel' THEN 4
    WHEN tta.Grade = 'Petrol' THEN 5
END

or wrap tta into a subquery to avoid duplicating the same logic

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

Judging from the comments, I think you may be looking for this:

SELECT 
         CASE 
            WHEN Grade = 'Diesel' THEN 4
            WHEN Grade = 'Petrol' THEN 5

       END as Grade
      ,sales

  FROM testTableA as tta

  INNER JOIN testTableB ttb on ttb.productid =  CASE 
                                                    WHEN tta.Grade = 'Diesel' THEN 4
                                                    WHEN tta.Grade = 'Petrol' THEN 5
                                                 END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Given your code sample, there are two possibilities:

  • Grade is a number, and the error is coming from the case comparing the value to 'Diesel'.
  • ProductId is a number, and the error is coming from the on condition when Grade has a value of 'Diesel'.

Of course, there are other possibilities, such as the table references really being views.

There is not enough information to determine which of these is actually happening. However, you know what the data types of your columns are, so you can immediately determine the problem.

If I had to guess, it would be the join condition. Joining something called productId to something called grade is suspicious.

Upvotes: 1

Related Questions