Archives of the TeradataForum

Message Posted: Thu, 12 Dec 2013 @ 13:45:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

As Dieter has described, T14 now has this rather lovely function called Split_To_Table.

We are now using this in conjunction with a Stored Procedure, pretty much how you want to do it yourself, I suspect.

My Procedure accepts ValuePairs (which are Country Code, Account Nr pair values), but it wouldn't really be much different.

The splitting is actually very easy :

     SELECT d.tokennum, d.AccountNumber
           FROM TABLE
     (strtok_split_to_table(null,'000012345,000067891,000034345',',')
                RETURNS (outkey INTEGER,
                         tokennum INTEGER,
                         AccountNumber VARCHAR(20) CHARACTER SET UNICODE)) as d

     order by
     --d.tokennum
     d.AccountNumber


     1        000012345
     3        000034345
     2        000067891

What you may be missing is the method of returning the Result set from a Stored Procedure - again, very easy :

     REPLACE PROCEDURE TESTCOMMERCIAL_X.CO2_ACCNT_SUMMARY_ALLDUPS_P01
     (
      IN nP_YearNr      SMALLINT,
      IN sP_ValuePairTx VARCHAR(4096) CHARACTER SET LATIN, -- format 'XX,999;XX,999'
     --
     OUT nP_ResultCode SMALLINT,
     OUT sP_ResultMsg  VARCHAR(512),
     )
       DYNAMIC RESULT SETS 1
       ... lots of code ... then ...
     RETURN_RESULTSET : BEGIN
         BEGIN DECLARE AccntShipment_Csr1 CURSOR WITH RETURN ONLY FOR
         SEL * FROM WHATEVER_YOUR_TABLE_IS;
      OPEN AccntShipment_Csr1;
      END; -- of Cursor Declare
     END RETURN_RESULTSET;

You may want to Insert into a Volatile Table your Split To Tables, then join that VT into your main Query ... will work a treat !

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>