All Forums Database
Agrias 6 posts Joined 06/11
18 Jul 2012
Stored Procedures - Need help with massaging data within an input parameter

Hello everyone,

I've got a question regarding what I can do with the data inside of a parameter passed to a stored procedure.  The procedure would start out like this:

 

REPLACE PROCEDURE DBASEWORK.PRC_MYPROC(

pPARAMETER1 VARCHAR(10000)

)

CREATE MULTISET VOLATILE TABLE TEMPVAL, NO LOG

(PARAMETERNAME VARCHAR(50))

UNIQUE PRIMARY INDEX (PARAMETERNAME)

ON COMMIT PRESERVE ROWS;

 

 

Let's say that I want to pass procedure a parameter that looks like this:

CALL DBASEWORK.PRC_MYPROC('VALUE1, VALUE2, VALUE3');

Is there a way for me to take this value and split it up into 3 separate rows in my TEMPVAL table?  So essentially it would be split up into 3 rows in this table;

ROW1: VALUE1

ROW2: VALUE2

ROW3: VALUE3

This wouldn't be 3 values every time, but rather it could be a dynamic amount.  Could have ('VALUE1, VALUE2') or ('VALUE1, VALUE2, VALUE3, VALUE4..., VALUEX).  Realistically, the most values that would be within this input parameter would be around 10, so it wouldn't be doing any serious processing. 

 

Any ideas would be extremely helpful, please reply if I can help be more clear. 

Qaisar Kiani 337 posts Joined 11/05
18 Jul 2012

As PL/SQL is allowed in Stored Procedure, so you can take the input parameter and by doing the string operations to split the results and insert them in the table.

You will need to use the INDEX/POSITION function to find the position of comma and then do a SUBSTRING operation to get the required results...

Agrias 6 posts Joined 06/11
18 Jul 2012

I guess I don't know what code to use to loop through the parameter.  I would use SUBSTRING and POSITION to figure out where the first value ends and the 2nd starts, and insert that value into the TEMP table. 

But then I'm not sure how I would loop back and do the process again, with the parameter now starting with the 2nd value that was initially passed. 

If the parameter pased was ('VALUE1, VALUE2, VALUE3) I guess logically it would flow like this:

1) do the first SUBSTRING statement and insert the row into the temp table:

2) Set a declared environment variable to equal the entire parameter, minus the first value that was just substring'd off

3) repeat process using the new environment variable

4) stop once the environment variable is empty. 

If anyone could give an example of what this loop would like, it would help me out a ton. 

dnoeth 4628 posts Joined 11/04
18 Jul 2012

This is an example from my trainings, it's no loop, but recursion, yet it's the same logic:

/*** How to split a comma delimited list of values ***/

DROP TABLE Strings;

CREATE TABLE Strings
(
  groupcol INT NOT NULL,
  string VARCHAR(991) NOT NULL
);

/*** Some comma delimited strings ***/
INSERT INTO Strings VALUES (1,'a,b,c,d,e');
INSERT INTO Strings VALUES (2,'a,bb,ccc,dddd,eeeee');

/*** Always be prepared for exceptional values... ***/
INSERT INTO Strings VALUES (3,'a,,bb');
INSERT INTO Strings VALUES (4,',a');
INSERT INTO Strings VALUES (5,'a,');
INSERT INTO Strings VALUES (6,',');
INSERT INTO Strings VALUES (7,'');

collect statistics on strings column groupcol;

/***
  Recursive SQL
  CAUTION, this query might need a lot of spool, because the string (or the rest of it)
  is used for each new level,
  e.g. 1000 chars, 100 values/result rows --> ~(1000*100/2) = 50000 chars in spool, i.e. 50x the size of the original column
***/
WITH RECURSIVE cte
 (groupcol,
  --string,
  len,
  remaining,
  word,
  pos
 ) AS (
  SELECT
    GroupCol,
--    String,
    POSITION(',' IN String || ',') - 1 AS len,
    SUBSTRING(String || ',' FROM len + 2) AS remaining,
    SUBSTRING(String FROM 1 FOR len) AS word,
    1
  FROM strings
  UNION ALL
  SELECT
    GroupCol,
--    String,
    POSITION(',' IN remaining)- 1 AS len_new,
    SUBSTRING(remaining FROM len_new + 2),
    SUBSTRING(remaining FROM 1 FOR len_new),
    pos + 1
  FROM cte
  WHERE remaining <> ''
 )
SELECT
  groupcol,
  pos,
  word
FROM cte
--WHERE word <> ''  /** to exclude empty strings **/
ORDER BY
  groupcol, pos;
 
/***
  Recursive SQL
  This version has to join back to the strings table for each level, but spool usage is less.
***/
WITH RECURSIVE cte
 (groupcol, start_pos, len) AS
 (
  SELECT
    GroupCol,
    1 (INT) AS start_pos,
    POSITION(',' IN String || ',') - 1 AS len
  FROM strings
  UNION ALL
  SELECT
    cte.GroupCol,
    cte.start_pos + len + 1 AS next_start,
    POSITION(',' IN SUBSTRING(s.string FROM next_start) || ',')- 1 AS len_new
  FROM cte JOIN strings s
  ON cte.groupcol = s.groupcol
  WHERE next_start <= CHAR_LENGTH(s.string)
 )
SELECT
  cte.*,
  SUBSTRING(s.string FROM start_pos FOR len) AS word
FROM cte JOIN strings s
  ON cte.groupcol = s.groupcol
--WHERE word <> ''  /** to exclude empty strings **/
ORDER BY cte.GroupCol, start_pos
;

You can Insert/Select the result into TEMPVAL.

Caution: Support of RECURSIVE was added (afair) in TD13, before you have to stick to a WHILE loop.

Btw, the easiest solution would involve a table UDF to split the data into rows (there's one builtin in TD14, i can't remember it's name).

Dieter

Dieter

Agrias 6 posts Joined 06/11
19 Jul 2012

Dieter - You are a gentlemen and a scholar!  That was exactly what I needed and it worked beautifully.  Thank you very much for your kindness.

You must sign in to leave a comment.