All Forums Database
Amarjot 3 posts Joined 12/10
07 Dec 2010
Using result set in a query

Is there an equivalent to user defined function (MS SQL) in teradata where I can write SQL that returns either a scaler or table which I can further use in a query.

For example:

SELECT sclfunctionName(param1, param2)

OR

SELECT * from tblfunctionName(param1, param2)

Both sclfunctionName and tblfunctionName contain some SQL.

I have tried it using a MACRO but don't know how I can use it in SELECT

CREATE MACRO tstMacro (param1 VARCHAR(10))
AS
(
SELECT fld1, fld2, fldm FROM tbl WHERE fldm = :param1;
-- my query is really long just a small one for example
);

like

SELECT
*
FROM
mytbl t1
INNER JOIN (EXEC tstMacro) AS t2 ON
t1.fld1 = t2.fld1

I have looked at UDF in teradata but believe they can only be written in C or C++.

Any help is greatly appreciated.

Thanks

Amarjot 3 posts Joined 12/10
09 Dec 2010

I am totally new to Teradata so can someone please let me know if something like this is possible in Teradata or not.

Will truly appreciate it.

Thanks

robpaller 159 posts Joined 05/09
10 Dec 2010

Why not use a derived table, volatile/temporary table, or view that you join into your query

Amarjot 3 posts Joined 12/10
22 Dec 2010

Hi Rob,

Thanks for the reply.

I AM using derived tables and have been copying the same SQL in different queries. I was hoping I could put the SQL in macro or a procedure so that I can re-use it. Since we cannot pass parameters to the views to can't use views either.

RobG 2 posts Joined 09/10
11 Jan 2011

I am facing the same issue. Anyone? If it's just not possible, that's a fine (but disappointing) answer.

You must sign in to leave a comment.