Skip to main content

How to get LIMIT VALUE from projection metadata for TOPK aggregate type?

  • December 27, 2016
  • 3 replies
  • 18 views
AlexZH

Hi everyone!

 

Is it possible to get LIMIT VALUE from projection metadata for TOPK aggregate type?

 

I've tried to find this info in v_catalog.projections and v_catalog.projection_columns - but without success.

 

From v_catalog.projections - I got aggregate type - TOPK.

From v_catalog.projection_columns - I got columns in PARTITION BY and ORDER BY clauses.

 

I need to get LIMIT VALUE using SELECT statement (system tables/views) - so I can't use meta-function export_objects() because of error:
[Vertica][VJDBC](6809) ERROR: Inappropriate usage of meta-function ("export_objects")

 

Vertica version:
Vertica Analytic Database v7.2.2-1.

 

Projection example:
CREATE PROJECTION VRT_DWH.PRJ_SMPL_PART_BY_2 (Employee_gender, Courtesy_title, Employee_key) AS
SELECT Employee_gender,
Courtesy_title,
Employee_key
FROM VRT_DWH.TBL_SMPL_ORD_BY
LIMIT 5 OVER (PARTITION BY Employee_gender ORDER BY Courtesy_title DESC);

 

Thanks in advance.
Yours respectfully,
AlexZH

    3 replies

    Navin_C
    Forum|alt.badge.img+2
    • Navin_C
    • Participating Frequently
    • December 30, 2016

    What is the requirement here.

     

    Do you want to limit the resultset to 5 values everytime.

    Or you want to have 5 records in windows partition clause everytime.

    AlexZH
    • AlexZH
    • Author
    • New Participant
    • January 3, 2017

    Hi, NC!

     

    It's just test example, but main idea is - to have 5 records in windows partition clause everytime.

     

    Yours respectfully,
    AlexZH

    DerekW
    Forum|alt.badge.img
    • DerekW
    • Participating Frequently
    • January 6, 2017

    I'm not sure if this is stored in the meta data of the projection, but you could get the projection definition using export_objects('','<table_name>') and parse this (e.g. using regex) to retrieve the limit clause.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement