question Re: Phoenix function in the Where clause in Archives of Support Questions (Read Only)

Phoenix function in the Where clause

dattatri_chandr — Fri, 15 Apr 2016 22:15:05 GMT

We have a phoenix table with "key" as time in millisec. I am trying to get count of number of records for the last 10 minutes by using Phoenix functions in the where clause, but not going anywhere. It just returns the zero count.

Anyone tried functions on the where clause something like below ?

select count(*) from CDC where CDC."key" > TO_CHAR((TO_NUMBER(NOW())-600000);

-Datta

Re: Phoenix function in the Where clause

elserj — Fri, 15 Apr 2016 22:26:33 GMT

Can you share your table schema, please? Also, it might help you to do something like the following to help debug

select key, TO_CHAR(TO_NUMBER(NOW())-600000) from CDC;

Re: Phoenix function in the Where clause

asinghal — Fri, 15 Apr 2016 22:40:58 GMT

yes , functions in where clause works perfectly.

I think to_char() will be giving epoch with commas

Re: Phoenix function in the Where clause

dattatri_chandr — Fri, 15 Apr 2016 23:05:48 GMT

Hi Josh, Thanks for the response. It works fine in the select query. I built the function by putting it in select query something like below. 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> select "key", TO_CHAR(TO_NUMBER(NOW())-600000) as TM from CDC LIMIT 5;

+------------------------------------------+-------------------+ | key | TM | +------------------------------------------+-------------------+ | 1460067042710,EPT,26|T|Z978926||1 | 1,460,734,960,474 | | 1460067042710,EPT,26|T|Z978926||8 | 1,460,734,960,474 | | 1460067042711,EPT,26|T|Z978926||1 | 1,460,734,960,474 | | 1460067042711,EPT,26|T|Z978926||8 | 1,460,734,960,474 | | 1460067042712,EPT,26|T|Z978926||1 | 1,460,734,960,474 | +------------------------------------------+-------------------+

Also works fine, If I put the hardcoded value in the function part of where clause ... 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> SELECT COUNT(*) FROM CDC WHERE "key"> '1460734960474'; +------------------------------------------+ | COUNT(1) | +------------------------------------------+ | 539753 | +------------------------------------------+ Also tried putting it in "where (regexp_split(CDC."key",',')[1]) > TO_CHAR((TO_NUMBER(NOW())-60000)" didn't work. DDL is as below : DROP VIEW IF EXISTS CDC; CREATE VIEW CDC ( "key" VARCHAR primary key ) default_column_family='d';

Re: Phoenix function in the Where clause

elserj — Fri, 15 Apr 2016 23:13:52 GMT

"where (regexp_split(CDC."key",',')[1]) > TO_CHAR((TO_NUMBER(NOW())-60000)"

You want to be doing the regexp_split on the TO_CHAR side of that expression, not the key side. Like Ankit pointed out, TO_CHAR is putting the commas in the number while your key does not have commas.

However, a comma sorts before all of the numbers so I would have thought that you would get all records (instead of none). Still, I think I would try fixing your WHERE clause to make sure you comparing numbers without commas on both sides of the expression.

Re: Phoenix function in the Where clause

dattatri_chandr — Fri, 15 Apr 2016 23:27:05 GMT

Thanks @asinghal & @Josh Elser . The following query fixed it..

select count(*) from CDC WHERE "key" > TO_CHAR(TO_NUMBER(NOW())-600000,'#############');

Appreciate your quick help.

-Datta