SAP SQL Anywhere

Question

I have tre columns i a SQL with names Material, pos, and value. Pos is a comma seperatde and value also the same.

Based column pos and column value, I want to create a field in my SQL. Pos contain comma separated values indicating from which pos column value should have. The new column will contain 18 numerical values.

Ex:

Material-----Pos------Value
Test123------1,10 ----002,001

I need a third column that should look like this: 002000000001000000

Value 002 starting on pos 1. It is filled with zeros to the value of 001 that starts at pos 10. Then it is filled with zeros until it becomes 18 characters.

Pos and value have always just as many kommasepaerade värdern and it can sometimes be more or less values.

Ex:

Material-----Pos------Value
Test123------1,10,15--002,001,003

It should look like this: 002000000001000030

How do I do this in the best way? I guess I should use function INSERTSTR or STUFF on some way...

Accepted Answer

The sa_split_list() procedure and STUFF function may help; here is a demonstration...

BEGIN
DECLARE @pos     LONG VARCHAR;
DECLARE @value   LONG VARCHAR;
DECLARE @result  VARCHAR ( 18 );

SET @pos    = '1,10';
SET @value  = '002,001';
SET @result = REPEAT ( '0', 18 );

FOR f_fetch1 AS c_fetch1 INSENSITIVE CURSOR FOR
SELECT pos_entry.line_num    AS @entry_number,
       pos_entry.row_value   AS @entry_pos,
       value_entry.row_value AS @entry_value
  FROM ( SELECT line_num,
                row_value
           FROM sa_split_list ( @pos ) ) AS pos_entry
       INNER JOIN
       ( SELECT line_num,
                row_value
           FROM sa_split_list ( @value ) ) AS value_entry
       ON pos_entry.line_num = value_entry.line_num
 ORDER BY @entry_number 
FOR READ ONLY
DO
   SET @result = STUFF ( @result, @entry_pos, LENGTH ( @entry_value ), @entry_value );
END FOR;

SELECT @result;

SET @pos    = '1,10,15';
SET @value  = '002,001,003';
SET @result = REPEAT ( '0', 18 );

FOR f_fetch2 AS c_fetch2 INSENSITIVE CURSOR FOR
SELECT pos_entry.line_num    AS @entry_number,
       pos_entry.row_value   AS @entry_pos,
       value_entry.row_value AS @entry_value
  FROM ( SELECT line_num,
                row_value
           FROM sa_split_list ( @pos ) ) AS pos_entry
       INNER JOIN
       ( SELECT line_num,
                row_value
           FROM sa_split_list ( @value ) ) AS value_entry
       ON pos_entry.line_num = value_entry.line_num
 ORDER BY @entry_number 
FOR READ ONLY
DO
   SET @result = STUFF ( @result, @entry_pos, LENGTH ( @entry_value ), @entry_value );
END FOR;

SELECT @result;

END;

@result            
------------------ 
002000000001000000 


@result            
------------------ 
002000000001000030

Answer 2

Here's a variation of Breck's solution that does not require a loop - instead it uses

an OLAP window to get the position of the next entry (or the "first positon after the max length" when there's no further entry, here 18 + 1 = 19)
two further calculated values @entry_padding_length with the number of zeroes to add and @padded_entry with exactly that
and the LIST aggregate to concatenate the padded entries (here explicitly with no delimiter, LIST would use a comma by default.

-- sample with Breck's second test data
BEGIN
DECLARE @pos     LONG VARCHAR;
DECLARE @value   LONG VARCHAR;
DECLARE @result  VARCHAR ( 18 );

SET @pos    = '1,10,15';
SET @value  = '002,001,003';
SET @result = '';


SELECT LIST( @padded_entry , '' )
   INTO @RESULT
FROM
   (SELECT DT1.*,
       -- calculate the position of the next entry (or MAX LENGTH + 1 for the last entry)
       ISNULL( FIRST_VALUE( @entry_pos )
             OVER ( ORDER BY @entry_number ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ), 19 )
          AS @next_entry_pos,
       -- calculate the length of the padding needed for the current entry
       @next_entry_pos - @entry_pos - LENGTH( @entry_value ) AS @entry_padding_length,
       @entry_value || REPEAT( '0', @entry_padding_length ) AS @padded_entry
    FROM
       (SELECT pos_entry.line_num    AS @entry_number,
           CAST(pos_entry.row_value AS INT)  AS @entry_pos,
           value_entry.row_value AS @entry_value
        FROM ( SELECT line_num, row_value
               FROM sa_split_list ( @pos ) ) AS pos_entry
           INNER JOIN
           ( SELECT line_num, row_value
               FROM sa_split_list ( @value ) ) AS value_entry
           ON pos_entry.line_num = value_entry.line_num
        ORDER BY @entry_number) DT1
   ) DT2
   ORDER BY 1;

SELECT @result;
END;

returns

'002000000001000030'

FWIW, here's the result of the derived table DT2 (i.e. before the LIST aggregate is used):

@entry_number,@entry_pos,@entry_value,@next_entry_pos,@entry_padding_length,@padded_entry
1,1,002,10,6,002000000
2,10,001,15,2,00100
3,15,003,19,1,0030

SAP SQL Anywhere

SQL with POS?

Follow this question

Related questions