SQL data type mismatch for integers and dates

Question

I am using MSquery and have the following sql query:

SELECT `Sheet1$`.colA, `Sheet1$`.colB, 
Switch(
colA = '1.0', 'A1',
colA = '2.0', 'A2',
colA = '3.0', 'C1',
colA = '4.0', 'C2'

)AS colG


FROM (

SELECT `Sheet1$`.colA, `Sheet1$`.colB
FROM `Sheet1$` `Sheet1$`
UNION ALL
SELECT `Sheet2$`.colC, `Sheet2$`.colD
FROM `Sheet2$` `Sheet2$`
) t;

I am getting an error message: Data type mismatch in criteria expression. I think it has something to do with the fact that I used single quotes around the numbers in the switch statement.

I have two tables and am appending them. An extra column is then added and a different message should be added depending on the value in column A for each row. This query works when I have column A as text, however when I try it for numbers or dates I get the error message. What do I need to use instead of single quotes for numbers and dates? Is it ok to use single quotes for the message that will be displayed?

Thanks very much.

colA is numeric. For testing I origionally used text and it worked as I wanted but when I change it to numbers I get the error message. As well as numbers I would also like to know how I would use dates. — kit

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-08-22T09:54:21

Change colA to a numeric type and then use the following query:

SELECT `Sheet1$`.colA,
        `Sheet1$`.colB, 
       SWITCH (
           colA = 1.0, 'A1',
           colA = 2.0, 'A2',
           colA = 3.0, 'C1',
           colA = 4.0, 'C2'
       ) AS colG
FROM
(
SELECT `Sheet1$`.colA,
       `Sheet1$`.colB
FROM `Sheet1$` `Sheet1$`
UNION ALL
SELECT `Sheet2$`.colC,
       `Sheet2$`.colD
FROM `Sheet2$` `Sheet2$`
) t;

SQL data type mismatch for integers and dates

1 Answers