Search code examples
sqlexcelodbccaseclr

ODBC Excel SQL Condition

I need to build a SQL query to read an Excel file, through ODBC with the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).

That query needs a conditional statement in the SELECT that expresses "if the column A from the page TA has the value x, then R1, else R2", R1 and R2 being values of the same type (0 and 1 or 'ABC' and 'DEF').

With CASE it would normally give

SELECT CASE A WHEN x THEN R1 ELSE R2 END AS RA FROM TA;

or

SELECT CASE WHEN A = x THEN R1 ELSE R2 END AS RA FROM TA;

This creates an error saying there's a missing operator.

Looking around I've read that Access (because no one gives a darn about Excel) does not have the CASE statement.

I've then tried the IIF statement, 

SELECT IIF (A = x, R1, R2) AS RA FROM TA;

which doesn't work, with an error telling tales about either missing operators or missing ODBC fields depending on how I expressed the condition (I use a vanilla CRecordset object, it fails on the Open call, and I don't do DoFieldExchange nowhere),

then I tried the SWITCH statement

SELECT SWITCH (A = x, R1, true, R2) AS RA FROM TA;

which failed too ("Too few arguments. 1 expected."), then I tried the CHOOSE statement

SELECT CHOOSE (1*(A = x), R1, R2) AS RA FROM TA;

which, surprisingly, doesn't work, with the same results.

I also tried all these queries with RA = ... instead of ... AS RA, to no avail.

How do I properly express a condition in a SQL query aimed at an Excel file through ODBC?

Edit: I know presuming ignorance is the norm in there, so let me be clear, [A] IS a column in TA, R1 and R2 just express the desired values, in my case those are literal values (72 and 70 respectively). My queries are also far larger, requesting multiple columns from joins with wheres and an order by, and all my queries worked well until now, with simple CRecordset objects that I'd call GetFieldValue from. Any query I added a conditional statement to started to fail. I've extensively debugged my work and the queries are correctly formatted as far as I can tell (no missing parentheses or such). I also have no Actual Knowledge that these statements are unsupported; a correction on my syntax that would work is welcome.

I CANNOT simply get TA.A then handle its value in code and I CANNOT use an API or a different driver to access the data.

Edit2: @Parfait:

CDatabase *here_db = new CDatabase () ;
here_db->OpenEx ("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\ExcelFile.xls") ;
CRecordset *here_rs = new CRecordset (here_db) ;
here_rs->Open (CRecordset::snapshot, "SELECT COUNT(*) FROM [TableA$]", CRecordset::readOnly) ;
//This works, btw.
int rowCount ;
CDBVariant v ;
here_rs->GetFieldValue ((short) 0, v, SQL_C_SLONG) ;
rowCount = v.m_lVal ;
here_rs->Close () ;
here_rs->Open (CRecordset::snapshot, CString (
    "SELECT"
    " CASE A"
    "   WHEN 'test' THEN 72"
    "   ELSE 70"
    " END AS RA"
    " FROM [TableA$]"
    ""), CRecordset::readOnly) ;
short i ;
int colCount ;
colCount = here_rs->GetODBCFieldCount () ;
CString h ("") ;
while (!here_rs->IsEOF ())
{
    for (i = 0; i < c; i++)
    {
        here_rs->GetFieldValue (i, h) ;
    }
    here_rs->MoveNext () ;
}
here_rs->Close () ;
delete here_rs ;
here_db->Close () ;
delete here_db ;
return TRUE ;

Edit3: This is CPP/Cli.

The Jet and Ace data providers work with OLEDB objects, I don't know that you can use them with CDatabase/CRecordset.

I went back to this after the week-end, rebuild my query, checked all the table names, file paths, column names etc, tried an IFF, and it worked. With the Microsoft Excel Driver and all. I'm assuming I made a syntax error somewhere but I can't for the life of me figure what it was. Anyways here is the query that ended working:

if (!here_rs->Open (CRecordset::snapshot,
    "SELECT"
    "   `A`,"
    "   IIF(`A` = x, 72, 70) AS RA"
    " FROM `TableA$`"
    "", CRecordset::readOnly))
{
    __debugbreak () ;
}

Since Parfait's answer is correct, I'll mark it as such. I'll still welcome an explanation as to what happened, if anyone has it.

Solution

  • Querying Excel workbooks in Windows environments would involve the JET/ACE SQL engine (Windows .dll files) which is the very same engine used in MS Access. Since this SQL dialect does not support ANSI's CASE statement, the best equivalent would be the IIF function. From your statement:

    [A] IS a column in TA, R1 and R2 just express the desired values

    Then, your IIF expression must treat the test and desired values as quoted literals if using alphanumerics. Otherwise the engine assumes they are fields in table. Do note: you can use actual columns in IIF. Also, use a table alias for TA designation.

    SELECT IIF(TA.A = 'test', 'R1', 'R2') AS RA 
FROM [TableA$] AS TA

    And for number values as used in attempted code, do not quote them. In both expressions you are implicitly assigning a data type (string and integer respectively) while explicitly assigning values to new calculated column, RA:

    SELECT IIF(TA.A = 'test', 72, 70) AS RA 
FROM [TableA$] AS TA