LucidDbAppLib GENERATE SEQUENCE

From Eigenpedia

NOTE: The applib UDR's sketched here do not actually exist yet; they are just proposals.

GENERATE_SEQUENCE

Declaration:

create or replace function generate_sequence(
  input_table cursor,
  start_number bigint,
  increment bigint)
returns table(
  input_table.*, 
  seq_num bigint)
language java
parameter style system defined java
deterministic
no sql
external name '...';

Input:

• INPUT_TABLE: rows for which ordinals are to be assigned
• START_NUMBER: the starting sequence number for the output SEQ_NUM column, can be a positive or negative number. If set to null, starting sequence number defaults to 1.
• INCREMENT: the step size for the sequence; can be a positive or negative number, but must not be 0. If positive, sequence number will be incremented by the value of the number; if negative, sequence number will be decremented by the absolute value of the number. If set to null, defaults to 1.

Output:

• a table with the same columns as the input table, plus one additional SEQ_NUM column of type BIGINT

Calculation:

• The output contains one row per input row, in the same order as the input. Input column values are passed through unmodified.
• The ith row of output has value START_NUMBER + i*INCREMENT for the SEQ_NUM column, with i=0 for the first row. Values beyond the range of BIGINT will wrap according to the rules of two's-complement representation.

Example Invocation:

select 
    step,
    dirty_seq,
    relative_seq,
    seq_num as absolute_seq
from table(
    generate_sequence(
        cursor(
            select step, dirty_seq, relative_seq
            from firing_squad_orders
            order by dirty_seq, relative_seq
        ),
        0,
        1));

Example Output:

See the last table in MondrianCompareSiblingsByOrderKeyProperty#Dirty_Data.

Exceptions:

• Value of zero specified for INCREMENT

GENERATE_SEQUENCE_PARTITIONED

Declaration:

create or replace function generate_sequence_partitioned(
  input_table cursor,
  partitioning_columns select from input_table,
  start_number bigint,
  increment bigint)
returns table(
  input_table.*, 
  seq_num bigint)
language java
parameter style system defined java
deterministic
no sql
external name '...';

Input:

• INPUT_TABLE: rows for which ordinals are to be assigned, presorted on PARTITIONING_COLUMNS
• PARTITIONING_COLUMNS: one or more columns of the input which are used to break the input set into independent partitions for the purposes of sequence generation
• START_NUMBER: the per-partition starting sequence number for the output SEQ_NUM column, can be a positive or negative number. If set to null, starting sequence number defaults to 1.
• INCREMENT: the step size for the sequence; can be a positive or negative number, but must not be 0. If positive, sequence number will be incremented by the value of the number; if negative, sequence number will be decremented by the absolute value of the number. If set to null, defaults to 1.

Output:

• a table with the same columns as the input table, plus one additional SEQ_NUM column of type BIGINT

Calculation:

• The output contains one row per input row, in the same order as the input. Input column values are passed through unmodified.
• For each distinct key within PARTITIONING_COLUMNS in the input set, identify a corresponding set of contiguous rows having that key as a partition.
• The ith row of output within a given partition has value START_NUMBER + i*INCREMENT for the SEQ_NUM column, with i=0 for the first row in the partition. Values beyond the range of BIGINT will wrap according to the rules of two's-complement representation.

Example Invocation:

select 
    step,
    dirty_seq,
    seq_num as relative_seq
from table(
    generate_sequence(
        cursor(
            select step, dirty_seq
            from firing_squad_orders
            order by dirty_seq
        ),
        row(dirty_seq),
        0,
        1));

Example Output:

See the first table in MondrianCompareSiblingsByOrderKeyProperty#Dirty_Data.

Exceptions:

• Input table not presorted correctly
• Value of zero specified for INCREMENT