Using functions in queries

[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.

[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;

[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)

[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)