Toad World® Forums

Querying connected excel worksheet data


#1

Hello,

Please i am trying to write Case statements to get data from a connected worksheet but i keep getting this error [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression. When i do Select * from table or query specific fields it works fine but i am unable to use Case or IF. I can do this with my other connect SQL DB’s and oracle DB’s.

Please any assistance or help is greatly appreciated.

Thank you


#2

If memory serves…the Excel ODBC driver doesn’t support CASE or IF. For whatever crazy reason in Jet connections it is IIF. If I remember correctly it should look something like this…

SELECT Name, IIF(Gender = ‘M’, ‘Male’,‘Female’) FROM Individual


#3

Excel does not support the CASE statement like many SQL databases do. I think you’ll have to use IIF in your select statement. I did a Google search and one good example I found was:

   SELECT
IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
TABLE_NAME