Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Using the LIKE clause in an Embeded SQL

    How to use the like clause in an embeded SQL?
    When we accsess the data using simple SQL, the following query will work.

    SELECT NAME FROM TABLE WHERE NAME LIKE 'TH%'

    will give THOMAS. The same query wont work when used in a COBOL program.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    01 user-input-name pic x(20)
    01 name pic x(20).

    move user-input-name to name.
    examine name replacing first ' ' by '%'.

    SELECT NAME FROM TABLE WHERE NAME LIKE :name
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    I am no COBOL expert, but I think, that Marcus_A is not correct. You have to change all trailing blanks by '%', because if you only append one '%' behind the last character, DB2 will look, in your example for a name beginning with 'TH', followed by anything, but with 17 blanks at the end (assuming your host-variable is CHAR(20)).

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Walter, you are correct that you are not a COBOL expert. The example I gave works fine on OS/390 COBOL with DB2 for OS/390. Trailing blanks are ignored in a LIKE predicate in this example.

    I will not guarantee that the EXAMINE statement syntax is exactly how it should be used for all circumstances, but it is close enough.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Correction.

    I just looked in the version 7 manuals, and it looks like it has changed since I have used it last. Therefore Walter is correct and I was wrong.

    I actually have some older COBOL programs that only use a single % with trailing blanks that worked correctly when they were written, but that was in DB2 version 3.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •