FLATTENED TABLE TRY

avoronin

I am trying to Run the following code on vertica 9.3 .
I want to get the last value from another table within a a gorup indicated by a key in flattenedtable. Several solutions I have tried are listed below. Is there a solution to do that?

DROP TABLE IF EXISTS A_MART.SALES_DATA_TEST;
CREATE TABLE IF NOT EXISTS A_MART.SALES_DATA_TEST (
A_SD_LINES_ID INT PRIMARY KEY Not null ENCODING COMMONDELTA_COMP ACCESSRANK 1000, -- anchor key
SD_LINES_ID INT Not null ACCESSRANK 2000, -- business key
COST1 DECIMAL(18,2) SET USING (
-- ERROR: Correlated subquery expression without aggregates and with limit is not supported
SELECT COST FROM "A"."A_SD_LINES_COST" a16
WHERE A_MART.SALES_DATA_TEST.A_SD_LINES_ID = a16.A_SD_LINES_ID
ORDER BY a16.actual_date DESC
LIMIT 1
),
COST2 DECIMAL(18,2) SET USING (
-- ERROR: Correlated subquery expression without aggregates and with limit is not supported
SELECT COST FROM "A"."A_SD_LINES_COST" a16
WHERE A_MART.SALES_DATA_TEST.A_SD_LINES_ID = a16.A_SD_LINES_ID
LIMIT 1 OVER (PARTITION BY a16.A_SD_LINES_ID ORDER BY a16.actual_date DESC)
),
COST3 DECIMAL(18,2) SET USING (
-- ERROR: Correlated subquery expression without aggregates and with limit is not supported
SELECT LAST_VALUE(COST) OVER (PARTITION BY a16.A_SD_LINES_ID ORDER BY a16.actual_date DESC)
FROM "A"."A_SD_LINES_COST" a16
WHERE A_MART.SALES_DATA_TEST.A_SD_LINES_ID = a16.A_SD_LINES_ID
LIMIT 1
)
)
ORDER BY A_SD_LINES_ID
UNSEGMENTED ALL NODES;

INSERT INTO A_MART.SALES_DATA_TEST(A_SD_LINES_ID, SD_LINES_ID)
SELECT a0.A_SD_LINES_ID, a0.SD_LINES_ID FROM "A"."A_SD_LINES" a0
WHERE A_SD_LINES_ID % 10000 = 0;

SELECT REFRESH_COLUMNS ('A_MART.SALES_DATA_TEST','');