Q
Problem solve Get help with specific problems with your technologies, process and projects.

How to summarize an alias reference outside of SQL views and MQTs

Apply a user-defined table function to summarize an alias reference and reference the UDTF on a SELECT statement.

Is there any way to select individual file members within SQL/400 views and materialized query tables (MQT)? Currently aliases cannot be used as part of a view.
No, alias references are not allowed in SQL views and MQTs.
One way to encapsulate an alias reference would to be use a user-defined table function (UDTF):
CREATE FUNCTION aliasudtf() 
RETURNS  TABLE(col2 char(6), col3  int) 
LANGUAGE SQL 
NO EXTERNAL ACTION 
DETERMINISTIC 
DISALLOW PARALLEL 
BEGIN 
Return ( 
select c2,c3 from alias1 
 union all 
select c2,c3 from alias2); 
END; 
Here's an example of how to reference a UDTF on a SELECT statement. UDTF references are allowed when creating SQL views and materialized query tables (MQTs).
SELECT col2 FROM TABLE( aliasudtf() )  x; 

Dig Deeper on iSeries database management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close