We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Can I use Top-K projections against views and with where clauses? — Vertica Forum

Can I use Top-K projections against views and with where clauses?

I need to support top k queries by a key that gets passed into my query. All the examples I see for Top K projections don't have any conditional logic and the query that I have does. Can I use Top K or live aggregate projections for this case? Will it work against a view or only the table?

 

My query looks like this:

 

CREATE PROJECTION rep_agg AS SELECT
t1.EFF_DATE AS EFF_DATE,
t1.COMPENSABLE_FLAG AS COMPENSABLE_FLAG,
t1.AVAIL_IN_SAMI_IND AS AVAIL_IN_SAMI_IND,
t1.TXN_AMOUNT AS TXN_AMOUNT,
t1.COMPENSABLE_SALES_AMOUNT AS COMPENSABLE_SALES_AMOUNT,
t1.FUND_CODE AS FUND_CODE,
t1.COMPOSITE_CODE AS COMPOSITE_CODE,
t1.HOC_HEART_BEAT_ID AS HOC_HEART_BEAT_ID,
t1.CURRENT_DATE_FLAG AS CURRENT_DATE_FLAG,
t1.CURRENT_MONTH_FLAG AS CURRENT_MONTH_FLAG,
t1.CURRENT_YEAR_FLAG AS CURRENT_YEAR_FLAG,
t1.PRIOR_YEAR_FLAG AS PRIOR_YEAR_FLAG,
t1.RTL_REGION_NUMBER AS RTL_REGION_NUMBER
FROM
poc_db."public".REP_TRANSACTIONS_AGGREGATE_temp_V1 t1
WHERE
t1.FUND_CODE = '00220'
AND t1.RTL_REGION_NUMBER = '112'
AND t1.CURRENT_YEAR_FLAG = 'Y'
AND t1.AVAIL_IN_SAMI_IND = 'Y'
AND t1.TRANSACTION_CLASS_MAJOR_DESC IN ('Exchange In',
'Purchase')
ORDER BY
COMPENSABLE_SALES_AMOUNT DESC LIMIT 100;

Comments

Leave a Comment

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