Presto new driver failing on long queries with parameters

Getting a generic error (error executing query) for any long queries using parameters after updating to the new Presto driver. With the old driver these work as they are. Pasted an example of a query that now fails below. In some cases, with some changes in spacing/syntax, the error can be isolated to a particular line, but usually the fix is still unclear (eg: it will fail on the 85th when statement in a case statement). Any tips would be much appreciated – we want to upgrade to the new driver as it fixes some other bugs, but keep having to revert to the deprecated driver given these broken queries.

with invoice_spend as
    (select
    enterprise_raw.suppliers.id,
    enterprise_raw.suppliers.name,
    enterprise_raw.suppliers.display_name,
    enterprise_raw.currencies.name as local_currency,
    enterprise_raw.currencies.code as invoice_alpha_three_code,
    sum(enterprise_raw.invoice_headers.total_with_taxes_amount) as total_spend,
    sum(enterprise_raw.invoice_headers.total_with_taxes_amount_usd) as total_spend_usd,
    count(enterprise_raw.invoice_headers.id) as total_invoices,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'actionemail' THEN 1 ELSE 0 END) as count_action_email,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'csn' THEN 1 ELSE 0 END) as count_csn,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'cxml' THEN 1 ELSE 0 END) as count_cxml,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'sftp' THEN 1 ELSE 0 END) as count_sftp,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'api' THEN 1 ELSE 0 END) as count_api,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'easy_form' THEN 1 ELSE 0 END) as count_easy_form,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'email' THEN 1 ELSE 0 END) as count_email,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'enterprise' THEN 1 ELSE 0 END) as count_enterprise,
    SUM(CASE WHEN enterprise_raw.invoice_headers.channel = 'invoicesmash' THEN 1 ELSE 0 END) as count_invoicesmash                          
    
    from enterprise_raw.invoice_headers
    inner join enterprise_raw.suppliers on
    cast(enterprise_raw.invoice_headers.supplier_id as varchar)= cast(enterprise_raw.suppliers.id as varchar)
    and cast(enterprise_raw.suppliers.instance as varchar) = cast(enterprise_raw.invoice_headers.instance as varchar)
    inner join enterprise_raw.currencies on
    cast(enterprise_raw.invoice_headers.currency_id as varchar)=cast(enterprise_raw.currencies.id as varchar)
    and cast(enterprise_raw.currencies.instance as varchar) = cast(enterprise_raw.invoice_headers.instance as varchar)
    where enterprise_raw.invoice_headers.instance= {{hashed_instance}}
    and enterprise_raw.invoice_headers.status = 'approved'
    and date_trunc('month', enterprise_raw.invoice_headers.created_at) BETWEEN date_trunc('month', date_add('month', -12, now())) AND date_trunc('month', now())
    group by
    enterprise_raw.suppliers.id,
    enterprise_raw.suppliers.name,
    enterprise_raw.suppliers.display_name,
    enterprise_raw.currencies.name,
    enterprise_raw.currencies.code
    order by total_spend desc
    ),

po_spend as
    (select
    enterprise_raw.suppliers.id,
    enterprise_raw.suppliers.name,
    enterprise_raw.suppliers.display_name,
    enterprise_raw.currencies.name as local_currency,
    enterprise_raw.currencies.code as po_alpha_three_code,
    sum(enterprise_raw.order_headers.total) as total_spend,
    sum(enterprise_raw.order_headers.total_usd) as total_spend_usd,
    count(enterprise_raw.order_headers.id) as total_orders
    from enterprise_raw.order_headers
    inner join enterprise_raw.suppliers on
    cast(enterprise_raw.order_headers.supplier_id as varchar)=cast(enterprise_raw.suppliers.id as varchar)
    and enterprise_raw.suppliers.instance = enterprise_raw.order_headers.instance
    inner join enterprise_raw.currencies on
    cast(enterprise_raw.order_headers.currency_id as varchar)=cast(enterprise_raw.currencies.id as varchar)
    and cast(enterprise_raw.currencies.instance as varchar) = cast(enterprise_raw.order_headers.instance as varchar)
    where enterprise_raw.order_headers.instance={{hashed_instance}} and
    enterprise_raw.order_headers.status in ('soft_closed','closed','issued')
    and date_trunc('month', enterprise_raw.order_headers.created_at) BETWEEN date_trunc('month', date_add('month', -12, now()))
       AND date_trunc('month', now())
    group by
    enterprise_raw.suppliers.id,
    enterprise_raw.suppliers.name,
    enterprise_raw.suppliers.display_name,
    enterprise_raw.currencies.name,
    enterprise_raw.currencies.code
    order by total_spend desc
    ),

suppliers_one as
    (select 
    enterprise_raw.suppliers.id,
    coalesce(enterprise_raw.normalized_supplier_insights.supplier_master_id, smc.master_id) as supplier_master_id,
    enterprise_raw.suppliers.name as supplier_name,
    enterprise_raw.suppliers.display_name as supplier_display_name,
    enterprise_raw.suppliers.tax_id,
    enterprise_raw.suppliers.coupa_connect_status,
    enterprise_raw.contacts.email,
    enterprise_raw.contacts.name_fullname,
    CONCAT(mobile_number.country_code, mobile_number.area_code, mobile_number.number, mobile_number.extension) AS mobile_number,
    CONCAT(work_number.country_code, work_number.area_code, work_number.number, work_number.extension) AS work_number,
    enterprise_raw.addresses.street1,
    enterprise_raw.addresses.street2,
    enterprise_raw.addresses.street3,
    enterprise_raw.addresses.street4,
    enterprise_raw.addresses.city,
    enterprise_raw.addresses.state,
    enterprise_raw.addresses.postal_code,
    enterprise_raw.addresses.country_id,
    enterprise_raw.countries.name as country_name,
    enterprise_raw.countries.alpha_three_code,
    enterprise_raw.currencies.name as currency_name,
    (CASE 
    WHEN (enterprise_raw.payment_terms.discount_rate IS NOT NULL and enterprise_raw.payment_terms.active=1) THEN 'yes'
    WHEN UPPER(enterprise_raw.payment_terms.code) like '%DISC%' and enterprise_raw.payment_terms.active=1 THEN 'yes'
    WHEN UPPER(enterprise_raw.payment_terms.code) like '%CD%' and enterprise_raw.payment_terms.active=1 THEN 'yes'
    WHEN UPPER(enterprise_raw.payment_terms.code) like '%SKONTO%' and enterprise_raw.payment_terms.active=1 THEN 'yes'
    WHEN UPPER(enterprise_raw.payment_terms.description) like '%DISC%' and enterprise_raw.payment_terms.active=1 THEN 'yes'
    WHEN UPPER(enterprise_raw.payment_terms.description) like '%SKONTO%' and enterprise_raw.payment_terms.active=1 THEN 'yes'
    ELSE 'no' END) as supplier_may_offer_discount,
    (CASE 
    WHEN days_for_net_payment IS NOT NULL THEN days_for_net_payment
    when UPPER(enterprise_raw.payment_terms.code) like '%DUE%ON RECEIPT%' THEN 0
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%14%' THEN 14
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%15%' THEN 15
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%30%' THEN 30
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%31%' THEN 31
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%35%' THEN 35
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%45%' THEN 45
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%60%' THEN 60
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%90%' THEN 90
    when UPPER(enterprise_raw.payment_terms.code) like '%NET%120%' THEN 120
    when UPPER(enterprise_raw.payment_terms.code) like '%10%NET%' THEN 10
    when UPPER(enterprise_raw.payment_terms.code) like '%14%NET%' THEN 14
    when UPPER(enterprise_raw.payment_terms.code) like '%15%NET%' THEN 15
    when UPPER(enterprise_raw.payment_terms.code) like '%30%NET%' THEN 30
    when UPPER(enterprise_raw.payment_terms.code) like '%31%NET%' THEN 31
    when UPPER(enterprise_raw.payment_terms.code) like '%35%NET%' THEN 35
    when UPPER(enterprise_raw.payment_terms.code) like '%45%NET%' THEN 45
    when UPPER(enterprise_raw.payment_terms.code) like '%60%NET%' THEN 60
    when UPPER(enterprise_raw.payment_terms.code) like '%90%NET%' THEN 90
    when UPPER(enterprise_raw.payment_terms.code) like '%120%NET%' THEN 120
    
    when UPPER(enterprise_raw.payment_terms.code) like '%NET CASH%' THEN 0
    when UPPER(enterprise_raw.payment_terms.code) like '%PAYABLE IMMEDIATELY%' THEN 0
    when UPPER(enterprise_raw.payment_terms.code) like '%PAYMENT WITH ORDER%' THEN 0
    when UPPER(enterprise_raw.payment_terms.code) like '%+%1 DAYS%' THEN 1
    when UPPER(enterprise_raw.payment_terms.code) like '%+%5 DAYS%' THEN 5
    when UPPER(enterprise_raw.payment_terms.code) like '%+%7 DAYS%' THEN 7
    when UPPER(enterprise_raw.payment_terms.code) like '%+%8 DAYS%' THEN 8
    when UPPER(enterprise_raw.payment_terms.code) like '%+%14 DAYS%' THEN 14
    when UPPER(enterprise_raw.payment_terms.code) like '%+%15 DAYS%' THEN 15
    when UPPER(enterprise_raw.payment_terms.code) like '%MOIS LE 15%' THEN 15
    when UPPER(enterprise_raw.payment_terms.code) like '%+%18 DAYS%' THEN 18
    when UPPER(enterprise_raw.payment_terms.code) like '%+%20 DAYS%' THEN 20
    when UPPER(enterprise_raw.payment_terms.code) like '%+%21 DAYS%' THEN 21
    when UPPER(enterprise_raw.payment_terms.code) like '%+%25 DAYS%' THEN 25
    when UPPER(enterprise_raw.payment_terms.code) like '%+%27 DAYS%' THEN 27
    when UPPER(enterprise_raw.payment_terms.code) like '%+%28 DAYS%' THEN 28
    when UPPER(enterprise_raw.payment_terms.code) like '%30 DAYS%' THEN 30
    when UPPER(enterprise_raw.payment_terms.code) like '%END OF%MONTH%' THEN 30
    when UPPER(enterprise_raw.payment_terms.code) like '%+%1 MONTH%' THEN 30
    when UPPER(enterprise_raw.payment_terms.code) like '%35 DAYS%' THEN 35
    when UPPER(enterprise_raw.payment_terms.code) like '%+%37 DAYS%' THEN 37
    when UPPER(enterprise_raw.payment_terms.code) like '%+%42 DAYS%' THEN 42
    when UPPER(enterprise_raw.payment_terms.code) like '%+%43 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.code) like '%+%45 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.code) like '%,%45 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.code) like '%+%56 DAYS%' THEN 56
    when UPPER(enterprise_raw.payment_terms.code) like '%60 DAYS%' THEN 60
    when UPPER(enterprise_raw.payment_terms.code) like '%+%2 MONTH%' THEN 60
    when UPPER(enterprise_raw.payment_terms.code) like '%65 DAYS%' THEN 65
    when UPPER(enterprise_raw.payment_terms.code) like '%+%70 DAYS%' THEN 70
    when UPPER(enterprise_raw.payment_terms.code) like '%+%86 DAYS%' THEN 86
    when UPPER(enterprise_raw.payment_terms.code) like '%+%90 DAYS%' THEN 90
    when UPPER(enterprise_raw.payment_terms.code) like '%90%NET%' THEN 90
    when UPPER(enterprise_raw.payment_terms.code) like '%120%NET%' THEN 120
    
    when UPPER(enterprise_raw.payment_terms.description) like '%DUE%ON RECEIPT%' THEN 0
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%1' THEN 1
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%10' THEN 10
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%14%' THEN 14
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%15%' THEN 15
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%20%' THEN 20
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%25%' THEN 25
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%30%' THEN 30
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%31%' THEN 31
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%35%' THEN 35
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%45%' THEN 45
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%60%' THEN 60
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%90%' THEN 90
    when UPPER(enterprise_raw.payment_terms.description) like '%NET%120%' THEN 120
    when UPPER(enterprise_raw.payment_terms.description) like '%10%NET%' THEN 10
    when UPPER(enterprise_raw.payment_terms.description) like '%14%NET%' THEN 14
    when UPPER(enterprise_raw.payment_terms.description) like '%15%NET%' THEN 15
    when UPPER(enterprise_raw.payment_terms.description) like '%30%NET%' THEN 30
    when UPPER(enterprise_raw.payment_terms.description) like '%31%NET%' THEN 31
    when UPPER(enterprise_raw.payment_terms.description) like '%35%NET%' THEN 35
    when UPPER(enterprise_raw.payment_terms.description) like '%45%NET%' THEN 45
    when UPPER(enterprise_raw.payment_terms.description) like '%60%NET%' THEN 60
    when UPPER(enterprise_raw.payment_terms.description) like '%90%NET%' THEN 90
    when UPPER(enterprise_raw.payment_terms.description) like '%120%NET%' THEN 120
    
    when UPPER(enterprise_raw.payment_terms.description) like '%NET CASH%' THEN 0
    when UPPER(enterprise_raw.payment_terms.description) like '%PAYABLE IMMEDIATELY%' THEN 0
    when UPPER(enterprise_raw.payment_terms.description) like '%PAYMENT WITH ORDER%' THEN 0
    when UPPER(enterprise_raw.payment_terms.description) like '%+%1 DAYS%' THEN 1
    when UPPER(enterprise_raw.payment_terms.description) like '%+%5 DAYS%' THEN 5
    when UPPER(enterprise_raw.payment_terms.description) like '%+%7 DAYS%' THEN 7
    when UPPER(enterprise_raw.payment_terms.description) like '%+%8 DAYS%' THEN 8
    when UPPER(enterprise_raw.payment_terms.description) like '%+%14 DAYS%' THEN 14
    when UPPER(enterprise_raw.payment_terms.description) like '%+%15 DAYS%' THEN 15
    when UPPER(enterprise_raw.payment_terms.description) like '%MOIS LE 15%' THEN 15
    when UPPER(enterprise_raw.payment_terms.description) like '%+%18 DAYS%' THEN 18
    when UPPER(enterprise_raw.payment_terms.description) like '%+%20 DAYS%' THEN 20
    when UPPER(enterprise_raw.payment_terms.description) like '%+%21 DAYS%' THEN 21
    when UPPER(enterprise_raw.payment_terms.description) like '%+%25 DAYS%' THEN 25
    when UPPER(enterprise_raw.payment_terms.description) like '%+%27 DAYS%' THEN 27
    when UPPER(enterprise_raw.payment_terms.description) like '%+%28 DAYS%' THEN 28
    when UPPER(enterprise_raw.payment_terms.description) like '%30 DAYS%' THEN 30
    when UPPER(enterprise_raw.payment_terms.description) like '%END OF%MONTH%' THEN 30
    when UPPER(enterprise_raw.payment_terms.description) like '%+%1 MONTH%' THEN 30
    when UPPER(enterprise_raw.payment_terms.description) like '%35 DAYS%' THEN 35
    when UPPER(enterprise_raw.payment_terms.description) like '%+%37 DAYS%' THEN 37
    when UPPER(enterprise_raw.payment_terms.description) like '%+%42 DAYS%' THEN 42
    when UPPER(enterprise_raw.payment_terms.description) like '%+%43 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.description) like '%+%45 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.description) like '%,%45 DAYS%' THEN 45
    when UPPER(enterprise_raw.payment_terms.description) like '%+%56 DAYS%' THEN 56
    when UPPER(enterprise_raw.payment_terms.description) like '%60 DAYS%' THEN 60
    when UPPER(enterprise_raw.payment_terms.description) like '%+%2 MONTH%' THEN 60
    when UPPER(enterprise_raw.payment_terms.description) like '%65 DAYS%' THEN 65
    when UPPER(enterprise_raw.payment_terms.description) like '%+%70 DAYS%' THEN 70
    when UPPER(enterprise_raw.payment_terms.description) like '%+%86 DAYS%' THEN 86
    when UPPER(enterprise_raw.payment_terms.description) like '%+%90 DAYS%' THEN 90
    when UPPER(enterprise_raw.payment_terms.description) like '%90%NET%' THEN 90
    when UPPER(enterprise_raw.payment_terms.description) like '%120%NET%' THEN 120
    
    ELSE NULL END) as days_for_net_payment,
    enterprise_raw.payment_terms.code,
    enterprise_raw.payment_terms.description
    
    from enterprise_raw.suppliers
    LEFT OUTER JOIN enterprise_raw.payment_terms
    ON cast(enterprise_raw.payment_terms.id as varchar) = cast(enterprise_raw.suppliers.payment_term_id as varchar)
    AND cast(enterprise_raw.payment_terms.instance as varchar) = cast(enterprise_raw.suppliers.instance as varchar)
    LEFT OUTER JOIN enterprise_raw.addresses
    ON cast(enterprise_raw.suppliers.instance as varchar) = cast(enterprise_raw.addresses.instance as varchar)
    AND cast(enterprise_raw.suppliers.primary_address_id as varchar) = cast(enterprise_raw.addresses.id as varchar)
    left outer join enterprise_raw.countries 
    ON cast(enterprise_raw.addresses.country_id as varchar)=cast(enterprise_raw.countries.id as varchar)
    AND cast(enterprise_raw.addresses.instance as varchar)=cast(enterprise_raw.countries.instance as varchar)
    LEFT OUTER JOIN enterprise_raw.contacts
    ON cast(enterprise_raw.suppliers.instance as varchar) = cast(enterprise_raw.contacts.instance as varchar)
    AND cast(enterprise_raw.suppliers.primary_contact_id as varchar) = cast(enterprise_raw.contacts.id as varchar)
    LEFT OUTER JOIN enterprise_raw.phone_numbers AS mobile_number
    ON cast(mobile_number.instance as varchar) = cast(enterprise_raw.contacts.instance as varchar)
    AND cast(mobile_number.id as varchar) = cast(enterprise_raw.contacts.mobile_phone_id as varchar)
    LEFT OUTER JOIN enterprise_raw.phone_numbers AS work_number
    ON cast(work_number.instance as varchar) = cast(enterprise_raw.contacts.instance as varchar)
    AND cast(work_number.id as varchar) = cast(enterprise_raw.contacts.work_phone_id as varchar)
    LEFT OUTER JOIN enterprise_raw.currencies
    ON cast(enterprise_raw.currencies.instance as varchar) = cast(enterprise_raw.countries. instance as varchar)
    AND cast(enterprise_raw.currencies.id as varchar) = cast(enterprise_raw.countries.currency_id as varchar)
    
    left join supplier_master.current smc 
    on smc.instance = enterprise_raw.suppliers.instance
    and smc.id = cast(enterprise_raw.suppliers.id as varchar)
    
    left JOIN enterprise_raw.normalized_supplier_insights
    ON cast(enterprise_raw.suppliers.id as varchar) = cast(enterprise_raw.normalized_supplier_insights.supplier_id as varchar) AND
    cast(enterprise_raw.normalized_supplier_insights.instance as varchar) = cast(enterprise_raw.suppliers.instance as varchar)
    
    where enterprise_raw.suppliers.instance={{hashed_instance}}),

suppliers_two as
    (select *
    from
    (select
    RANK() OVER(PARTITION BY supplier_master_id ORDER BY count_connected_customers DESC) as rank,
    *
    from
    (select 
    sum(case when enterprise_raw.suppliers.coupa_connect_status = 'Linked' then 1 else 0 end) as community_linked_CSP,
    count(enterprise_raw.normalized_supplier_insights.supplier_master_id) as count_connected_customers,
    enterprise_raw.normalized_supplier_insights.supplier_master_id,
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_san = 0) THEN 'No' ELSE 'Yes' END "community_uses_SAN",
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_csp = 0) THEN 'No' ELSE 'Yes' END "community_uses_CSP",
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_cxml = 0) THEN 'No' ELSE 'Yes' END "community_uses_cXML",
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_sftp = 0) THEN 'No' ELSE 'Yes' END "community_uses_SFTP"
    
    from enterprise_raw.suppliers
    
    join enterprise_raw.normalized_supplier_insights
    on cast(enterprise_raw.normalized_supplier_insights.supplier_id as varchar) = cast(enterprise_raw.suppliers.id as varchar)
    and cast(enterprise_raw.normalized_supplier_insights.instance as varchar) = cast(enterprise_raw.suppliers.instance as varchar)
    
    where enterprise_raw.normalized_supplier_insights.supplier_master_id IS NOT NULL
    
    group by 
    enterprise_raw.normalized_supplier_insights.supplier_master_id,
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_san = 0) THEN 'No' ELSE 'Yes' END,
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_csp = 0) THEN 'No' ELSE 'Yes' END,
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_cxml = 0) THEN 'No' ELSE 'Yes' END,
    case when (enterprise_raw.normalized_supplier_insights.g_c_invoice_sftp = 0) THEN 'No' ELSE 'Yes' END)
    )
    where rank =1
    ),
vcard as
    (-- rewrote this query on 2022.08.29 after a discussion in #coupa-pay-sme channel. no longer using virtual_cards table and focusing only on charges.
select 
nsi.supplier_master_id,
case when sum(ch.reporting_total) > 0 then 'Yes' else null end as accepting_vcard

from enterprise_raw.charges ch

join enterprise_raw.normalized_supplier_insights nsi
on nsi.instance = ch.instance
and nsi.supplier_id = ch.supplier_id 

where  date_trunc('month', ch.charge_date) BETWEEN date_trunc('month', date_add('month', -12, now())) AND date_trunc('month', now())
and supplier_master_id is not null

group by
nsi.supplier_master_id)



select 
coalesce(suppliers_one.id, invoice_and_po_data.supplier_id) as supplier_id,
coalesce(suppliers_one.supplier_name, invoice_and_po_data.supplier_name) as supplier_name,
coalesce(suppliers_one.supplier_display_name, invoice_and_po_data.supplier_display_name) as supplier_display_name,
coalesce(suppliers_one.supplier_master_id, suppliers_two.supplier_master_id) as supplier_master_id,
-- added ' to front to prevent excel from changing the number format of text_id
-- suppliers_one.tax_id as tax_id, 
'' as tax_id, 
suppliers_one.coupa_connect_status as linked_with_customer_on_csp,
case when 
suppliers_one.coupa_connect_status = 'Linked' then 'yes'
when suppliers_two.community_linked_CSP > 0 then 'yes' 
else 'unknown' end as supplier_on_CSP,
suppliers_one.email,
suppliers_one.name_fullname,
suppliers_one.mobile_number,
suppliers_one.work_number,
suppliers_one.street1,
suppliers_one.street2,
suppliers_one.street3,
suppliers_one.street4,
suppliers_one.city,
suppliers_one.state,
suppliers_one.postal_code,
-- attempting to fill in the country details in cases where it was omitted
CASE
    WHEN suppliers_one.country_name IS NULL AND trim(suppliers_one.state) in (
    'Alabama','AL',
    'Alaska','AK',
    'Arizona','AZ',
    'Arkansas','AR',
    'California','CA',
    'Colorado','CO',
    'Connecticut','CT',
    'Delaware','DE',
    'Florida','FL',
    'Georgia','GA',
    'Hawaii','HI',
    'Idaho','ID',
    'Illinois','IL',
    'Indiana','IN',
    'Iowa','IA',
    'Kansas','KS',
    'Kentucky','KY',
    'Louisiana','LA',
    'Maine','ME',
    'Maryland','MD',
    'Massachusetts','MA',
    'Michigan','MI',
    'Minnesota','MN',
    'Mississippi','MS',
    'Missouri','MO',
    'Montana','MT',
    'Nebraska','NE',
    'Nevada','NV',
    'New Hampshire','NH',
    'New Jersey','NJ',
    'New Mexico','NM',
    'New York','NY',
    'North Carolina','NC',
    'North Dakota','ND',
    'Ohio','OH',
    'Oklahoma','OK',
    'Oregon','OR',
    'Pennsylvania','PA',
    'Rhode Island','RI',
    'South Carolina','SC',
    'South Dakota','SD',
    'Tennessee','TN',
    'Texas','TX',
    'Utah','UT',
    'Vermont','VT',
    'Virginia','VA',
    'Washington','WA',
    'West Virginia','WV',
    'Wisconsin','WI',
    'Wyoming','WY'
    )
    THEN 'United States'
    
    WHEN suppliers_one.country_name IS NULL AND trim(suppliers_one.state) in (
    'Newfoundland and Labrador','NL',
    'Prince Edward Island','PE',
    'Nova Scotia','NS',
    'New Brunswick','NB',
    'Quebec','QC',
    'Ontario','ON',
    'Manitoba','MB',
    'Saskatchewan','SK',
    'Alberta','AB',
    'British Columbia','BC',
    'Yukon','YT',
    'Northwest Territories','NT',
    'Nunavut','NU'
    
    )
THEN 'Canada'

else suppliers_one.country_name END as country_name,
-- attempting to fill in the country details in cases where it was omitted
CASE
    WHEN suppliers_one.country_id IS NULL AND trim(suppliers_one.state) in (
    'Alabama','AL',
    'Alaska','AK',
    'Arizona','AZ',
    'Arkansas','AR',
    'California','CA',
    'Colorado','CO',
    'Connecticut','CT',
    'Delaware','DE',
    'Florida','FL',
    'Georgia','GA',
    'Hawaii','HI',
    'Idaho','ID',
    'Illinois','IL',
    'Indiana','IN',
    'Iowa','IA',
    'Kansas','KS',
    'Kentucky','KY',
    'Louisiana','LA',
    'Maine','ME',
    'Maryland','MD',
    'Massachusetts','MA',
    'Michigan','MI',
    'Minnesota','MN',
    'Mississippi','MS',
    'Missouri','MO',
    'Montana','MT',
    'Nebraska','NE',
    'Nevada','NV',
    'New Hampshire','NH',
    'New Jersey','NJ',
    'New Mexico','NM',
    'New York','NY',
    'North Carolina','NC',
    'North Dakota','ND',
    'Ohio','OH',
    'Oklahoma','OK',
    'Oregon','OR',
    'Pennsylvania','PA',
    'Rhode Island','RI',
    'South Carolina','SC',
    'South Dakota','SD',
    'Tennessee','TN',
    'Texas','TX',
    'Utah','UT',
    'Vermont','VT',
    'Virginia','VA',
    'Washington','WA',
    'West Virginia','WV',
    'Wisconsin','WI',
    'Wyoming','WY'
) THEN 223

WHEN suppliers_one.country_id IS NULL AND trim(suppliers_one.state) in (
    'Newfoundland and Labrador','NL',
    'Prince Edward Island','PE',
    'Nova Scotia','NS',
    'New Brunswick','NB',
    'Quebec','QC',
    'Ontario','ON',
    'Manitoba','MB',
    'Saskatchewan','SK',
    'Alberta','AB',
    'British Columbia','BC',
    'Yukon','YT',
    'Northwest Territories','NT',
    'Nunavut','NU'
) THEN 39

else suppliers_one.country_id END as country_id,

-- attempting to fill in the country details in cases where it was omitted
CASE
    WHEN suppliers_one.alpha_three_code IS NULL AND trim(suppliers_one.state) in (
    'Alabama','AL',
    'Alaska','AK',
    'Arizona','AZ',
    'Arkansas','AR',
    'California','CA',
    'Colorado','CO',
    'Connecticut','CT',
    'Delaware','DE',
    'Florida','FL',
    'Georgia','GA',
    'Hawaii','HI',
    'Idaho','ID',
    'Illinois','IL',
    'Indiana','IN',
    'Iowa','IA',
    'Kansas','KS',
    'Kentucky','KY',
    'Louisiana','LA',
    'Maine','ME',
    'Maryland','MD',
    'Massachusetts','MA',
    'Michigan','MI',
    'Minnesota','MN',
    'Mississippi','MS',
    'Missouri','MO',
    'Montana','MT',
    'Nebraska','NE',
    'Nevada','NV',
    'New Hampshire','NH',
    'New Jersey','NJ',
    'New Mexico','NM',
    'New York','NY',
    'North Carolina','NC',
    'North Dakota','ND',
    'Ohio','OH',
    'Oklahoma','OK',
    'Oregon','OR',
    'Pennsylvania','PA',
    'Rhode Island','RI',
    'South Carolina','SC',
    'South Dakota','SD',
    'Tennessee','TN',
    'Texas','TX',
    'Utah','UT',
    'Vermont','VT',
    'Virginia','VA',
    'Washington','WA',
    'West Virginia','WV',
    'Wisconsin','WI',
    'Wyoming','WY'
)THEN 'USA'

WHEN suppliers_one.alpha_three_code IS NULL AND trim(suppliers_one.state) in (
    'Newfoundland and Labrador','NL',
    'Prince Edward Island','PE',
    'Nova Scotia','NS',
    'New Brunswick','NB',
    'Quebec','QC',
    'Ontario','ON',
    'Manitoba','MB',
    'Saskatchewan','SK',
    'Alberta','AB',
    'British Columbia','BC',
    'Yukon','YT',
    'Northwest Territories','NT',
    'Nunavut','NU'
)THEN 'CAN'

else suppliers_one.alpha_three_code END as alpha_three_code,
-- attempting to fill in the currency details in cases where country was omitted in address
CASE
    WHEN suppliers_one.currency_name IS NOT NULL THEN suppliers_one.currency_name
    WHEN suppliers_one.currency_name IS NULL AND trim(suppliers_one.state) in (
    'Alabama','AL',
    'Alaska','AK',
    'Arizona','AZ',
    'Arkansas','AR',
    'California','CA',
    'Colorado','CO',
    'Connecticut','CT',
    'Delaware','DE',
    'Florida','FL',
    'Georgia','GA',
    'Hawaii','HI',
    'Idaho','ID',
    'Illinois','IL',
    'Indiana','IN',
    'Iowa','IA',
    'Kansas','KS',
    'Kentucky','KY',
    'Louisiana','LA',
    'Maine','ME',
    'Maryland','MD',
    'Massachusetts','MA',
    'Michigan','MI',
    'Minnesota','MN',
    'Mississippi','MS',
    'Missouri','MO',
    'Montana','MT',
    'Nebraska','NE',
    'Nevada','NV',
    'New Hampshire','NH',
    'New Jersey','NJ',
    'New Mexico','NM',
    'New York','NY',
    'North Carolina','NC',
    'North Dakota','ND',
    'Ohio','OH',
    'Oklahoma','OK',
    'Oregon','OR',
    'Pennsylvania','PA',
    'Rhode Island','RI',
    'South Carolina','SC',
    'South Dakota','SD',
    'Tennessee','TN',
    'Texas','TX',
    'Utah','UT',
    'Vermont','VT',
    'Virginia','VA',
    'Washington','WA',
    'West Virginia','WV',
    'Wisconsin','WI',
    'Wyoming','WY'
) then 'United States of America, Dollars'

WHEN suppliers_one.currency_name IS NULL AND trim(suppliers_one.state) in (
    'Newfoundland and Labrador','NL',
    'Prince Edward Island','PE',
    'Nova Scotia','NS',
    'New Brunswick','NB',
    'Quebec','QC',
    'Ontario','ON',
    'Manitoba','MB',
    'Saskatchewan','SK',
    'Alberta','AB',
    'British Columbia','BC',
    'Yukon','YT',
    'Northwest Territories','NT',
    'Nunavut','NU'
) THEN 'Canada, Dollars'

WHEN invoice_and_po_data.po_spend_local_currency = invoice_and_po_data.invoice_spend_local_currency and invoice_and_po_data.invoice_spend_local_currency IS NOT NULL THEN invoice_and_po_data.invoice_spend_local_currency
ELSE NULL END as supplier_currency,
suppliers_one.code as payment_terms_code,
suppliers_one.days_for_net_payment,
suppliers_one.description,
suppliers_one.supplier_may_offer_discount,
invoice_and_po_data.po_alpha_three_code,
invoice_and_po_data.po_spend_local_currency,
invoice_and_po_data.po_spend,
invoice_and_po_data.po_spend_usd,
invoice_and_po_data.po_spend_total_orders as total_orders,
invoice_and_po_data.invoice_alpha_three_code,
invoice_and_po_data.invoice_spend_local_currency,
invoice_and_po_data.invoice_spend,
invoice_and_po_data.invoice_spend_usd,
invoice_and_po_data.total_invoices,
((CASE WHEN invoice_and_po_data.invoice_spend_usd IS NULL THEN 0 ELSE invoice_and_po_data.invoice_spend_usd END) / NULLIF(CASE WHEN invoice_and_po_data.total_invoices IS NULL THEN 0 ELSE invoice_and_po_data.total_invoices END,0)) as invoice_average_spend_usd,

(CASE
-- scenario 1
WHEN suppliers_one.currency_name IS NULL
    and invoice_and_po_data.po_spend_local_currency IS NULL 
    and invoice_and_po_data.invoice_spend_local_currency IS NULL THEN 'no'
-- scenario 3    
WHEN suppliers_one.currency_name IS NULL
    and invoice_and_po_data.po_spend_local_currency IS NOT NULL 
    and invoice_and_po_data.invoice_spend_local_currency IS NOT NULL
    and invoice_and_po_data.po_spend_local_currency <> invoice_and_po_data.invoice_spend_local_currency THEN 'yes'
-- scenario 4    
WHEN invoice_and_po_data.po_spend_local_currency IS NULL
    and suppliers_one.currency_name IS NOT NULL 
    and invoice_and_po_data.invoice_spend_local_currency IS NOT NULL
    and suppliers_one.currency_name <> invoice_and_po_data.invoice_spend_local_currency THEN 'yes'
-- scenario 5    
WHEN invoice_and_po_data.invoice_spend_local_currency IS NULL
    and invoice_and_po_data.po_spend_local_currency IS NOT NULL 
    and suppliers_one.currency_name IS NOT NULL
    and invoice_and_po_data.po_spend_local_currency <> suppliers_one.currency_name THEN 'yes'
-- scenario 7, scenario 8
WHEN suppliers_one.currency_name IS NOT NULL
    and invoice_and_po_data.po_spend_local_currency IS NOT NULL 
    and invoice_and_po_data.invoice_spend_local_currency IS NOT NULL
    and ((invoice_and_po_data.po_spend_local_currency <> invoice_and_po_data.invoice_spend_local_currency) 
        or (invoice_and_po_data.po_spend_local_currency <> suppliers_one.currency_name) 
        or (suppliers_one.currency_name <> invoice_and_po_data.invoice_spend_local_currency)) THEN 'yes'
ELSE 'no' END) as cross_border_spend,
invoice_and_po_data.electronic_invoices_via_SAN,
suppliers_two.community_uses_SAN,
invoice_and_po_data.electronic_invoices_via_CSP,
suppliers_two.community_uses_CSP,
invoice_and_po_data.electronic_invoices_via_cXML,
suppliers_two.community_uses_cXML,
invoice_and_po_data.electronic_invoices_via_SFTP,
suppliers_two.community_uses_SFTP,
invoice_and_po_data.partially_electronic_invoices_via_email,
invoice_and_po_data.partially_electronic_invoices_via_invoicesmash,
invoice_and_po_data.non_electronic_invoices_via_API,
invoice_and_po_data.non_electronic_invoices_via_easy_form,
invoice_and_po_data.non_electronic_invoices_via_paper,
vcard.accepting_vcard

from 
(select
(CASE
WHEN invoice_spend.id IS NOT NULL THEN invoice_spend.id
ELSE po_spend.id END) as supplier_id,
(CASE
WHEN invoice_spend.name IS NOT NULL THEN invoice_spend.name
ELSE po_spend.name END) as supplier_name,
(CASE
WHEN invoice_spend.display_name IS NOT NULL THEN invoice_spend.display_name
ELSE po_spend.display_name END) as supplier_display_name,
invoice_spend.local_currency as invoice_spend_local_currency,
invoice_spend.invoice_alpha_three_code,
invoice_spend.total_spend as invoice_spend,
invoice_spend.total_spend_usd as invoice_spend_usd,
invoice_spend.total_invoices as total_invoices,
invoice_spend.count_action_email as electronic_invoices_via_SAN,
invoice_spend.count_csn as electronic_invoices_via_CSP,
invoice_spend.count_cxml as electronic_invoices_via_cXML,
invoice_spend.count_sftp as electronic_invoices_via_SFTP,
invoice_spend.count_email as partially_electronic_invoices_via_email,
invoice_spend.count_invoicesmash as partially_electronic_invoices_via_invoicesmash,
invoice_spend.count_api as non_electronic_invoices_via_API,
invoice_spend.count_easy_form as non_electronic_invoices_via_easy_form,
invoice_spend.count_enterprise as non_electronic_invoices_via_paper,
po_spend.local_currency as po_spend_local_currency,
po_spend.po_alpha_three_code,
po_spend.total_spend as po_spend,
po_spend.total_spend_usd as po_spend_usd,
po_spend.total_orders as po_spend_total_orders

from invoice_spend

full outer join po_spend 
on invoice_spend.name=po_spend.name
and invoice_spend.local_currency = po_spend.local_currency) as invoice_and_po_data

full outer join suppliers_one
on suppliers_one.id = invoice_and_po_data.supplier_id

left join suppliers_two
on suppliers_two.supplier_master_id = suppliers_one.supplier_master_id

left join vcard
on suppliers_one.supplier_master_id = vcard.supplier_master_id

-- where (total_invoices >= 1 or invoice_and_po_data.po_spend_total_orders >= 1)

--group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52

order by supplier_id

Please post diagnostic info