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

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

Comments

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

  • Hi, NC!

     

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

     

    Yours respectfully,
    AlexZH

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.