MySQL View Logic with Case

Question

I've been tasked with editing one of our MySQL views to include rows it was missing. The problem is the previous view query was using if() statements which we're not capturing all the tables rows for some reason. I am by no means a SQL master and cannot figure this out...

Original Code Sample:

CREATE VIEW `b2b_data` AS SELECT 
`asteriskcdrdb`.`cdr`.`uniqueid` AS `callid`,
`asteriskcdrdb`.`cdr`.`calldate` AS `ENTER`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`did`,`asteriskcdrdb`.`cdr`.`src`) AS `caller_id`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),'outbound','inbound') AS `direction`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`src`,substr(`asteriskcdrdb`.`cdr`.`dstchannel`,5,3)) AS `AGENT`,
sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`,
`asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`dstchannel`,`asteriskcdrdb`.`cdr`.`did`) AS `ROUTE`,
`asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE (((`asteriskcdrdb`.`cdr`.`dst` = '601') AND (`asteriskcdrdb`.`cdr`.`dcontext` = 'ext-group')) OR ((`asteriskcdrdb`.`cdr`.`dst` = '601') AND (`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal-xfer')) OR ((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal') AND (`asteriskcdrdb`.`cdr`.`src` in ('143','146','317','318','320','225','214'))))

I read online that IF...ELSEIF only works in stored procedures and functions. So I scrapped that idea. I then thought maybe I could use CASE to make the query return what I needed. Needless to say this did not work and clearly was wrong... but it does display the logic I'm trying to use.

Modded Code Sample:

CREATE VIEW `b2b_data` AS SELECT
`asteriskcdrdb`.`cdr`.`uniqueid` AS `callid`,
`asteriskcdrdb`.`cdr`.`calldate` AS `ENTER`,

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`did` AS `caller_id`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN 'outbound' AS `direction`;
    WHEN 'from-internal-xfer' THEN 'inbound' AS `direction`;
    WHEN 'ext-local' THEN 'inbound' AS `direction`;
    WHEN 'ext-group' THEN 'inbound' AS `direction`;
    WHEN 'from-did-direct' THEN 'inbound' AS `direction`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`src` AS `AGENT`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
    WHEN 'ext-group' THEN substr(`asteriskcdrdb`.`cdr`.`dstchannel`;5;3)) AS `AGENT`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'from-internal-xfer' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'ext-local' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'ext-group' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'from-did-direct' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`dstchannel` AS `ROUTE`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`dstchannel` AS `ROUTE`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`src` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` = '601';
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
END CASE;

score 0 · Accepted Answer · answered May 12 '16 at 20:11

0

You're putting the AS clauses in the wrong place. The entire CASE expression results in a single value, which you assign the alias to. And you don't use ; between the WHEN clauses or after the case, since that ends the entire query. There's no separator between WHEN clauses, and the entire expression is separated from other SELECT columns with ,.

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`did`
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`src
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`src`
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`src`
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`src`
END CASE AS `caller_id`,

and similar for tall the other CASE expressions.

answered May 12 '16 at 20:11

Barmar

741,623
53
500
612

I will give this a shot tomorrow and upvote your answer if it works correctly. I have a feeling I wrote it incorrectly all three times... I was using both commas and semi-colons to seperate the WHEN clauses. Thought I had to because of the MySQL documentation example [here](https://dev.mysql.com/doc/refman/5.7/en/case.html). – Mark Newton May 13 '16 at 04:05
That documentation is for the `CASE` **statement**, which is used in stored procedures; the semicolons are for the end of the statements that it controls. `CASE` **expressions** are described [here](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html) – Barmar May 13 '16 at 15:45
See also [Case Expression vs Case Statement](http://stackoverflow.com/questions/12436859/case-expression-vs-case-statement) – Barmar May 13 '16 at 15:47
Thank you Barmar! You were correct about everything. I managed to get it to work, although now I've run into other issues but they are logic issues that I can work through. – Mark Newton May 13 '16 at 18:14

MySQL View Logic with Case

1 Answers1