AW: Using the LIKE feature within a SELECT in Embedded SQL for RPG

Hi,

you either have to use a varying length field containing the like value,
or use the sQL scalar function to trim off trailing blanks:

Example:
D HstName         S             50A   varying       
D HstNameFix      S             50A                 
D DspName         S             50A                 
 *------------------------------------------------------------
C                   Eval      HstNameFix = '%CANADIAN%'       
                                                           
C/Exec SQL                                                 
C+ Declare CsrC0 Cursor for                                
C+   Select Name1F from Adresse                            
C+            Where     Name1F Like Trim(:HstNameFix)      
C/End-Exec                                                 
                                                           
c/EXEC SQL Open CsrC0                                      
C/END-EXEC                                                 
                                                           
c/EXEC SQL Fetch CsrC0 into :DspName                       
c/END-EXEC                                                 
                                                           
c/EXEC SQL Close CsrC0                                     
C/END-EXEC                                                 
C     DspName       Dsply   
 *-------------------------------------------------------------
C                   Eval      HstName = '%CANADIAN%'    
                                                     
C/Exec SQL                                           
C+ Declare CsrC1 Cursor for                          
C+   Select Name1F from Adresse                      
C+            Where     Name1F Like :HstName         
C/End-Exec                                           
                                                     
c/EXEC SQL Open CsrC1                                
C/END-EXEC                                           
                                                     
c/EXEC SQL Fetch CsrC1 into :DspName                 
c/END-EXEC                                           
                                                     
c/EXEC SQL Close CsrC1                               
C/END-EXEC                                           
C     DspName       Dsply  
C                   Eval     *INLR = *On;       

Birgitta                               

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von PHarte@xxxxxxxxxx
Gesendet: Freitag, 13. Mai 2005 20:39
An: rpg400-l@xxxxxxxxxxxx
Betreff: Using the LIKE feature within a SELECT in Embedded SQL for RPG


    I am tring to use the LIKE function within my SELECT statement, and am 
having some 
    trouble with it.
    Both my prompt field and field I want to search are 30a.
    Ex:  I key "CANADIAN" on the prompt and want to return to my subfile 
any 
    records with "CANADIAN COAST GUARD" and "MOLSON CANADIAN" and "THE 
    CANADIANS WIN HOCKEY GOLD AGAIN".
    c/exec sql 

    c+ declare maincursor cursor for select * from wmmstr where 
    c+ :pname like wmname order by wmname 
    c/end-exec 
 
    Thanks,
    Pat. 

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.