Archives of the TeradataForum

Message Posted: Mon, 17 Jan 2011 @ 15:15:29 GMT

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

I'm struggling to get one of my input variables to be used within a Macro, so I'm wondering what I'm doing wrong :

     REPLACE MACRO TNTCALDAYSDIFF_M01(
     IN_DATE DATE,
     IN_WK CHAR
     ) AS
     (
     SELECT
     bla, bla bla from my_Tables
     WHERE T1.DTE_DT <= :IN_DATE
     AND (:IN_WK = TNT_WK OR :IN_WK = 'N/A');
     );

If I run this : EXEC TNTCALDAYSDIFF_M01(IN_DATE = CURRENT_DATE, IN_WK='N/A')

I get zero rows back in all cases, even if I pass through the correct value for the IN_WK of '01'.

Can anyone see if I'm doing anything obviously wrong ?

Also, is there a way of 'seeing' the value that's within the Macro, or is Inserting it into a Temporary Table and then looking at the value in the only way ?

Basically, what I'd love to be able to do is Exec the Macro from within a View and then filter the output of those rows, based upon the View Columns. Does anyone have any clever way of doing this ? (I can't be the only one to want to do it, I'm sure).

Because I can't do this, I'm having to use 'generic reporting' type variables (i.e. AND (:IN_WK = TNT_WK OR :IN_WK = 'N/A')), and they'll end up being 9 of those !

Regards

David Clough
Database Developer
Database Design Group

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