0

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?

Ralf
  • 16,086
  • 4
  • 44
  • 68
Ailcid
  • 5
  • 3
  • How about just getting rid of the where clause? – BlueEel Apr 24 '18 at 21:03
  • I will get the error if I did that. When I first write the script I don't have the where clause for the period but I found out that it's only converting those numbers with it. I even tried to convert those without periods to Integer and it gave me the same error. – Ailcid Apr 24 '18 at 21:07

3 Answers3

0

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.

Daniel
  • 3,312
  • 1
  • 14
  • 31
  • My original query doesn't have that clause and it's causing an error. I figured out that it's only converting/casting those with periods. – Ailcid Apr 24 '18 at 21:12
0

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.

Ailcid
  • 5
  • 3
0

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

dopple
  • 92
  • 10
  • What you say totally makes sense and I really appreciate your help. But I can’t test it now since our developers already fixed the data type issue. Thanks again! – Ailcid Jun 15 '18 at 06:14
  • Getting the data fixed at source is always the best option, if not always a viable option. It's good that you managed to get that done in your case. – dopple Jun 15 '18 at 09:16