Using host variable with 'like' operator

[jxnaray]

Hi, I have some problems using the host variable with like operator. I have a situation wherein I have to extract rows with a particular column matching different patterns. e.g

SELECT *
FROM MYTABLE
WHERE COLUMN1 LIKE ATTERN;

I am embedding the above SQL in my COBOL code as part of the cursor definition. I have declared the host variable PATTERN in the working-storage section. In the program I move a literal '%NM%' (inclusive of quotes) to PATTERN hoping that DB2 will substitute this in the above SQL statement. But when the SQL executes, it returns no rows (SQLCODE = 100). The problem is - PATTERN is derived from some other inputs and also the no. of times I want to execute the SQL depends on other factors. What do I do in this scenario??

Any help is highly appreciated.
Thanks,
Jay.

 

[MarcLodge]

Jay,
I believe that you need to leave out the quotes.
Let me know if this works.
Marc

 

[jxnaray]

Marc, I tried moving just NM% (without any quotes), and still it does not seem to work.

Thanks,
Jay.

 

[MarcLodge]

Jay,
Not wishing to state the obvious, but are you obsolutely 100% positive that something exist with begins NM in upper case? Can you confirm this by running the SQL in QMF or SPUFI or something similar?

The structure of the SQL is correct.

Marc

 

[jxnaray]

Yes, I do have rows with COLUMN1 beginning with caps NM. I didn't try in spufi, however I tried the same in the embedded SQL in the cobol program itself. It does work with literal '%NM%' in the cursor definition. It does not work with a host variable. Sometimes so called obvious things cause real problem as in this case.

 

[kkitt]

I have run into this problem before and did some research on it (can't remeber where i found it right now)and it said that when using a host variable the remaing spaces needed to be filled with the '%' character.

I wrote a little routine thta would find the last none space character and then fill in from there to the end of the field length. This should not be hard to modify if the charaters that you are looking for reside in someplace other in the field. For this I would pre-load the host variable and then move in the characters you want by reference modifications.

*-----------------------------------------------------------
* ROUTINE TO FIND THE LAST NONE SPACE CHARACTER IN THE
* DESCRIPTION FIELD. ONCE FOUND THEN MOVE THE DESCRIPTION
* FIELD STARTING FROM THE FIRST CHAR TO THE LAST NON SPACE.
* AFTER THIS THE CALC THE FIRST SPACE AFTER THE LAST NON
* SPACE AND THE REMAINING SPACE LEFT. THESE SPACES WILL
* REPLACED BY '%.
*
* IF NO SPACES FOUND THEN MOVE THE WHOLE FIELD.
*-----------------------------------------------------------
     IF WS-ITEM-DESCRPT = SPACES
         PERFORM 0000-GOBACK      
      ELSE
         PERFORM VARYING DESC-SUB FROM DESC-MAX-LEN BY -1
            UNTIL DESC-SUB = 0
               OR WS-ITEM-DESCRPT(DESC-SUB:1) NOT EQUAL SPACES
         END-PERFORM
         IF DESC-SUB GREATER THAN ZERO
            MOVE WS-ITEM-DESCRPT(1:DESC-SUB) TO WS-DE6C-ITEM-DESC
            ADD 1 TO DESC-SUB GIVING WS-START
            SUBTRACT DESC-SUB FROM DESC-MAX-LEN GIVING WS-LENGTH
            INSPECT WS-DE6C-ITEM-DESC(WS-START:WS-LENGTH)
               REPLACING ALL SPACES BY '%'
         ELSE
            MOVE WS-ITEM-DESCRPT TO WS-DE6C-ITEM-DESC
         END-IF
      END-IF.      
*-----------------------------------------------------------

 

[gregsimpson]

Jay,

noth kkitt and MArc are correct. You don't want the quotes inside the host variable. However you do need to replace the rest of the characters with a %.

It is a little confusing that it works in all other situations with only 1 % but when using a host variable you have to potentially provide %%%%%%.......


Cheers
Greg

 

[MarcLodge]

Jay,
My apologies, I should have picked up on this earlier, especially as the program that I checked the syntax with, I had written the following code:

INSPECT SFULLNAME
CONVERTING ' ' TO '_'
AFTER INITIAL '%'

Marc

 

[Poseidon]

jay,

try this:

set PATTERN to "NM"

SELECT * FROM MYTABLE
WHERE COLUMN1 LIKE '%' !! ATTERN !! '%'

martin

 

[Alanatwork]

You need to use a variable length cobol field like this:

03 W020-SEARCH-VALUE.
49 W020-SEARCH-LEN PIC S9(4) COMP.
49 W020-SEARCH-VAL PIC X(25).

set length component equal to actual size of string and I think you should find it works ok.

 

[jxnaray]

Thanks one and all,
I tried various options you suggested, and the one wherein we replace the remaining bytes with '_' or '%' worked well.

The solution having to define a variable length variable - didn't seem to work. Or I might have missed sth here.

Anyway, I am glad that I joined this tek group.
Thanks,
Jay.