Case when not working as expected

174
September 12, 2017, at 11:32 AM

I have a query that seems to work except for this one last value that I need and I can't figure out where I'm going wrong.

I posted the full query below for reference, but specifically, this is the line that I need to fix:

CASE when a.LEGTYPE1 = 1 then FINALLYCALLEDPARTYNO
             else callingpartyno END AS Phone_Number  

So in my table a, cdrdb.session, I have fields legtype1, finallycalledpartyno and callingpartyno. All I need to do in this line is say "If legtype1 = 1, insert the value from finallycalledpartyno into phone_number, but if legtype1 = 2 then insert the value from callingpartyno into phone_number.

Currently, this is putting the originating call number into the phone_number field so it's basically switching the number called with the number calling.

I don't think it's necessary but here's the full query.

Insert into test.AMBrawDataDump(
            Inbound_Answered, 
            Missed, 
            Outbound, 
            Phone_number,)  
SELECT 
            , CASE WHEN a.LEGTYPE1 = 2 AND ANSWERED = 1 
              THEN 1 ELSE 0 END AS Inbound_Answered      -- Inbound answered 
calls
            , CASE WHEN a.LEGTYPE1 = 2 AND ANSWERED = 0
              THEN 1 ELSE 0 END AS Missed             -- Missed Calls
            , CASE WHEN a.LEGTYPE1 = 1
              THEN 1 ELSE 0 END AS Outbound               -- Outbound calls
            , a.HOLDTIMESECS -- Hold time in seconds
            ,CASE when a.LEGTYPE1 = 1 then FINALLYCALLEDPARTYNO
             else callingpartyno END AS Phone_Number      -- this is my problem
FROM cdrdb.session a
INNER JOIN cdrdb.callsummary b
 ON a.NOTABLECALLID = b.NOTABLECALLID
 WHERE b.ts BETWEEN curdate() - interval 30 day and curdate()

;

UPDATE:

To be clear, legtype1 will be 1 or 2. 1 is outbound, 2 is inbound. So in the query, i'm trying to say if legtype1 = 1, put a 1 in for outbound. If legtype1 = 2 and answered = 1, put 1 for inbound, and if legtype1 = 2 but answered = 0 then put 1 for missed. So, essentially, every row should only have a 1 in one of the three columns. If the call was outbound though, I want the number from finallycalledpartyno inserted into phone_number.

READ ALSO
LOOP IN PHP / MYSQL

LOOP IN PHP / MYSQL

I have a record of dates and logidI wonder if I could display the dates depending on the logid like 2017-09-01 to 2017-09-05 for logid = 015511439 then 2017-09-01 to 2017-09-05 for 9272276416

139
MySQL: Help to search each word in multiple columns as case insensitive

MySQL: Help to search each word in multiple columns as case insensitive

I have a table that contains: product, cost, comment

141
Why does MariaDB-10.2.8 UNHEX() return NULL?

Why does MariaDB-10.2.8 UNHEX() return NULL?

I have a SQL script that works on MySQL (up to current versions)I built a copy of MariaDB-10

258
REST API not working with MySQL

REST API not working with MySQL

I am creating a REST API using MySQL DB in visual studio 2015 in aspnet mvc 4

272