The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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...

asked 30 Nov '16, 11:30

Rolle's gravatar image

Rolle
345182339
accept rate: 0%

edited 30 Nov '16, 14:22


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 
permanent link

answered 30 Nov '16, 14:41

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

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
permanent link

answered 01 Dec '16, 04:47

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 01 Dec '16, 04:49

It’s a good idea to have a mini POS that sit on top of SQL Account, simple setup because almost everything is actually SQL Account based. Use SQL Account stock list, and can real time link or batch link. The POS is can consider fully integrated with SQL Account. I don't have much idea about this, but I think above to users given a perfect answer for you. I hope it is helpful for you to solve your problem.

Dave Hall Writer

permanent link

answered 01 Dec '16, 05:40

davehall's gravatar image

davehall
5
accept rate: 0%

Comment Text Removed
2

It’s a good idea ...

It’s also a good idea to try to answer the according question:)

(01 Dec '16, 08:05) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×71

question asked: 30 Nov '16, 11:30

question was seen: 189 times

last updated: 01 Dec '16, 08:05