Hello,

I'm wondering if there are IF/IIF/CASE functions or REPLACE functions for QODBC?  The reasons for the respective functions are below:

• I would like to build a SELECT statement where if the value is one thing, do a, if it's another, do b.  This is normally done as an Iif statement (in Access) or a CASE statement (in SQL).  For instance:

SELECT Name, Address1, IF(Gender = 'F', 'Pink', 'Blue') AS ClothingColor FROM Customers
SELECT CASE Gender WHEN 'F' THEN 'Pink' WHEN 'M' THEN 'Blue' END FROM CUSTOMERS

• I would like to replace a character in a string with another one.  For instance, if I have a placeholder character in a string, such as "~", I may want to replace it with a blank, and then TRIM it:

Assume that Codeword = "COMMIKKMENK"

SELECT Replace(CodeWord, 'K', 'T') FROM Codewords

Result would be "COMMITTMENT".

Are either of these two functionalities available with the QODBC driver?  If not, are they planned?  Thanks!

The use of

CASE sql_expression
     WHEN sql_expression_value THEN return_value
     ELSE return_value

or

CASE 
     WHEN sql_Boolean_expression THEN return_value
     ELSE return_value

is currently not supported by QODBC, but it's on the wish list!

All the QODBC Functions can be found by clicking here.

The REPLACE function can be done in MS Access using VBA functions like this:

    DoCmd.RunSQL _
        "INSERT INTO Local_ItemInventory ( Part, Description, QuantityOnHand ) " & _
        "SELECT Name, replace(PurchaseDesc,chr(10),chr(13) & chr(10)), QuantityOnHand " & _
        "FROM ItemInventory " & _
        "WHERE IsActive=Yes"

and QODBC does support the REPLACE function directly too:

{fn replace(sql_string_to_search, sql_string_to_find, sql_string_to_replace_with)}

For example:

SELECT Name, {fn REPLACE("Name", 'i', 'xxxxxx')} AS "Replaced" FROM Customer