Macros to get non-PK columns in PowerDesigner

[Former Member]

I'm trying to write a user defined template to create a trigger on a table where the SQL is based on all the columns not in the primary key.

I have the functions

.PKCOLN

is there someway I can get the set of columns that aren't in the Primary Key ?

[marc_ledier · Accepted solution]

You can refer to .ALLCOL macro and filter the PK columns

Ex.

<<

here are the non-pk columns

.ALLCOL("[%ISPKEY%?:do something with non-pk %COLUMN% (%COLTYPE%)\n]\")

here are the pk columns

.PKCOLN("do something with PK %COLUMN% (%COLTYPE%)")

>>

You can also use the .FOREACH_COLUMN macro

ex.

.FOREACH_COLUMN()

[%ISPKEY%?:do something with %COLUMN% (%COLTYPE%)\n]\

.ENDFOR

Notice the [...\n]\

First \n add a new line at the end of the group whereas the last \ instruct to continue on the same line.

Therefore, if the content of variables is empty, the \n is not added and you don't have a new line for filtered rows.

You can have more details on doc

Customizing and Extending PowerDesigner

  > DBMS Definition Files 

     > PDM Variables and Macros

Marc

[marc_ledier · Accepted solution]

You can also use GTL with :

.foreach_item(Table.Columns,"\nhead\n","\ntail\n",%Primary%==false)

do something with non-pk %COLUMN% (%COLTYPE%)

.next(",\n")

Marc

[Former Member · Accepted solution]

That's excellent.

Only just started with PowerDesigner... so haven't seen this construct

[%ISPKEY%?:%COLUMN%]

Doesn't show up in the % drop down list or in the help.

Could you point me at a link.

Thanks

Mike

[Former Member · Accepted solution]

Sorry - one more question...

I'm trying to use this in a where clause so need an "and" between each clause.

I'm trying this but have an "And" too many

where  .ALLCOL("[%ISPKEY%?:(ins.%COLUMN% is null and history.%COLUMN% is null) and\n]\", "", "", "")

I can't insert "and" into any of the other parameters as they get printed for every column

[marc_ledier · Accepted solution]

For complex syntax, use GTL instead of macro.

here;

.foreach_item(Table.Columns,"where\n","\n",%Primary%==false)

   (ins.%COLUMN% is null and history.%COLUMN% is null)

.next(" and\n")

Help for conditional syntax is closed to the previous one

Customizing and Extending PowerDesigner

  > DBMS Definition Files

    > PDM Variables and Macros

      > Testing Variable Values with the [ ] Operators

Marc

[Former Member · Accepted solution]

Thanks again - excellent.

I have the GTL form working ... in fact I prefer the GTL so will convert all the template to GTL,

Am still using ErWin and PowerDesign but now looking like PowerDesigner is the way to go for us.

Thanks