Home >	Contents >	Index >
Template Tag TOC  |   Alpha TOC  |   Tag Family TOC  |   Purpose TOC  |   Annotated TOC  |   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 called tablename. This enables SQLEXP 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 variable mywhere 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

EXECSQL

		Home >	Contents >	Index >
	Oracle XML Tag Reference Copyright (c) 2013, 2016, Oracle and/or its affiliates. All rights reserved.