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 ?
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.
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)