Programming Guide

dm_gen_change_select_having

Edits the HAVING clause in a SELECT statement for automatic SQL generation 

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

arg
Reserved for future use.

search_cond
The search condition to include in the HAVING clause.

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

DM_GEN_APPEND
Adds search_cond to the end of the HAVING clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM  tables HAVING existing_having_clause AND search_cond

DM_GEN_PREPEND
Adds search_cond to the beginning of the HAVING clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM
 tables HAVING search_cond AND existing_having_clause

DM_GEN_REPLACE_ALL
search_cond replaces the existing HAVING clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list FROM
 tables HAVING search_cond

If flag is set to this value and search_cond is set to an empty string, the HAVING clause is removed. For example: 

x = dm_gen_change_select_having
 ("", "", DM_GEN_REPLACE_ALL)

Returns

Description

dm_gen_change_select_having lets you edit the HAVING 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.

Generally, a HAVING clause sets search conditions for the preceding GROUP BY clause. The SQL generator creates GROUP BY clauses automatically for aggregate functions. GROUP BY clauses can also be generated using the function dm_gen_change_select_group_by. HAVING clauses can be generated with the Having property or by using this function. For more information on automatic SQL generation, 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 dm_gen_change_select_having 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:
	# Generate a having clause.
	# Function property is set to titles_having.

proc titles_having (event)

vars retval(5)

  if (event == TM_SEL_BUILD_PERFORM)
	  {
	  retval = dm_gen_change_select_having\
	    ("", "count(*) > 2", DM_GEN_APPEND)

  retval = dm_gen_change_select_having\
	    ("", "dir_last_name like 'W%'", DM_GEN_APPEND)

  if (retval != 0)
	    return TM_FAILURE
	  }

return TM_PROCEED

See Also

dm_gen_sql_info