SQL Sequence Stretch0 Function

The seq_stretch0() function is an alternative to seq_stretch() for “stretching” a sequence by injecting 0 for “missing elements”, seq_stretch0() copies the elements of the third argument dVal3 for values in ts1 that are also present in ts2, or are present in ts2 but missing in ts1. The result sequence has the number of elements of ts1 plus the number of elements in ts2 that are missing in ts1.

Following is an example script demonstrating the seq_stretch0() function:

 
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
    VALUES(6,'{1,2,3,5}','{2,3,4}','{1.1,1.2,1.3}');
     
    SELECT ts1,ts2,dVal3,seq_stretch0(ts1,ts2,dVal3) AS "stretch0"
    FROM SimpleSequence WHERE testNumber=6;
     
    ts1{}
    ts2{}
    dVal3{}
    stretch0{}
    ----------------------------------------------------------------------------------
    {1, 2, 3, 5}
    {2, 3, 4}
    {1.100000, 1.200000, 1.300000}
    {0.000000, 1.100000, 1.200000, 1.300000, 0.000000}
     

So, in the example above the result sequence is obtained as follows:

0. The first element of ts1 (1) is missing in ts2, so the “filler” value (0.0) is copied into the result sequence.

1. The next element of ts1 is 2 which is present also in ts2, so the first element of dVal3 (1.1) is copied into the result sequence.

2. The next element of ts2 (3) matches the next element of ts1, so the next element of dVal3 (1.2) copied into the result sequence.

3. The next element in ts2 is 4 which is not present in ts1, so the next value in dVal3 (1.3) is copied into the result sequence.

4. The next element in ts1 is 5, which is not present in ts2, so the filler value of (0.0) is copied into the result sequence.

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    f 5