Using result set in a query

Database

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
4 REPLIES

Re: Using result set in a query

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

Re: Using result set in a query

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

Re: Using result set in a query

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.
N/A

Re: Using result set in a query

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