I would like to create an alias to a multimember file 'dynamically'. I want to have a string passed as member in the format of yyyymm (for example 200407). I tried the following:
CREATE ALIAS XXXGL.TEST
(SELECT SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS
CHAR(20)),1,4) || SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS
CHAR(20)),6,2) from LIBB.FILEB)
Unfortunately, it doesn?t work. Do you have workarounds?
To dynamically create an alias you need to prepare a CREATE ALIAS statement. Also, a member name cannot start with a numeric character. Here's an example using an SQL procedure that generates an alias dynamically for a generated member name that starts with 'M'
CREATE PROCEDURE aliastest() LANGUAGE SQL BEGIN DECLARE stmt1 VARCHAR(256); DECLARE mbrname VARCHAR(10); SET mbrname = (SELECT SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS CHAR(20)),1,4) || SUBSTR(CAST(DATE(DAYS(CURDATE()) -15) AS CHAR(20)),6,2) from sysibm.sysdummy1); SET stmt1 = 'CREATE ALIAS XXXGL.TEST FOR LIBA.FILEA(M'|| TRIM(TRAILING FROM mbrname) ||')'; PREPARE s1 FROM stmt1; EXECUTE s1; END; CALL ALIASTEST();
MORE INFORMATION ON THIS TOPIC
Check out this Search400.com Featured Topic: Database issues resolved
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: Tips, tutorials and more.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading