Skip to content
Advertisement

I can’t get the results of the interrogation.(postgresql and php)

I want to search by output but I am doing wrong where it somehow doesn’t work. I am using PostgreSQL.

SELECT 
      CDR.xml_cdr_uuid,
        CDR.direction,
        CDR.caller_id_number,
        CDR.destination_number,
        CDR.start_stamp,
        CDR.end_stamp,
        CDR.duration,
        CDR.record_path,
        CDR.record_name,
CASE  
           WHEN (bridge_uuid is not null) and (answer_stamp is not null) and billsec >0  THEN 'answer'
             WHEN (missed_call = true and bridge_uuid is null) AND (json->'variables'->>'sip_invite_failure_status' IN('487','486','480') ) THEN 'not_call'
             WHEN (answer_stamp is not NULL) and (bridge_uuid is NULL) AND (sip_hangup_disposition !='send_refuse') THEN 'voicemail'
             ELSE 'ROTA_error' END
        as status
FROM v_xml_cdr as CDR
GROUP BY  xml_cdr_uuid
HAVING    ('status' = 'not_call')

enter image description here

Advertisement

Answer

Try this:

SELECT 
      CDR.xml_cdr_uuid,
        CDR.direction,
        CDR.caller_id_number,
        CDR.destination_number,
        CDR.start_stamp,
        CDR.end_stamp,
        CDR.duration,
        CDR.record_path,
        CDR.record_name,
CASE  
           WHEN (bridge_uuid is not null) and (answer_stamp is not null) and billsec >0  THEN 'answer'
             WHEN (missed_call = true and bridge_uuid is null) AND (json->'variables'->>'sip_invite_failure_status' IN('487','486','480') ) THEN 'not_call'
             WHEN (answer_stamp is not NULL) and (bridge_uuid is NULL) AND (sip_hangup_disposition !='send_refuse') THEN 'voicemail'
             ELSE 'ROTA_error' END
        as status
FROM v_xml_cdr as CDR
GROUP BY  xml_cdr_uuid
HAVING    (CASE  
           WHEN (bridge_uuid is not null) and (answer_stamp is not null) and billsec >0  THEN 'answer'
             WHEN (missed_call = true and bridge_uuid is null) AND (json->'variables'->>'sip_invite_failure_status' IN('487','486','480') ) THEN 'not_call'
             WHEN (answer_stamp is not NULL) and (bridge_uuid is NULL) AND (sip_hangup_disposition !='send_refuse') THEN 'voicemail'
             ELSE 'ROTA_error' END = 'not_call')

You trying to compare two strings, the string 'status' and string 'not call' in your HAVING clause. So, everytime this comparison will be false

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement