title: Casting not working correctly in Amazon Athena (Presto)?
body: <p>I have a doctor license registry dataset which includes the total_submitted_charge_amount for each doctor as well as the number of entitlements with medicare &amp; medicaid . I used the query from the answer suggested below:</p>
  
  <pre><code>    with datamart AS 
      (SELECT npi,
           provider_last_name,
           provider_first_name,
           provider_mid_initial,
           provider_address_1,
           provider_address_2,
           provider_city,
           provider_zipcode,
           provider_state_code,
           provider_country_code,
           provider_type,
           number_of_services,
  
          CASE
          WHEN REPLACE(num_entitlement_medicare_medicaid,',', '') ='' THEN
          null
          ELSE CAST(REPLACE(num_entitlement_medicare_medicaid,',', '') AS DECIMAL)
          END AS medicare_medicaid_entitlement,
          CASE
          WHEN REPLACE(total_submitted_charge_amount,',', '') ='' THEN
          null
          ELSE CAST(REPLACE(num_entitlement_medicare_medicaid,',', '') AS DECIMAL)
          END AS total_submitted_charge_amount
      FROM cmsaggregatepayment2017)
  SELECT *
  FROM datamart
  ORDER BY  total_submitted_charge_amount DESC
  </code></pre>
  
  <p>Unfortunately I get the error </p>
  
  <blockquote>
    <p>INVALID_CAST_ARGUMENT: Cannot cast VARCHAR '' to DECIMAL(38, 0)</p>
  </blockquote>
  
  <p>This query ran against the <code>aggregatepayment_data_2017</code> database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: <code>be01d1e8-dc4d-4c75-a648-428dcb6be3a5</code>." I have tried Decimal, Real, Big int and nothing works for casting num_entitlement_medicare_medicaid. Below is a screenshot of how the data looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/OKMAM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OKMAM.png" alt="enter image description here"></a></p>
  
  <p>Can someone please suggest how to rephrase this query?</p>