lundi 4 novembre 2019

If-Then Statement In A SQL Query Insists On Trying To Convert To Wrong Type, Then Fails

I have a SQL query, linking table 1 to table 2 via an inner join, containing this part in the select part of the statement:

select
table1.field1,
table2.field1,
CASE (table2.field1)
     WHEN -2 THEN ''
     ELSE table2.field2 as table2Field2
END,
table3.field4
from...

I want to be able to return table2Field2 when it has a relevant value, ie: when the object represented in table2 is not null, so that table2.field1 does not have a value of -2. In this case, the value of table2Field2 should be blank instead of a meaningless value.

However, this returns 0 instead of the blank text. If I change this line:

 WHEN -2 THEN ''

to this:

 WHEN -2 THEN 'someText'

then it complains at me that it's trying to convert an int to a string, which I'm not. table2field1 is an int, but table2Field2 is a string, which is what we're actually returning here.

How do I state (even more specifically) in this query that I'm returning the string field as a string, and not something else as a string that isn't (a) a string, and (b) the thing I specified I'm returning please?

All suggestions welcome, many thanks in advance for any help :)

Aucun commentaire:

Enregistrer un commentaire