Unable to convert string to number on Redshift

Asked Mar 08 '23 at 20:54

Active Mar 08 '23 at 22:39

Viewed 160 times

I'm running this code in an Amazon Redshift query:

case when offer.disbursement_required = 'true' and  (json_extract_array_element_text(offer.disbursement_requirements, 2, true) :: numeric <> offer.amount  or (cast(json_extract_array_element_text(offer.disbursement_requirements, 2, true) :: numeric) is null or offer.amount  is null) 
        and not ((cast(json_extract_array_element_text(offer.disbursement_requirements, 2, true)) :: numeric) is null and offer.amount  is null) then 1

This is returning:

[Code: 500310, SQL State: 0A000] Amazon Invalid operation: cannot cast type cstring to numeric;

and I'm not sure why.

Any help would be appreciated!! :)

edited Mar 08 '23 at 22:39

John Rotenstein

241,921
22
380
470

asked Mar 08 '23 at 20:54

Prathyusha

What does `json_extract_array_element_text(offer.disbursement_requirements, 2, true)` contain? – John Rotenstein Mar 08 '23 at 22:43
{"maxAllocation": null, "minAllocation": 8000.00} @john – Prathyusha Mar 09 '23 at 00:58
Then that is the cause of the error... How would `{"maxAllocation": null, "minAllocation": 8000.00}` convert to a numeric? – John Rotenstein Mar 09 '23 at 01:08
What is the best way to convert the minallocation value to a numeric in redshift? – Prathyusha Mar 14 '23 at 17:46
You'd probably need to use another `json_extract_array_element_text()` to extract the `8000`. – John Rotenstein Mar 14 '23 at 22:50

Unable to convert string to number on Redshift

0 Answers0