Hi All -
Im trying to create vertica year and month partition table.
Year and Month for the partition column has been drived from integer column - "PAYMENT_ID" using the user defined function - "PROD4.PAYID_PREDICT_TIMEZ"
For example - when i call this function .. this convert payment id into time stamp. as like below example..
dbadmin=> select PROD4.PAYID_PREDICT_TIMEZ(PAYMENT_ID), payment_id from PROD4.VAP_PAYMENTS_ADDITIONAL_DATA LIMIT 10
dbadmin-> ;
PAYID_PREDICT_TIMEZ | payment_id
---------------------+----------------
2013-01-08 01:47:48 | 26628156895938
2013-01-08 01:47:48 | 26628156896332
Using the function constructed the DDL to create partion table .. year and month drive from payment_id using the UDF. however table creation failed with error "ERROR 5371: User defined function not allowed: payid_predict_timez"
following is the DDL for your reference. If any one know fix to this issue.. please let me know. Thanks
CREATE TABLE PROD4.TEST_TABLE
(
PAYMENT_ID int NOT NULL,
PROCESSING_TYPE int,
ORIGINAL_TRANSACTION_AMOUNT int
)
PARTITION BY (((date_part('year', PROD4.PAYID_PREDICT_TIMEZ(TEST_TABLE.PAYMENT_ID)) * 100) + date_part('month', PROD4.PAYID_PREDICT_TIMEZ(TEST_TABLE.PAYMENT_ID))));
