CODEWITHSUNDEEP
amazon-web-servicesamazon-s3prestoamazon-athena
caliGeek
caliGeek

Reputation: 419

Unable to use the logical operator despite of casting in the written query using Amazon Athena

I am unable to use a logical operator on one of my columns even though I'm casting it as a big int."total_submitted_charge amount" is stored as a string in my database. Below is my query:

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,
         try_cast(CAST(REPLACE(total_submitted_charge_amount,
         ',') AS DECIMAL) AS BIGINT) AS total_submitted_amount
FROM cmsaggregatepayment2017
WHERE provider_zipcode='90250'

As soon as I add " AND total_submitted_amount>2000", query stops working. Can someone please guide me

Upvotes: 0

Views: 238

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

Per SQL spec, total_submitted_amount is a projection and can be used only in the "outer" scope. I.e. you can either

SELECT * FROM ( <your query> ) WHERE total_submitted_amount > 2000

or

< your query >
...  AND try_cast(CAST(REPLACE(total_submitted_charge_amount, ',') AS DECIMAL) AS BIGINT) > 2000

Note: there is no performance penalty from using the expression second time, it will still be evaluated once per row in Presto (Athena is based on Presto).

Upvotes: 1

Related Questions