Archives of the TeradataForum

Message Posted: Tue, 18 Jan 2011 @ 19:56:11 GMT

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

At the risk of some wooly thinking on my part, I'll mention some areas where I've wondered whether more flexibility would have been good...

i) "... Manage complex code, logic or transformations within the DB, rather than in an external tool/code base." Yes, we've certainly done this, but with mixed success. After advising to one developer that imbedding multiple pieces of SQL in a Macro would always work better than in a Stored Procedure, I somewhat ended up with egg on my face. In this particular case there were multiple steps, with one step writing intermediate results to a work Table, only for the next step to use it.

The problem with the Macro approach was that gathering Stats wasn't possible, whereas it was with the Stored Procedure. Hence, the Stored Procedure (whilst not having much - if any - procedural code) did, overall, work faster (in fact, the Macro went over with Spool space issues!)

Perhaps we should have done the whole thing in bteq, but there you are.

ii) Tactical Queries : yes, works well, although even here the odd IF ELSE statement would be very useful.

iii) Incorporate within a View : (Here's where the wooly thinking might really kick in, wrt to right tool for the right job !). Given that a Macro returns a result set, it would be nice to be able to do that which is available in Oracle (albeit from a Stored Procedure), that being to SELECT from a Macro. Actually, my analogy with Oracle suggests that the deficiency is not with Macros but with Stored Procedures.

It would be nice to be able to do this :

     SELECT * FROM MY_MACRO (IN_DATE = CURRENT_DATE)
     WHERE COL1 BETWEEN 1 AND 9
     ORDER BY MY_COL;

Anyway, just some thoughts.

Regards

David Clough
Database Developer
Database Design Group

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