Use of 'LIKE' in SQL 'WHERE' statement in Batch COBOL program

[Tom Walker]

I cannot get the WHERE statement to work correctly unless I use a
literal with the LIKE. I want to use a working storage data name so
that I can vary the WHERE statement.

Example that works:

WHERE DSNAME LIKE 'ABC%'

Example that does not work:

WHERE DSNAME LIKE :WS-HOLD-LIKE-STRING

Is there an alternative method or work around?

[Rhino]

Post by Tom Walker
I cannot get the WHERE statement to work correctly unless I use a
literal with the LIKE. I want to use a working storage data name so
that I can vary the WHERE statement.
WHERE DSNAME LIKE 'ABC%'
WHERE DSNAME LIKE :WS-HOLD-LIKE-STRING
Is there an alternative method or work around?

First of all, I'm going to assume that you are only showing a fragment of
the statement, specifically the WHERE clause, not the complete statement. I
assume you know that a WHERE clause by itself is never going to work; you
need at least a SELECT and FROM clause to make a legal SQL SELECT statement.

I don't have a COBOL compiler handy and haven't written a DB2 COBOL program
in a few years. However, I used to write quite a few of them so I'm pretty
sure that what you are doing *is* possible. (I say "pretty sure" because I
don't recall ever trying to use a host variable in a LIKE predicate.)

What datatype is the column you are searching with the LIKE predicate? As I
vaguely recall, CHARs and VARCHARs behave slightly differently with respect
to the LIKE predicate. With one of them - I don't recall which - you have to
put an extra blank at the end of the search string to make it work for some
cases. Unfortunately, I can't find the spot in the manual that says that.
Perhaps I saw it in a newsgroup post somewhere; you might try doing a Google
search in this newsgroup on 'LIKE'.

You didn't mention which version of DB2 you are using or what platform you
are using. However, the DB2 manuals are generally pretty good so I'd suggest
looking through your manuals for articles about LIKE and see what you can
find about the exact rules of the LIKE predicate. The closest thing I can
find in my manuals is the 'LIKE Predicate' article in the 'Language
Elements' section of the SQL Reference for my platform, DB2 V7.2 on
Windows/Linux/Unix. Unfortunately, I can't find the item about different
behaviours of LIKE for different datatypes. (Maybe they've changed the LIKE
to behave uniformly for all datatypes since I first saw that item??)

If the manual doesn't answer your question, perhaps you could post again
with more details, showing your full statement, information about the
definition of the DSNAME column in your table, a small sampling of data
showing us the cases that concern you, and some remarks about the results
you are getting versus the results you expected to get. That would give us a
better chance to help you. Also, it's *always* a good idea to state your
platform and DB2 version because there are still some differences in
behaviour between platforms and versions.

Rhino

[Gert van der Kooij]

Post by Tom Walker
I cannot get the WHERE statement to work correctly unless I use a
literal with the LIKE. I want to use a working storage data name so
that I can vary the WHERE statement.
WHERE DSNAME LIKE 'ABC%'
WHERE DSNAME LIKE :WS-HOLD-LIKE-STRING
Is there an alternative method or work around?

If I do remember right you need to fill the remaining part of the
string with %. If your WS-HOLD_LIKE-STRING has a length of 8 you need
to put the string 'ABC%%%%%' in this field.

[Bob Sparks]

Post by Tom Walker
I cannot get the WHERE statement to work correctly unless I use a
literal with the LIKE. I want to use a working storage data name so
that I can vary the WHERE statement.
WHERE DSNAME LIKE 'ABC%'
WHERE DSNAME LIKE :WS-HOLD-LIKE-STRING
Is there an alternative method or work around?

I don't use cobal but I have run into similar problems

Make sure you are not passing spaces where you don't expect them. IE

WS-HOLD-LIKE-STRING = 'ABC %' or
WS-HOLD-LIKE-STRING = 'ABC% '

[Mark Yudkin]

It will work, but WS-HOLD-LIKE-STRING must be "varchar", e.g (max length 18
here).
01 WS-HOLD-LIKE-STRING.
49 L PIC S9(4) COMP-5.
49 D PIC X(18).
The length of your string (4 for 'ABC%') goes into L.

I assume from your "it doesn't work" that you're using a fixed length string
variable. In that case, you have something like 'ABC% '
meaning to match ABC, anything, followed by 14 blanks (using my length 18
example).

Review the section on COBOL Host Variables in the Embedded SQL section of
the API Reference.

Post by Tom Walker
I cannot get the WHERE statement to work correctly unless I use a
literal with the LIKE. I want to use a working storage data name so
that I can vary the WHERE statement.
WHERE DSNAME LIKE 'ABC%'
WHERE DSNAME LIKE :WS-HOLD-LIKE-STRING
Is there an alternative method or work around?