Archives of the TeradataForum

Message Posted: Thu, 29 Jul 2004 @ 13:09:57 GMT

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

<-- Anonymously Posted: Thursday, July 29, 2004 09:08 -->

I was wondering, does anyone know if it is possible to treat a parameter in a Macro as an 'object'? I don't think it is possible, but thought to check here first. Please see the example below.

I'm trying to get a distinct list of values for a column, based on the column name passed to the Macro. The table I'm selecting from is subject to change often (add columns/remove columns), therefore the current CASE approach shown below is not viable. The number of columns in the table on average is 45. The table contains a few million rows.

Unfortunately my solution has to be a macro. I'm trying not to use Dynamic SQL in a Stored Procedure, since that may involve multiple steps, which isn't something that would work for my scenario.

     REPLACE MACRO MyMacro (colname char(30)) AS (
     SELECT
           CASE
                 WHEN :colname = 'CHECK_ID'      THEN CHECK_ID
                 WHEN :colname = 'BIRTH_DATE'    THEN BIRTH_DATE
                 WHEN :colname = 'OPEN_DATE'     THEN OPEN_DATE
                 etc..
            END
     FROM
           mydb.mytable
     GROUP BY 1;)     ;

Need to do something like this, but it doesn't work.

     REPLACE MACRO MyMacro (colname char(30)) AS (
     SELECT
           ":colname"
     FROM
           mydb.mytable
     GROUP BY 1;);

Any ideas?

Thanks!

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