Sybase ASE 15.5 – Creating Custom Function for Partitioning

datetimepartitioningsybase

I want to create a partitioned table to store messages.

If the message belongs to "an even week number" => go to the partEven partition !
If the message belongs to "an odd week number" => go to the partOdd partition !

Here is my broken code :

CREATE TABLE myTable (
    id NUMERIC(18,0) IDENTITY,
    message_date_time DATETIME,
    PRIMARY KEY (id)
    )partition by list (message_date_time)
        (partEven datepart(week, message_date_time) % 2 == 0,
         partOdd datepart(week, message_date_time) % 2 == 1
        )

Could someone help me with the syntax ? (if it's possible this way)

It seems that I'm only allowed to route records on the "message_date_time" constant but I want to do it on the result of a function applied to "message_date_time".

Please help 🙂

Best Answer

You cannot write your own partition function. What you need to do is create an additional column, assign a value resulting from the function you want to use, and partition on that column. Unfortunately, you cannot partition on a computed column, which would be the easiest solution.

Related Question