Each column of a query result set is associated with a variable. A script header specifies the association between the columns of the result set and the variable. The values of the returned records are stored in these variables, which are then committed as object attribute values.
The script header is contained within curly brackets { }. The variables are listed within the brackets, each variable separated by a comma. There is a matching column for each variable in the Select statement that follows the header.
For example:
{OWNER, @OBJTCODE, SCRIPT, @OBJTLABL}
SELECT U.USER_NAME, P.PROC_NAME, P.PROC_DEFN, P.REMARKS
FROM SYSUSERPERMS U,SYSPROCEDURE P
WHERE [%SCHEMA% ? U.USER_NAME='%SCHEMA%' AND] P.CREATOR=U.USER_ID
ORDER BY U.USER_NAME
The variables can be any listed in PDM Variables.
Each comma-separated part of the header is associated with the following information:
Name of variable (mandatory). See the example in Processing with variable names
The ID keyword follows each variable name. ID means that the variable is part of the identifier
The ... (ellipsis) keyword means that the variable must be concatenated for all the lines returned by the SQL query and having the same values for the ID columns
Retrieved_value = PD.value lists the association between a retrieved value and a PowerDesigner value. A conversion table converts each value of the record (system table) to another value ( in PowerDesigner). This mechanism is optionally used. See the example in Processing with conversion table
The only mandatory information is the variable name. All others are optional. The ID and ... (ellipsis) keywords are mutually exclusive.
{TABLE ID, ISPKEY ID, CONSTNAME ID, COLUMNS ...}
select
t.table_name,
1,
null,
c.column_name + ', ',
c.column_id
from
systable t,
syscolumn c
where
etc..
In this script, the identifier is defined as TABLE + ISKEY+ CONSTNAME.
In the result lines returned by the SQL script, the values of the fourth field is concatenated in the COLUMNS field as long as these ID values are identical.
SQL Result set Table1,1,null,'col1,' Table1,1,null,'col2,' Table1,1,null,'col3,' Table2,1,null,'col4,' In PowerDesigner memory Table1,1,null,'col1,col2,col3' Table2,1,null,'col4'
In the example, COLUMNS will contain the list of columns separated by commas. PowerDesigner will process the contents of COLUMNS field to remove the last comma.
The syntax inserted just behind a field inside the header is:
(SQL value1 = PowerDesigner value1, SQL value2 = PowerDesigner value2, * = PowerDesigner value3)
where * means all other values.
For example:
{ADT, OWNER, TYPE(25=JAVA , 26=JAVA)}
SELECT t.type_name, u.user_name, t.domain_id
FROM sysusertype t, sysuserperms u
WHERE [u.user_name = '%SCHEMA%' AND]
(domain_id = 25 OR domain_id = 26) AND
t.creator = u.user_id
In this example, when the SQL query returns the value 25 or 26, it is replaced by JAVA in TYPE variable.