Chapter 15 Writing SQL Statements in PowerDesigner

PDM Variables

Many objects have both a code variable and a generated code variable.

The code variable is the attribute code that is defined in the object property sheet.

The generated code variable is computed from the attribute code according to available generation options. The values for the code and generated code variables can be different in the following situations:

When the Code is a reserved word or it contains invalid characters, the generated code is quoted
When the code is longer than authorized by the database, the generated code is truncated

Note:

To access variables of sub-objects (columns in a table for example) you have to use loop macros or GTL macros to browse the list of sub-objects. For example, in a trigger the following macro loops on table columns and for each column with the CannotModify attribute outputs the code of the column followed by "cannot be modified":

.foreach_item(Table.Columns)
.if(%CannotModify%)
-- "%Code%" cannot be modified

.endif
.next

For more information, see "Using macros" in the chapter "Building Triggers and Procedures".

Common variables for all named objects

The following variables can be used for all named object definitions:

Variable name	Comment
@OBJTNAME	Object name
@OBJTCODE	Object code
@OBJTLABL	Object comment
@OBJTDESC	Object description

Common variables for objects

The following variables can be used in all object definitions:

Variable name	Comment
COMMENT	Object comment. If no comment is defined the object name is used
OWNER	Generated code of the object owner, or the object parent
DBPREFIX	Database prefix of object (name of database + '.' if database defined)
QUALIFIER	Whole object qualifier (database prefix + owner prefix)
OPTIONS	SQL text defining physical options for object
CONSTNAME	Object constraint name
CONSTRAINT	Object constraint SQL body. For example(A <= 0) AND (A >= 10)
RULES	Concatenation of server expression for business rule associated with object

Table variables

The following variables can used in a table definition:

Variable name	Comment
TABLE	Generated code of the table
TNAME	Table name
TCODE	Table code
TLABL	Table comment
PKEYCOLUMNS	List of primary key columns. For example: A, B
TABLDEFN	Complete body of the table definition. It contains definition of columns, checks and keys
CLASS	Abstract data type name
CLUSTERCOLUMNS	List of columns used for the cluster

Variables for domain and column checks

The following variables can be used in domain check parameter and column check parameter definitions:

Variable name	Comment
UNIT	Standard check unit attribute
FORMAT	Standard check format attribute
DATATYPE	Data type. For example int, char(10) or numeric(8, 2)
DTTPCODE	Data type code. For example int, char or numeric
LENGTH	Data type length. For example 0, 10 or 8
PREC	Data type precision. For example 0, 0 or 2
ISRDONLY	TRUE if the read-only attribute of standard check is selected
DEFAULT	Default value
MINVAL	Minimum value
MAXVAL	Maximum value
VALUES	List of values. For example(0, 1, 2, 3, 4, 5)
LISTVAL	SQL constraint associated with a list of values. For example C1 in (0, 1, 2, 3, 4, 5)
MINMAX	SQL constraint associated with minimum and maximum values. For example(C1 <= 0) AND (C1 >= 5)
ISMAND	TRUE if the domain or column is mandatory
MAND	Contains the keywords "null" or "not null" depending on if the attribute is mandatory or not mandatory
NULL	Contains keyword "null" if the domain or column is not mandatory
NOTNULL	Contains Keyword "not null" if the domain or column is mandatory
IDENTITY	Keyword "identity" if the domain or column is identity (Sybase specific)
WITHDEFAULT	Keyword "with default" if the domain or column is with default
ISUPPERVAL	TRUE if the upper-case attribute of standard check is selected
ISLOWERVAL	TRUE if the lower-case attribute of standard check is selected

Column variables

The following variables can be used in a column definition:

Variable name	Comment
COLUMN	Generated code of the column
COLNNO	Position of Column in List of columns of Table
COLNNAME	Column name
COLNCODE	Column code
PRIMARY	Contains Keyword "primary" if the column is a primary key column
ISPKEY	TRUE if the column is part of a primary key
FOREIGN	TRUE if the column is part of a foreign key
COMPUTE	Compute constraint text

Abstract data type variables

The following variables can be used in an abstract data type definition:

Variable name	Comment
ADT	Generated code of the abstract data type
TYPE	Type of Abstract data type. For example "array", or "list"
SIZE	Abstract data type size
ISARRAY	TRUE if the abstract data type is of type array
ISLIST	TRUE if the abstract data type is of type list
ISSTRUCT	TRUE if the abstract data type is of type structure
ISOBJECT	TRUE if the abstract data type is of type object
ISJAVA	TRUE if the abstract data type is of type JAVA class
ADTDEF	Contains definition of the abstract data type

Abstract data type attribute variables

