Programming Guide

dm_gen_change_select_where

Edits the WHERE clause in a SELECT statement used in automatic SQL generation 

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

arg
Reserved for future use.

where_expr
Text of the expression to include in the WHERE clause. If the expression includes a parameter and the function is called within a JPL procedure, the parameter name must be declared with four colons because of colon expansion (::::parm1).

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

DM_GEN_APPEND
When flag is set to this value, where_expr is added to end of the WHERE clause. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list
 FROM table_list WHERE link_expression
 AND existing_where_expr AND where_expr

DM_GEN_PREPEND
Adds where_expr to the beginning of the expressions derived from the use_in_where property. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list
 FROM table_list WHERE link_expression
 AND where_expr AND existing_where_expr

DM_GEN_REPLACE_ALL
Removes all the expressions based on the use_in_where property being PV_YES and where_expr replaces the previous data. This produces the following statement: 
DBMS DECLARE cursor FOR SELECT select_list
 FROM table_list WHERE link_expression AND where_expr

You also need to call dm_gen_change_execute_using to remove the existing_where_expr from the USING clause of the EXECUTE statement.

Returns

Description

dm_gen_change_select_where lets you edit the WHERE clause of a SELECT statement. The structure for the SELECT statement, which is generally built by a call to dm_gen_sql_info in the TM_SEL_GEN event, must already exist before dm_gen_change_select_where is called.

By default, the data for the WHERE clause comes from:

dm_gen_change_select_where adds to or replaces the data based on the use_in_where property. For more information on how the SQL generator uses this property, refer to Chapter 33, "Using Automated SQL Generation," in Application Development Guide.

In particular, this function can be used to add a BETWEEN clause or a subquery to a SELECT statement.

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_where 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 the next 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 IN clause to WHERE clause.
	# Function property is set to titles_in.

proc titles_in (event)

vars retval(5)

if (event == TM_SEL_BUILD_PERFORM)
{
vars occ(3), i(3), in_buffer(255) comma(1)

occ = @widget("qbe_titleid")->num_occurrences
 
# If the array "qbe_titleid" contains data, build
# a SQL "in" clause.
  if (occ > 0)
  {
  for i=1 while i <= occ
  {
    if (qbe_titleid[i] != "")
    {
		in_buffer = in_buffer ## comma ## \
        ':+qbe_titleid[i]'
		comma = ","
    }
  }
 
  in_buffer = "title_id in (" ##in_buffer ")"
  retval = dm_gen_change_select_where \
    ("", in_buffer, DM_GEN_APPEND)

  if (retval != 0)
  return TM_FAILURE
  }
}
return TM_PROCEED
 
# JPL Procedure:
# Append search condition using onscreen value.
# Function property is set to titles_where.

proc titles_where (event)

vars retval(5)

  if (event == TM_SEL_BUILD_PERFORM)
  {
  retval = dm_gen_change_select_where\
    ("", "film_minutes > ::::parm1", DM_GEN_APPEND)
  retval = dm_gen_change_execute_using("", "parm1", \
    "film_minutes", 1, DM_GEN_ABSOLUTE_OCCUR, \
    DM_GEN_APPEND)

    if (retval != 0)
    return TM_FAILURE
    }

return TM_PROCEED

See Also

dm_gen_sql_info, dm_gen_change_execute_using