CODEWITHSUNDEEP
denodo
Ailcid
Ailcid

Reputation: 5

Denodo: How to aggregate varchar data types?

I'm creating an aggregate from a anstime column in a view table in Denodo and I'm using a Cast to convert it to float and it works only for those numbers with period (example 123.123) but does not work for the numbers without period (example 123). Here's my code which only works for those numbers with period:

SELECT row_date,
    case
        when sum(cast(anstime as float)) is null or sum(cast(anstime as float)) = 0
        then 0
        else sum(cast(anstime as float))
    end as xans
FROM table where anstime like '%.%'
group by row_date

Can someone please help me how to handle those without period?

Upvotes: 0

Views: 383

Answers (3)

dopple
dopple

Reputation: 92

My guess is you've got values in anstime which are are not numeric, hence why not having the where anstime like '%.%' predicate causes a failure, as has been mentioned in other comments.

You could try adding in an intermediate view before this one which strips out any non numeric values (leaving the decimal point character of course) and this might then allow you to not have to use the where anstime like '%.%' filter.

Perhaps the REGEXP function which would possibly help there

Upvotes: 0

Ailcid
Ailcid

Reputation: 5

I appreciate those who responded to my concern. In the end we had to reach out to our developers to fix the data type of the column from varchar to float rather than doing a workaround.

Upvotes: 0

Daniel
Daniel

Reputation: 3370

Your where anstime like '%.%' clause is going to restrict possible responses to places where anstime has a period in it. Remove that if you want to allow all values.

Upvotes: 0

Related Questions