The following variable can be used in an abstract data type attribute definition:

Variable name	Comment
ADTATTR	Generated code of an abstract data type attribute

Domain variables

The following variable can be used in a domain definition:

Variable name	Comment
DOMAIN	Generated code of a domain (also available for columns)

Business rule variables

The following variables can be used in a business rule definition:

Variable name	Comment
RULE	Generated code of a business rule
RULENAME	Rule name
RULECODE	Rule code
RULECEXPR	Rule client expression
RULESEXPR	Rule server expression

Index variables

The following variables can be used in an index definition:

Variable name	Comment
TABLE	Generated code of the parent of an index, can be a table or a query table (view)
INDEX	Generated code of the index
INDEXNAME	Index name
INDEXCODE	Index code
UNIQUE	Contains keyword "unique" when an index is unique
INDEXTYPE	Contains the index type (DBMS dependant)
CIDXLIST	List of index columns. For example A asc, B desc, C asc
INDEXKEY	Contains keywords "primary", "unique" or "foreign" depending on an index origin
CLUSTER	Contains keyword "cluster" when an index is a clustered index
INDXDEFN	Used for defining an index within a table definition

Index column variables

The following variables can be used in index column definitions:

Variable name	Comment
ASC	Contains keywords "ASC" or "DESC" depending on the sort order
ISASC	TRUE if the index column sort is ascending

Reference variables

The following variables can be used in a reference definition:

Variable name	Comment
REFR	Generated code of the reference
PARENT	Generated code of the parent table
PNAME	Parent table name
PCODE	Parent table name
PQUALIFIER	Parent table qualifier. See also QUALIFIER
CHILD	Generated code of a child table
CNAME	Child table name
CCODE	Child table code
CQUALIFIER	Child table qualifier. See also QUALIFIER
REFRNAME	Reference name
REFRCODE	Reference code
FKCONSTRAINT	Foreign key (reference) constraint name
PKCONSTRAINT	Constraint name of the primary key used to reference the object
CKEYCOLUMNS	List of parent key columns. For example: C1, C2, C3
FKEYCOLUMNS	List of child foreign key columns. For example: C1, C2, C3
UPDCONST	Update declarative constraint. This can be any of the following keywords Restrict Cascade Set null Set default
DELCONST	Delete declarative constraint. This can be any of the following keywords Restrict Cascade Set null Set default
MINCARD	Minimum cardinality
MAXCARD	Maximum cardinality
POWNER	Parent table owner name
COWNER	Child table owner name
CHCKONCMMT	TRUE when check on commit is selected on a reference (ASA 6.0 specific)
JOINS	Reference joins
REFRNO	Reference number in the child table collection of references

Reference column variables

The following variables can be used in reference column definitions:

Variable name	Comment
CKEYCOLUMN	Generated code of the parent table column (primary key)
FKEYCOLUMN	Generated code of a child table column (foreign key)
PK	Generated code of a primary key column
PKNAME	Primary key column name
FK	Generated code of a foreign key column
FKNAME	Foreign key column name
AK	Alternate key column code (same as PK)
AKNAME	Alternate key column name (same as PKNAME)
COLTYPE	Primary key column data type
DEFAULT	Foreign key column default value
HOSTCOLTYPE	Primary key column data type used in procedure declaration. For example: without length

Key variables

The following variables can be used in a key definition:

Variable name	Comment
COLUMNS COLNLIST	List of key columns. For example"A, B, C"
ISPKEY	TRUE when a key is the primary key for the table
PKEY	Primary key constraint name
AKEY	Alternate key constraint name
KEY	Key constraint name
ISMULTICOLN	True if the key has more than one column
CLUSTER	Cluster keyword

Variables for views

The following variables can be used in a view definition:

Variable name	Comment
VIEW	Generated code of the view
VIEWNAME	View name
VIEWCODE	View code
VIEWCOLN	List of columns of a view. For example: "A, B, C"
SQL	SQL text of a view. For example Select * from T1
VIEWCHECK	Contains the keyword "with check option" if this option is selected in the view property sheet
SCRIPT	Complete view creation order. For example create view V1 as select * from T1

Trigger variables

The variables listed below can be used in a trigger definition. You can also use owning table variables in a trigger definition.

Variable name	Comment
ORDER	Order number of Trigger (where the current DBMS supports more than one trigger of one type)
TRIGGER	Generated code of the trigger
REFNO	Reference order number in the list of references for the table
ERRNO	Error number for standard error
ERRMSG	Error message for standard error
MSGTAB	Name of a table containing user-defined error messages
MSGNO	Name of a column containing error numbers in a user-defined error table
MSGTXT	Name of a column containing error messages in a user-defined error table
SCRIPT	SQL script of trigger or procedure.
TRGBODY	Trigger body (only for Oracle ODBC reverse engineering)
TRGDESC	Trigger description (only for Oracle ODBC reverse engineering)
TRGDEFN	Trigger definition

