Programming Guide

dm_gen_change_select_group_by

Edits the GROUP BY clause in a SELECT statement for automatic SQL generation 

#include <tmusubs.h>
int dm_gen_change_select_group_by(char *arg, char *column, int flag);

arg
Reserved for future use.

column
The name of the column to be used in the GROUP BY clause.

flag
Specifies the type of change to make with one of the following constants:

DM_GEN_APPEND
Adds column to the end of the GROUP BY clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM
 tables GROUP BY existing_group_by_list, column

DM_GEN_PREPEND
Adds column to the beginning of the GROUP BY clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM
 tables GROUP BY column, existing_group_by_list

DM_GEN_REPLACE_ALL
column replaces the previous GROUP BY clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM
 tables GROUP BY column

If flag is set to this value and column is set to an empty string, the GROUP BY clause is removed. For example: 

x = dm_gen_change_select_group_by
 ("", "", DM_GEN_REPLACE_ALL)

Returns

Description

dm_gen_change_select_group_by allows you to edit the GROUP BY clause built with the SQL generator. The data structure for the SELECT statement, which is built by a call to dm_gen_sql_info (generally in the TM_SEL_GEN event), must already exist before this function is called. Note that this function must be called once for each change you wish to make.

By default, the SQL generator builds a GROUP BY clause automatically when one of the select expressions is an aggregate function. For more information on how the SQL generator builds statements, refer to Chapter 33, "Using Automated SQL Generation," in Application Development Guide.

This function can be implemented as part of a transaction manager event function which processes the TM_SEL_BUILD_PERFORM event. If you are modifying the select processing for a server view, call the dm_gen_change_select_group_by function from an event function attached to the first parent table view in the server view.

To view a sample event function written in JPL, refer to the example in this section. For more information on writing transaction event functions, refer to Chapter 32, "Writing Transaction Event Functions," in Application Development Guide.

Example

# JPL Procedure:
	# Append column not part of table view to automatically
	# generated group by clause.
	# Function property set to titles_group.

proc titles_group (event)

vars retval(5)

  if (event == TM_SEL_BUILD_PERFORM)
	  {
	    retval = dm_gen_change_select_list \
	      ("", "rating_code", "rc", DM_GEN_APPEND)
	    retval = dm_gen_change_select_group_by \
	      ("", "rating_code", DM_GEN_APPEND)
	
    if (retval != 0)
	      return TM_FAILURE
	  }

return TM_PROCEED

See Also

dm_gen_sql_info