Archives of the TeradataForum
Message Posted: Thu, 12 Dec 2013 @ 13:45:45 GMT
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 !
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|