Database, trigger, and procedure generation variables

The following variables can be used for database, procedure, and trigger generation:

Variable name	Comment
DATE	Generation date & time
USER	Login name of the user executing the generation
PATHSCRIPT	Path where the file script will be generated
NAMESCRIPT	Name of the file script where SQL orders will be written
STARTCMD	Description explaining how to execute a generated script
ISUPPER	TRUE if upper case generation option is set
ISLOWER	TRUE if lower case generation option is set
DBMSNAME	Name of the DBMS associated with the generated model
DATABASE	Code of the database associated with the generated model

Reverse engineering variables

The following variables can be used when reverse engineering a database into a PDM:

Variable name	Comment
R	Set to TRUE during reverse engineering
S	Allows to skip a word. The string is parsed for reverse engineering but is not generated
D	Allows to skip a numeric value. The numeric value is parsed for reverse engineering but is not generated
A	Allows to skip all text. The text is parsed for reverse engineering but is not generated
ISODBCUSER	True if the current user is the connected user
CATALOG	Catalog name that will be used in ODBC reverse engineering queries
SCHEMA	Schema that will be used in ODBC reverse engineering queries
SIZE	Data type size of a column or a domain. Used for ODBC reverse engineering, when a data type length is not defined in the system tables
VALUE	One value from the list of values in a column or domain
TRGTYPE	Variable used in the Create order of a trigger. Trigger type uses keywords for each trigger type, for example "BeforeInsert", or "AfterUpdate"
TRGEVENT	Variable used in the Create order of a trigger. Trigger event uses keywords for each trigger event, for example" Insert", "Update", and "Delete"
TRGTIME	Variable used in the Create order of a trigger. Timing of trigger uses the keywords "Null", "Before", and "After"

Database synchronization variables

The following variables can be used for database generation when synchronizing a modified PDM with an existing database:

Variable name	Comment
OLDOWNER	Old owner name of the object. See also OWNER
NEWOWNER	New owner name of the object. See also OWNER
OLDQUALIFIER	Old qualifier of the object. See also QUALIFIER
NEWQUALIFIER	New qualifier for the object. See also QUALIFIER
OLDTABL	Old code of the table
NEWTABL	New code of the table
OLDCOLN	Old code of the column
NEWCOLN	New code of the column

Database security variables

The following database security variables are available:

Variable name	Comment
PRIVLIST	List of privileges for a grant/revoke order
PERMLIST	List of permissions for a grant/revoke order
GRANTEE	Name of the user, group, or role for a grant/revoke order
ID	Name of the user
GROUP	Name of the group
ROLE	Name of the role
OBJECT	Database objects (table, view, column, and so on)
PERMISSION	SQL grant/revoke order for a database object
PRIVILEGE	SQL grant/revoke order for an ID (user, group, or role)
GRANTOPTION	Option for grant: with grant option / with admin option
REVOKEOPTION	Option for revoke: with cascade

Metadata variables

The following metadata variables are available:

Variable name	Comment
@CLSSNAME	Localized name for an object class. For example: Table, View, Column, Index
@CLSSCODE	Object class code. For example: TABL, VIEW, COLN, INDX

DBMS, database options variables

The following DBMS and database options variables are available:

Variable name	Comment
TABLESPACE	Generated code of a tablespace
STORAGE	Generated code of a storage

Variables for ASE & SQL Server

The following DBMS specific variables are available for Sybase Adaptive Server Anywhere and Microsoft SQL Server:

Variable name	Comment
RULENAME	Name of a business rule object associated with a domain
DEFAULTNAME	Name of a default object associated with a domain
USE_SP_PKEY	Use sp_primary key to create primary keys (SQL Server)
USE_SP_FKEY	Use sp_foreign key to create foreign keys (SQL Server)

Sequence variable

The following variable can be used in a sequence definition:

Variable name	Comment
SQNC	Name of sequence

Procedure variables

The following variables can be used in a procedure definition:

Variable name	Comment
PROC	Generated code of a procedure (also available for trigger when a trigger is implemented with a procedure)
FUNC	Generated code of a procedure where the procedure is a function (with a return value)

Join index variables (IQ)

The following variables can be used in a join index definition:

Variable name	Comment
JIDX	Generated code for join index
JIDXDEFN	Complete body of join index definition
REFRLIST	List of references (for ODBC)
RFJNLIST	List of reference joins (for ODBC)