Archives of the TeradataForum

Message Posted: Fri, 01 Jul 2011 @ 13:24:48 GMT

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

Hi Guys,

I need to build up a dynamic SQL Statement, which included a multi-Column Join statement.

The actual Columns themselves will be derived from a Control Table Column and the data in that Column will look something like this string :

'KEY_COL1, KEY_COL2, KEY_COL3'

Having read that string into a variable within a Stored Procedure, I then want to weave it into my dynamic SQL statement, which will need to end up looking something like :

       'INS INTO ' || TRIM(ARCH_DB) ||'.'|| TRIM(vFor.C_TAB) ||'_'||TRIM(AR_YR)
     || ' SEL CA1.* FROM ' || TRIM(vFor.C_DB_NM) ||'.'|| TRIM(vFor.C_TAB) || '
     CA1 INNER JOIN ' || TRIM(ARCH_DB)||'.'||
       TRIM(P_TAB)  ||'_'||TRIM(AR_YR)  || ' PA1 ON CA1.' || TRIM(UPI_NM) || ' =
     PA1.' || TRIM(UPI_NM) ||';';

       'INS INTO ' || TRIM(ARCH_DB) ||'.'|| TRIM(vFor.C_TAB) ||'_'||TRIM(AR_YR)
     || ' SEL CA1.* FROM ' || TRIM(vFor.C_DB_NM) ||'.'|| TRIM(vFor.C_TAB) || '
     CA1 INNER JOIN ' || TRIM(ARCH_DB)||'.'||
       TRIM(P_TAB)  ||'_'||TRIM(AR_YR)  || ' PA1
     ON CA1.KEY_COL1 = PA1.KEY_COL1
     AND CA1.KEY_COL2 = PA1.KEY_COL2
     AND CA1.KEY_COL3 = PA1.KEY_COL3
     ;';

Now, I was thinking of calling another Proc which would programmatically read through the supplied string, and for each Column Name found, write it out to a Global Temporary Table (GTT)

The calling Proc would then declare a Cursor on this result set and systematically build up the Dynamic Sql statement.

But then I thought, I wonder whether a Procedure can read through the result set of another Procedure which had been declared as DYNAMIC RESULT SETS 1 and let that Proc do the GTT processing.

Is this possible ?

Short of doing a UDF, is there a very neat way of doing this, or my initial thoughts good enough ?

Regards

David Clough
Senior Database Designer

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