Error in SQL statement: ParseException: missing ')' at 'case'(line 21, pos 0)

Question

I'm trying to adapt someone else's query, but can't get the original to work.

I get the following SQL error message regarding the following query:

Error in SQL statement: ParseException: missing ')' at 'case'(line 21, pos 0)

select 
    call_record_id,
    user_id,
    agentDrivenSale,
    calling_phone_number_clean,
    matched_which
from 
    (select 
         call_record_id,
         user_id,
         agentDrivenSale,
         calling_phone_number_clean,
         matched_which,
         rank() over (partition by user_id order by row_number asc) as user_rank,
         rank() over (partition by call_record_id order by row_number asc) as call_rank
     from 
         (select 
              case 
                  when call_record_id_1 is not null 
                      then call_record_id_1 
                      else call_record_id_2 
              end as call_record_id
              case 
                  when calling_phone_number_clean is not null 
                      then calling_phone_number_clean 
                      else CALLING_PHONE_NUMBER_CLEAN_2 
              end as CALLING_PHONE_NUMBER_CLEAN
              case 
                  when calling_phone_number_clean is not null 
                      then matched_1 
                      else matched_2 
              end as matched_which,
              'TRUE' as agentDrivenSale,
              user_id,
              row_number() over (order by first_invoca_call_time asc, activated_at asc) as row_number
          from
              (select 
                   invc.complete_call_id as call_record_id_1,
                   invc2.complete_call_id as call_record_id_2,
                   cases.case_number,
                   'TRUE' as agentDrivenSale,
                   subs.user_id,
                   get_json_object(subs.offer, "$['category_key']"),
                   substr((subs.activated_at), 1, 19)::timestamp as activated_at,
                   invc.CALLING_PHONE_NUMBER_CLEAN,
                   invc2.CALLING_PHONE_NUMBER_CLEAN as CALLING_PHONE_NUMBER_CLEAN_2,
                   invc.start_time_network_timezone,
                   invc2.start_time_network_timezone as start_time_network_timezone_2,
                   case 
                       when invc.start_time_network_timezone is null 
                           then invc2.start_time_network_timezone
                       when invc2.start_time_network_timezone is null 
                           then invc.start_time_network_timezone
                       when invc.start_time_network_timezone <= invc2.start_time_network_timezone  
                           then invc.start_time_network_timezone
                       else invc2.start_time_network_timezone 
                   end as first_invoca_call_time
               from 
                   prod_salesforce2_public.V_CUSTOMER_CASES_SF_REP cases
               inner join 
                    MART_PLATFORM.V_BILLING_SUBSCRIPTION subs on subs.user_id = cases.user_id
               left join 
                   marketing_idga.v_invoca_calls invc on REPLACE(REPLACE(REPLACE(REPLACE(cases.phone_number, '(', ''), ' ', ''), '-', ''), ')', '') = invc.CALLING_PHONE_NUMBER_CLEAN
                AND invc.CALLER_CALL_NUM = 1
               left join
                   PROD_SALESFORCE2_PUBLIC.CUSTOMER_CALLS calls ON get_json_object(calls.customer_calls, "$['Case__r.CaseNumber']")  = cases.case_number
               left join 
                   marketing_idga.v_invoca_calls invc2 on REPLACE(REPLACE(REPLACE(REPLACE(get_json_object(calls.customer_calls, "$.Customer_Phone__c"), '(', ''), ' ', ''), '-', ''), ')', '') = invc2.CALLING_PHONE_NUMBER_CLEAN
                           AND invc2.CALLER_CALL_NUM = 1
               left join 
                   marketing_idga.impact_actions actions on actions.order_id = subs.user_id
               where 
                    call_type = 'Phone Enrollment'
                    and sub_type in ('Success')
                    and substr((subs.activated_at), 1, 19)::timestamp >= '2022-2-1'
                    and substr((subs.activated_at), 1, 19)::timestamp < '2022-12-1'
                    and actions.order_id is null
                    --and get_json_object(subs.offer, "$['category_key']") = 'idgsuite_d2c'
                   ) q
               where  
                   (call_record_id_1 is not null or call_record_id_2 is not null)) data
    ) output 
where 
    user_rank = 1 and call_rank = 1

I'm unclear why it would require a parenthesis. When I add one, it then says it's missing a ')' on the same line but pos 6. Any thoughts? Thanks.

Answer 1

You are missing several commas within one of the sub-queries.

Change this:

select 
case when call_record_id_1 is not null then call_record_id_1 else call_record_id_2 end as call_record_id
case when calling_phone_number_clean is not null then calling_phone_number_clean else CALLING_PHONE_NUMBER_CLEAN_2 end as CALLING_PHONE_NUMBER_CLEAN
case when calling_phone_number_clean is not null then matched_1 else matched_2 end as matched_which
,'TRUE' as agentDrivenSale

To This:

select 
case when call_record_id_1 is not null then call_record_id_1 else call_record_id_2 end as call_record_id
,case when calling_phone_number_clean is not null then calling_phone_number_clean else CALLING_PHONE_NUMBER_CLEAN_2 end as CALLING_PHONE_NUMBER_CLEAN
,case when calling_phone_number_clean is not null then matched_1 else matched_2 end as matched_which
,'TRUE' as agentDrivenSale

Without context, it's a rather large query to review for other possible issues such as a potential for ambiguous column names. Maybe it will run after that part is fixed.