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
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',
COMPENSABLE_SALES_AMOUNT DESC LIMIT 100;