Hello, im having following select:
count(*) as numcust,
sum(c_acctbal) as totacctbal
substring(c_phone, 1, 2) as cntrycode,
substring(c_phone, 1, 2) in (‘13’, ‘31’, ‘23’, ‘29’, ‘30’, ‘18’, ‘17’)
and c_acctbal > (
c_acctbal > 0.00
and substring(c_phone, 1, 2) in (‘13’, ‘31’, ‘23’, ‘29’, ‘30’, ‘18’, ‘17’)
and not exists (
o_custkey = c_custkey
But im getting: Function substring(text, integer, integer) not supported. However, i found in documentation , that function substring works. So, is there other way ?
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.
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.
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.