?
Solved

using LIKE statement in SQLRPG with variable and '%'...

Posted on 2003-03-27
7
Medium Priority
?
1,340 Views
Last Modified: 2008-03-10
hello all my friend ,
   I meet a BIG problem now :

C            MOVE '''%'     SIGBEF  2
C            MOVE '%'''     SIGAFT  2
C     SIGBEF CAT  NAMES     NAMST1  37
C     NAMST1 CAT  SIGAFT    NAMSTR  39
C*This make NAMSTR ->  '%NAMES%'
C*SQL start here
C/EXEC SQL
C+  SELECT NAME FROM CUSTMAS
C+    WHERE NAME LIKE :NAME1                    *<-big problem here
C/END-EXEC
*this select all the records have a name include NAMES

it can run ,BUT~~~~~~~~~~~~~~~~~!!!
there are NOTHING returns........
HELP ~~~~~~~~~~~!! PLS T_T

                                                 By windyui
0
Comment
Question by:windyui
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 8217223
The contents of field NAMESTR is not what you expect.

the way you use the CAT statement the contents of this field will be "%somevalue               %"

To get the value you need you have to use :

C       '"%'      CAT  NAMES      NAMESTR
C                 CAT  '%"':0      NAMESTR

the :0 in the second line wil remove trailing  blanks from NAMESTR before concat the %" to it.

Have Fun !!!

0
 

Author Comment

by:windyui
ID: 8221942
...
Actually ,
I have done it just as what u said before I posted it here..T______________T...

It doesn't work.

NAMSTR's value is '%NAMES%' , but nothing returns when i execute this sql.And I am wondering if the Sql statement is NOT dynamic sql.
HELP~~~~~~~~~~~~~~~~~~~~~~~~~!!!!!

and the code is the following:
C            MOVE '''%'     SIGBEF  2
C            MOVE '%'''     SIGAFT  2
C     SIGBEF CAT  NAMES     NAMST1  37
C     NAMST1 CAT  SIGAFT    NAMSTR  39
C*This make NAMSTR ->  '%NAMES%'
C*SQL start here
C/EXEC SQL
C+  SELECT NAME FROM CUSTMAS
C+    WHERE NAME LIKE :NAMSTR        *<-big problem here
C/END-EXEC
*this select all the records have a name include NAMES
0
 

Author Comment

by:windyui
ID: 8222275
Finally , I find that the % charactor is NOT the WILDCHAR ,see the following sql.

c/exec sql
c+   select * from AAA where
c+      NAME like '%JACK%'
c/end-exec

what will happen??
the SQLRPG will search %JACK% in the database instead of
JACK.

T_________________________T

so , what is the WILDCHAR of SQLRPG??????????????????
HELP
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 8228837
You tested it with a * already???
0
 
LVL 14

Expert Comment

by:daveslater
ID: 8747631
Hi
The problem you have is:
SQL is interpreting the statement as:

SELECT NAME FROM CUSTMAS
 WHERE NAME LIKE “%VALUE%       “

i.e. it has a number of spaces it the end!

Try the following


C                  MOVE   *ALL’%’      C255255
C      ‘%’            CAT      NAME:0      NAMST1    37
C      NAMST1      CAT      C255:0            NAMES      39

This makes names ‘%NAMES%%%%%%%%%%%%%%%%%%%%%’

The like statement will then work’
0
 
LVL 14

Accepted Solution

by:
daveslater earned 1196 total points
ID: 8747637
Hi
The problem you have is:
SQL is interpreting the statement as:

SELECT NAME FROM CUSTMAS
 WHERE NAME LIKE “%VALUE%       “

i.e. it has a number of spaces it the end!

Try the following


C                  MOVE   *ALL’%’      C255      255
C      ‘%’            CAT      NAME:0      NAMST1    37
C      NAMST1      CAT      C255:0            NAMES      39

This makes names ‘%NAMES%%%%%%%%%%%%%%%%%%%%%’

The like statement will then work’
0
 
LVL 33

Expert Comment

by:shalomc
ID: 8993394
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept daveslater comment as answer
 

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ShalomC
EE Cleanup Volunteer
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question