Using a copycode variable with the LIKE predicate of DB2

, ,
1

I’m trying to use copycode to allow for a variable to be placed in the LIKE predicate of a SELECT statement. Has anyone been able to do this? The following is my attempt…

program

INPUT (AD=MIL’_‘)
‘ENTER FIRST NAME:’ #FIRST (AL=9)
’ AND/OR LAST:’ #LAST (AL=9)
COMPRESS “'” #FIRST “%'” INTO #FIRST LEAVING NO
COMPRESS “'” #LAST “%'” INTO #LAST LEAVING NO
INCLUDE TLKADDC1 ‘#FIRST’ ‘#LAST’

copycode

WRITE &1& &2&
SELECT * INTO VIEW TEM
FROM VEMPE
WHERE FIRST_NM LIKE(&1&)
AND LAST_NM LIKE(&2&)
ORDER BY LAST_NM
FETCH FIRST 5 ROW ONLY
DISPLAY LOGON_ID FIRST_NM LAST_NM
END-SELECT

results

LOGON_ID FIRST_NM LAST_NM

‘%’ ‘LINK%’

thanks

2

My DB2 is ancient history, but, I think you have too many apostrophes.

Your statement will read (in part)

WHERE FIRST_NM LIKE (‘#FIRST%’)

don’t you want the value of #FIRST there? e.g. LIKE (STEVE%)

also, make sure you have enough room for the name and first name and the percent.

steve

3

I’m not a db2 user at all, but if you can’t place a variable in the LIKE predicate within a Natural program, you won’t be able to get around this restriction by using copycode.

Posting tip: Your code and output will be easier to read if you put it in code tags.

4

Steve, thanks for the reply. To refresh your memory, the LIKE predicate syntax is LIKE(‘var%’) where var = a text string. As the WRITE statement inside the copycode shows, the values passed to the copycode are ‘%’ and ‘LINK%’. If these were inserted properly, the SELECT statement would be:

SELECT * INTO VIEW TEM
FROM VEMPE
WHERE FIRST_NM LIKE(‘%’)
AND LAST_NM LIKE(‘LINK%’)
ORDER BY LAST_NM
FETCH FIRST 5 ROW ONLY

I’ve tried everything I can think of to make this work, but the copycode method is not working for me. If someone knows the proper syntax for the copycode or some other method to allow a variable to be used in the LIKE predicate, please let me know. At this point, I’m ready to abandon the idea.

Thanks

5

Pad out the variables with %.

DEFINE DATA LOCAL                                     
1 #INPUT       (A32)                                  
1 #NAME        (A128)                                 
1 #CREATOR     (A128)                                 
END-DEFINE                                            
*                                                     
INPUT #INPUT                                                                        
*                                                     
EXAMINE FULL #INPUT FOR FULL ' ' AND REPLACE WITH '%' 
*                                                     
WRITE #INPUT                                          
*                                                     
SELECT NAME, CREATOR                                  
  INTO #NAME, #CREATOR                                
  FROM SYSIBM-SYSTABLES                               
  WHERE NAME LIKE #INPUT                              
  DISPLAY #NAME(AL=32)        
          #CREATOR(AL=32)     
END-SELECT                    
*                             
END

This will make the input something like ‘TABLENAME%%%%%%%%%%%%%%%%%%%%%%%’

edit: Removed the unnecessary COMPRESS.

6

Does Natural support LIKE?

Try without the LIKE clause and see if it works

steve

7

The LIKE clause works, you just have to know the super-secret way to use it. 8)

So, don’t tell anyone!

8

The problem is that DB2 will include the spaces after the ‘%’ in the pattern that it is comparing, so it is looking for a value with all those spaces.

Another way you can code it:

DEFINE DATA LOCAL                                 
1 #INPUT       (A32)                              
1 #NAME        (A128)                             
1 #CREATOR     (A128)                             
1 #POSITION    (P3)                               
END-DEFINE                                        
*                                                 
INPUT #INPUT                                      
*                                                 
COMPRESS #INPUT '%'                               
    INTO #INPUT                                   
    LEAVING NO SPACE                              
*                                                 
EXAMINE #INPUT FOR '%' GIVING POSITION #POSITION  
*                                                 
WRITE #INPUT                                      
*                                                 
SELECT NAME, CREATOR                              
  INTO #NAME, #CREATOR                            
  FROM SYSIBM-SYSTABLES                           
  WHERE NAME LIKE SUBSTR(#INPUT,1,#POSITION )    
  DISPLAY #NAME(AL=32)                           
          #CREATOR(AL=32)                        
END-SELECT                                       
*                                                
END

This only passes the part of the input up to the ‘%’ and not the trailing spaces.

9

Daniel, good to see you are still around. You da Man!