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

[oliv2831]

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

[david551]

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.

[david551]

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