Problem with substring

Hello, im having following select:

select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from
(
select
substring(c_phone, 1, 2) as cntrycode,
c_acctbal
from
customer
where
substring(c_phone, 1, 2) in (‘13’, ‘31’, ‘23’, ‘29’, ‘30’, ‘18’, ‘17’)
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone, 1, 2) in (‘13’, ‘31’, ‘23’, ‘29’, ‘30’, ‘18’, ‘17’)
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
)
group by
cntrycode
order by
cntrycode;

But im getting: Function substring(text, integer, integer) not supported. However, i found in documentation , that function substring works. So, is there other way ?

Hi Dominik,

Thanks for reaching out. Can you please advise where you found the substring function in our latest documentation? We’d like to get this corrected.

https://docs.heavy.ai/v/v5.10.2/

The substring function is scheduled for release in HEAVY.AI v6.0*, but is not included in the latest release at the time of writing this reply, v5.10.2.

*Release version may be subject to change.

Hi @Dominik,

In that list there are all the reserved keywords, we inerith from the calcite parser and in the past the substring function was usable, on projection queries only, when the enable-interoperability parameter was turned on.
Anyway it didn’t work in aggregates so the tpch query was goong.to fail.

As @Neill said we are going to include that function, as other common string functions, that will also works on aggregates in the upcoming releases (it’s likely to be included in the 6.0)

I will come back to you when that happens.

Regards,
Candido

1 Like