Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1460
  • Last Modified:

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

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
windyui
Asked:
windyui
  • 2
  • 2
  • 2
  • +1
1 Solution
 
theo kouwenhovenCommented:
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
 
windyuiAuthor Commented:
...
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
 
windyuiAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
theo kouwenhovenCommented:
You tested it with a * already???
0
 
daveslaterCommented:
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
 
daveslaterCommented:
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
 
shalomcCTOCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now