ProcessGroupSQL

The ProcessGroupSQL or BusinessProcessGroup pattern works like the normal Plex ProcessGroup and should be used to process a group of records.

Unlike the normal ProcessGroup it uses dynamic SQL for record selection and inherits form the BusinessFunctionShell function (so it has logging, default parameters...).

To use it you should

Contents 1 Short usage guide 2 Main edit points 2.1 Process row 2.2 Call FindBrowse.GenSelectFromStatement 2.3 AlterSQLStatement 3 DynamicSQL

Short usage guide

1. Inherit from SEC_V20/SEC BusinessProcessGroup or from your own inherited pattern. Because it uses DB access your function should be placed beneath a view.
2. Drag a view to the Local/DataForFetch variable. Your data will be there and not in the view!!!!
3. Goto Pre Point Subroutines
4. Goto Sub Call FindBrowse.GenSelectFromStatement and call the GenSelectStatement function from your entity
5. Goto Sub Prosee Row and build your logic,
6. Put some fields to the Restrict input or the local OrdrerBy variable if you need to

Main edit points

Process row

Process row in the Sub Process row to handle each row

Call FindBrowse.GenSelectFromStatement

Call FindBrowse.GenSelectFromStatement to call the GenSelectFromStatement function and to fill the local variables.

AlterSQLStatement

AlterSQLStatement in SUB GenSQLStatement to change the generated SQL-Statement

DynamicSQL

Dynamic SQL consists out of four standard parts:

1. SELECT is generated by the "GenSelectFromStatement" function and has all fields from your entity with a 'A.' prefix
2. FROM is generated by the "GenSelectFromStatement" function is your table
3. WHERE is generated from the Restricted input variable (field impl name and content) and has per default an equal operator. That operator could be changed in the AlterWhereStatementForField edit point.
4. ORDER BY is generated from the local OrderBy variable (field impl name) and has per default an ascending order. It could be changed in the AlterOrderByStatementForField edit point.

For easy debugging the final SQL Statement will be written to the application log file.

If you need to do someting special in SQL like a group by or a join you could modify the "SQLStatement" message that is beneath each function or you could use the AlterSQLStatement edit point.

Example message with join:

Example Log-Output with generated statement:

...
2011-06-21 12:13:52,400 INFO  [plexperformance] C5 ContractEngagement.Fetch.CalculateEstimate- START
2011-06-21 12:13:52,402 INFO  [plexperformance] select /*TOP*/ A.C5ZUNB ,A.C5FACD,A.C5KHCD, ... ,A.C5ZQNB  from C5CONTP A
inner join AZIFFEP B on B.AZAGNR=A.C5ZUNB  where B.AZP8SW =  'C2' and B.AZXFNB =  11105181032000 
2011-06-21 12:13:52,406 INFO  [plexperformance] C2 Contract2.BusinessBaseFunctions.Read- START
2011-06-21 12:13:52,407 INFO  [plexperformance] BusinessExtraFunctions.CopyDefaultToACTIParameters- START
2011-06-21 12:13:52,407 INFO  [plexperformance] BusinessExtraFunctions.CopyDefaultToACTIParameters- END
2011-06-21 12:13:52,407 INFO  [plexperformance] C2 Contract2.BusinessBaseFunctions.Read- END
...

Example how to set the where operator of the first field to not equal:

Post Point AlterWhereStatementForField
 Case
  When  Work<SEC CounterLong01> == <SEC CounterLong01.01> 
   Set  Local/WhereStatement<GenWhereOperator> = <GenWhereOperator.not equal>