%LikeExact meta-SQL element

Syntax

%LikeExact(fieldname, "Literal")

Description

The %LikeExact meta-SQL variable expands to look for literal values. Use this variable when exact matches are necessary, taking into account wildcards in the literal values.

Note:

This meta-SQL is not implemented for COBOL.

%LikeExact generates one of the following:

  • If the literal contains no wildcards:

    fieldname = 'literal'

  • If the literal ends with the '%' wildcard:

    fieldname like 'literal' [escape '\']

Some platforms require that you use RTRIM to get the correct value. The following characters are wildcards even when preceded with the backslash (\) escape character.

  • %

  • _

Therefore, on some platforms, the literal must end with a percent sign (%) wildcard that isn't preceded by a backslash (\). Here are some examples:

  • literal = 'ABC%'

    You do not need RTRIM on any platform.

  • literal = 'ABC\%'

    You need RTRIM on Microsoft SQL Server and DB2.

Parameters

Parameter Description

fieldname

Specify a field to be used in the first part of the Like comparison.

literal

Specify the value to search for.

Example

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'ABC')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID = 'ABC'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'AB%C')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE RTRIM(AE_APPL_ID) LIKE 'AB%C'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE LIKEEXACT(AE_APPL_ID, 'AB%C%')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID LIKE 'AB%C%'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'AB%C% ')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID LIKE 'AB%C% '

The following example shows using ExpandSqlBinds:

SQLExec(ExpandSqlBinds("SELECT COUNT(*) FROM PS_ITEM WHERE %LIKEEXACT(BUSINESS_⇒
UNIT, :1)", "M04"), %COUNT);

Considerations Using Bind Markers

If you're using a bind marker (such as ":1") for the literal argument in a SQLExec, you must wrap the SQL string with ExpandSqlBinds. ExpandSqlBinds replaces bind markers with the actual input values.

The following forms work:

  • Application Engine SQL action (with or without the ReUse property enabled).

    UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, %Bind(AE_⇒
APPL_ID, STATIC))

    The STATIC modifier is only required if the ReUse property is enabled, but you can always use it.

  • PeopleCode.

    AE_TESTAPPL_AET.AE_APPL_ID = "AB\_C";
    SQLExec("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, :⇒
AE_TESTAPPL_AET.AE_APPL_ID)");

    Here is another acceptable form:

    SQLExec(ExpandSqlBinds("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LikeExact⇒
(AE_APPL_ID, :1)", "AB\_C"));

This form does not work:

SQLExec("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, :⇒
1)", "AB\_C");

Related Topics