Sequence Iterator SQL Functions

The iterator functions are used to extract elements from sequences. The first and last functions return scalar values. The search() function is used to extract the specified range of elements from a materialized sequence.

seq_first_int( input )	Return the first value in the input sequence of integers
seq_first_real( input )	Return the first value in the input sequence of floating point numbers
seq_last_int( input )	Return the last value in the input sequence of integers
seq_last_real( input )	Return the last value in the input sequence of floating point numbers
seq_search( input, from, to )	Return the sequence with elements in the specified range from the materialized input sequence. The arguments from and to specify values in the object's sequence; boundary_from and boundary_to are boundary types typically specified as exdb.SeqIteratorBoundary.MCO_SEQ_BOUNDARY_INCLUSIVE or exdb.SeqIteratorBoundary.MCO_SEQ_BOUNDARY_EXCLUSIVE
seq_search_first( input, from, limit )	Combine the seq_search() and seq_first() to produce a sequence ordered according to the input sequence, starting with the value specified in the second argument from and having the maximum number of values specified in the third argument limit
seq_search_last( input, till, limit )	Combine the seq_search() and seq_last() to produce a sequence ordered according to the input sequence, starting with the value specified in the second argument till and having the maximum number of values specified in the third argument limit
seq_map( input1, input2 )	Extract from the input1 sequence elements with positions specified in the input2 sequence. (The positions should be provided in ascending order (these are typically obtained using methods filter_pos() or top_pos())
seq_project( des, src )	Project the materialized sequence src to the destination sequence dst.

 

Example

Following is an example code snippet demonstrating the first and last functions:

         
    -- _first_int, _last_int, _first_real, _last_real
            
    CREATE TABLE SimpleSequence11( testNumber unsigned(4) primary key,
        dVal1 sequence(double), dVal2 sequence(double) );
    INSERT INTO SimpleSequence(testNumber,iVal1,fVal1)
        VALUES(2,'{-3,-2,-1,0,1,2,3}','{11.1,17.7,-23.3,42.5}');
         
    SELECT seq_first_int(iVal1) AS "first_int", seq_last_int(iVal1) AS "last_int",
        seq_first_real(fVal1) AS "first_real", seq_last_real(fVal1) AS "last_real"
    FROM SimpleSequence WHERE testNumber = 2;
     
    first_int 
          last_int        
    first_real      
    last_real
    -------------------------------------------------------------------------
    -3 
         3 
          11.1000003814697        
    42.5
     

As explained in Analytics Functions Library Examples page, the data in the following example is taken from historical values from 2013 for IBM. The data is loaded into xSQL by running various scripts in directory samples/xsql/scripts/financial. To reproduce the example below run the g.bat (or g.sh on Linux systems) specifying the example number 6. For instance, with scripts/financial as the current working directory, simply type:

     
    g 6
     

Following is an example code snippet demonstrating the use of the search()function to extract the trading dates and volumes for January 2013:

         
    -- search
            
    SELECT symbol, seq_search(day, 20130101, 20130131) as Jan_13,
        volume@Jan_13 as "Volume_Jan_13"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol  
    Jan_13{}  
    Volume_Jan_13{}
    -------------------------------------------------------------------------
    SYM0    
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}  
    {335, 808, 45, 402, 732, 48, 805}