Page 1 of 1
Using functions in queries
Posted: Tue Jan 14, 2014 10:33 pm
by NorbertKrupa
Is there any work around to this?
Code: Select all
SELECT RIGHT(a.time, 29) FROM (SELECT GET_AHM_TIME() AS time) a;
I receive the error:
Meta-function ("get_ahm_time") can be used only in the Select clause
In SQL Server, there is CROSS APPLY, which let you
cross apply a function.
Re: Using functions in queries
Posted: Wed Jan 15, 2014 6:47 pm
by JimKnicely
norbertk,
I don't think we can use the Vertica meta-functions in SQL, other than in a simple select statement.
You can get the AHM time from the
VS_EPOCHS table when joining it to the
SYSTEM table...
Example:
Code: Select all
dbadmin=> select get_ahm_time();
get_ahm_time
-------------------------------------------------
Current AHM Time: 2014-01-15 06:00:01.194944-05
(1 row)
dbadmin=> select time from system join vs_epochs on epoch_number = ahm_epoch;
time
-------------------------------
2014-01-15 06:00:01.194944-05
(1 row)
So you might try something like:
- SELECT RIGHT(a.time, 29) FROM (SELECT time FROM system JOIN vs_epochs ON epoch_number = ahm_epoch) foo;
Re: Using functions in queries
Posted: Wed Jan 15, 2014 8:46 pm
by NorbertKrupa
Thanks for that query,
Nothing returns for me when I run that. I don't have any overlapping epochs.
Code: Select all
dbadmin=> SELECT * FROM vs_epochs;
epoch_number | time
--------------+-------------------------------
15256962 | 2014-01-15 13:24:40.966451-06
15257227 | 2014-01-15 13:27:40.73399-06
15257507 | 2014-01-15 13:30:40.575625-06
15257771 | 2014-01-15 13:33:40.431682-06
15257917 | 2014-01-15 13:35:17.869272-06
Code: Select all
dbadmin=> SELECT * FROM system;
current_epoch | ahm_epoch | last_good_epoch | refresh_epoch | designed_fault_tolerance | node_count | node_down_count | current_fault_tolerance | catalog_revision_number | wos_used_bytes | wos_row_count | ros_used_bytes | ros_row_count | total_used_bytes | total_row_count
---------------+-----------+-----------------+---------------+--------------------------+------------+-----------------+-------------------------+-------------------------+----------------+---------------+----------------+---------------+------------------+-----------------
15258000 | 15257430 | 15257492 | -1 | 1 | 8 | 0 | 1 | 15958846 | 4015325184 | 19230814 | 5722056396455 | 211603722833 | 5726071721639 | 211622953647
(1 row)
Re: Using functions in queries
Posted: Thu Jan 16, 2014 1:20 pm
by JimKnicely
Hi,
I thought that the earliest epoch stored in the vs_epochs table is always the AHM epoch. At least that has been my experience.
Maybe one of the other system tables that have a column containing 'AHM' might help you out?
Code: Select all
dbadmin=> select distinct nspname, relname, attname from vs_columns where attname ilike '%ahm%' order by 1, 2, 3;
nspname | relname | attname
------------+---------------------------+---------------------------
dc_tables | dc_catalog_info | ahm_epoch
dc_tables | dc_catalog_info | ahm_timestamp
dc_tables | dc_catalog_info_by_day | ahm_epoch_end_value
dc_tables | dc_catalog_info_by_day | ahm_epoch_peak_delta
dc_tables | dc_catalog_info_by_day | ahm_epoch_peak_end
dc_tables | dc_catalog_info_by_day | ahm_epoch_peak_start
dc_tables | dc_catalog_info_by_day | ahm_epoch_start_value
dc_tables | dc_catalog_info_by_day | ahm_timestamp_end_value
dc_tables | dc_catalog_info_by_day | ahm_timestamp_start_value
dc_tables | dc_catalog_info_by_hour | ahm_epoch_end_value
dc_tables | dc_catalog_info_by_hour | ahm_epoch_peak_delta
dc_tables | dc_catalog_info_by_hour | ahm_epoch_peak_end
dc_tables | dc_catalog_info_by_hour | ahm_epoch_peak_start
dc_tables | dc_catalog_info_by_hour | ahm_epoch_start_value
dc_tables | dc_catalog_info_by_hour | ahm_timestamp_end_value
dc_tables | dc_catalog_info_by_hour | ahm_timestamp_start_value
dc_tables | dc_catalog_info_by_minute | ahm_epoch_end_value
dc_tables | dc_catalog_info_by_minute | ahm_epoch_peak_delta
dc_tables | dc_catalog_info_by_minute | ahm_epoch_peak_end
dc_tables | dc_catalog_info_by_minute | ahm_epoch_peak_start
dc_tables | dc_catalog_info_by_minute | ahm_epoch_start_value
dc_tables | dc_catalog_info_by_minute | ahm_timestamp_end_value
dc_tables | dc_catalog_info_by_minute | ahm_timestamp_start_value
dc_tables | dc_catalog_info_by_second | ahm_epoch_end_value
dc_tables | dc_catalog_info_by_second | ahm_epoch_start_value
dc_tables | dc_catalog_info_by_second | ahm_timestamp_end_value
dc_tables | dc_catalog_info_by_second | ahm_timestamp_start_value
v_internal | dc_catalog_info | ahm_epoch
v_internal | dc_catalog_info | ahm_timestamp
v_internal | dc_catalog_info_by_day | ahm_epoch_end_value
v_internal | dc_catalog_info_by_day | ahm_epoch_peak_delta
v_internal | dc_catalog_info_by_day | ahm_epoch_peak_end
v_internal | dc_catalog_info_by_day | ahm_epoch_peak_start
v_internal | dc_catalog_info_by_day | ahm_epoch_start_value
v_internal | dc_catalog_info_by_day | ahm_timestamp_end_value
v_internal | dc_catalog_info_by_day | ahm_timestamp_start_value
v_internal | dc_catalog_info_by_hour | ahm_epoch_end_value
v_internal | dc_catalog_info_by_hour | ahm_epoch_peak_delta
v_internal | dc_catalog_info_by_hour | ahm_epoch_peak_end
v_internal | dc_catalog_info_by_hour | ahm_epoch_peak_start
v_internal | dc_catalog_info_by_hour | ahm_epoch_start_value
v_internal | dc_catalog_info_by_hour | ahm_timestamp_end_value
v_internal | dc_catalog_info_by_hour | ahm_timestamp_start_value
v_internal | dc_catalog_info_by_minute | ahm_epoch_end_value
v_internal | dc_catalog_info_by_minute | ahm_epoch_peak_delta
v_internal | dc_catalog_info_by_minute | ahm_epoch_peak_end
v_internal | dc_catalog_info_by_minute | ahm_epoch_peak_start
v_internal | dc_catalog_info_by_minute | ahm_epoch_start_value
v_internal | dc_catalog_info_by_minute | ahm_timestamp_end_value
v_internal | dc_catalog_info_by_minute | ahm_timestamp_start_value
v_internal | dc_catalog_info_by_second | ahm_epoch_end_value
v_internal | dc_catalog_info_by_second | ahm_epoch_start_value
v_internal | dc_catalog_info_by_second | ahm_timestamp_end_value
v_internal | dc_catalog_info_by_second | ahm_timestamp_start_value
v_internal | system | ahm_epoch
v_internal | vs_epoch_map | ahmepoch
(56 rows)