Func_odbc.conf: how to det a default value if row not found?

Hello,

I’m using Asterisk 13.13.1 with a ODBC/MySQL back-end.

I’d like to write a query like: “if given CallerID is found in MYTABLE, then return corresponding LocationCode if this LocationCode is set, else, if LocationCode is not set or no row found, then return a default value”

I’ve tried directly in isql console, variations of the bellow query:

SELECT COALESCE(DID.loccode, 12345) AS MYCODE FROM DID WHERE ‘+123456789’ BETWEEN DID.did_start AND DID.did_end
±--------------------------------------------------------------------------------+
| MYCODE |
±--------------------------------------------------------------------------------+
±--------------------------------------------------------------------------------+

Any tip ?

Best regards

I think that is an SQL question, not an Asterisk one.

It’s a long time since I worked with SQL, but I think you can achieve it, non-procedurally, with an outer join on a one row table (which can probably be a table expression). The ON clause will be funny.

Your basic problem is that no rows at all is not the same as a row with all null values.

Having a procedure that tests for no matches may well be more efficient. I’d always look at the query plan when doing anything strange.

Another thought is that you could do an aggregate sub-query as the left hand operand of the COALESCE.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.