How can I use substr function in query or find an alternative way

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How can I use substr function in query or find an alternative way

jiawei chen
Hi kylin's developers,
    I am using kylin-1.6.0 now. I have to use a sql query like:

SELECT
SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2) AS PERIOD,
COUNT(*) AS LL,
FROM table_name
GROUP BY
SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2);

and I got a failed response that shows no match found for function
signature TO_CHAR, (If I remove to_char, SUBSTR still not found).

Is there any plane to support these transform functions?
Or what alternative way can I choose?

Thank you!
Reply | Threaded
Open this post in threaded view
|

Re: How can I use substr function in query or find an alternative way

shaofengshi
Hi Jiawei, did you check Apache Calcite's SQL grammer? Kylin uses Calcite
to parse SQL, it can help: https://calcite.apache.org/docs/reference.html

2017-05-25 14:47 GMT+08:00 jiawei chen <[hidden email]>:

> Hi kylin's developers,
>     I am using kylin-1.6.0 now. I have to use a sql query like:
>
> SELECT
> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2) AS PERIOD,
> COUNT(*) AS LL,
> FROM table_name
> GROUP BY
> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2);
>
> and I got a failed response that shows no match found for function
> signature TO_CHAR, (If I remove to_char, SUBSTR still not found).
>
> Is there any plane to support these transform functions?
> Or what alternative way can I choose?
>
> Thank you!
>



--
Best regards,

Shaofeng Shi 史少锋
Reply | Threaded
Open this post in threaded view
|

Re: How can I use substr function in query or find an alternative way

Billy Liu
In reply to this post by jiawei chen
A better way is to define PERIOD in your Hive view, and load the Hive view
as the fact table.

2017-05-25 14:47 GMT+08:00 jiawei chen <[hidden email]>:

> Hi kylin's developers,
>     I am using kylin-1.6.0 now. I have to use a sql query like:
>
> SELECT
> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2) AS PERIOD,
> COUNT(*) AS LL,
> FROM table_name
> GROUP BY
> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2);
>
> and I got a failed response that shows no match found for function
> signature TO_CHAR, (If I remove to_char, SUBSTR still not found).
>
> Is there any plane to support these transform functions?
> Or what alternative way can I choose?
>
> Thank you!
>
Reply | Threaded
Open this post in threaded view
|

Re: How can I use substr function in query or find an alternative way

Julian Hyde
Except that PERIOD becomes a reserved keyword in the next version of Calcite. :)

On Thu, May 25, 2017 at 6:33 PM, Billy Liu <[hidden email]> wrote:

> A better way is to define PERIOD in your Hive view, and load the Hive view
> as the fact table.
>
> 2017-05-25 14:47 GMT+08:00 jiawei chen <[hidden email]>:
>
>> Hi kylin's developers,
>>     I am using kylin-1.6.0 now. I have to use a sql query like:
>>
>> SELECT
>> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2) AS PERIOD,
>> COUNT(*) AS LL,
>> FROM table_name
>> GROUP BY
>> SUBSTR(TO_CHAR(`time`,'YYYYMMDDHH24MISS'),9,2);
>>
>> and I got a failed response that shows no match found for function
>> signature TO_CHAR, (If I remove to_char, SUBSTR still not found).
>>
>> Is there any plane to support these transform functions?
>> Or what alternative way can I choose?
>>
>> Thank you!
>>