Home > Contents > Index >
SQLEXP
Creates a string that can be used as part of an SQL WHERE clause.
Syntax
<SQLEXP OUTSTR="STRING_NAME" TYPE="TYPE_VAL" VERB="VERB_VAL" STR="VALUE_LIST" COLNAME="COL_NAME"/>Parameters
OUTSTR (required)
- Name of variable used to store resulting string.
TYPE (required)
- Logical operator to use between relational comparison. Possible values are "
AND
" or "OR
", and "IN
".
VERB (required)
- Relational operator to use. Possible values are:
- "
IN
" - items is in the source list.- "
NOT IN
" - items are not in the source list.- "
LIKE
" - item is like column- "
=
" - item equals column- "
!=
" - item does not equal column.- "
%3c
" (hex <) - item less than column.- "
%3e
" (hex >) - item greater than column.STR (required)
- A comma-separated list of items to test against a column name.
COLNAME (required)
- Column name to use in relational comparison.
Description
The
SQLEXP
tag creates a string that can be used as part of an SQL WHERE clause.SQLEXP
takes a comma-separated string of items and a column name and places a relational operator between each item/column name pair. A logical operator is placed between each item/column name comparison.
Note
Before using
SQLEXP,
you must set a variable calledtablename
. This enablesSQLEXP
to automatically quote values for text columns but not for numerical columns.Error Numbers
The possible values of
errno
include:
Value Description -100 General error. -103 Bad or missing table name. -106 Bad column name.Example
This example constructs a WHERE clause for use by
EXECSQL
.
<SETVAR NAME="tablename" VALUE="colors"/> <SQLEXP OUTSTR="mywhere" TYPE="OR" VERB="LIKE" STR="red,blue" COLNAME="color"/> <!-- The value of mywhere is: color LIKE '%red%' OR color LIKE '%blue%' --> <IF COND="IsError.Variables.errno=true"> <THEN> <!--Handle error--> </THEN> <ELSE> <EXECSQL
SQL="SELECT * FROM colors WHERE Variables.mywhere" LIST="colorlist"/> </ELSE> </IF>This example for
SQLEXP
sets the variablemywhere
to:
username NOT IN (`joe','jim','sue') <SETVAR
NAME="tablename" VALUE="SystemUsers" /> <SQLEXP OUTSTR="mywhere" TYPE="IN" VERB="NOT IN" STR="joe,jim,sue" COLNAME="username" />See Also
Home > Contents > Index > ![]()
Oracle XML Tag Reference
Copyright (c) 2013, 2015, Oracle and/or its affiliates. All rights reserved.