| View previous topic :: View next topic |
| Author |
Message |
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
 Posted: Mon Jul 13, 2009 10:16 am Post subject: Problem in SQL query |
 |
|
Hi All,
The program is in IMS-DC. In which the user enters Name, and then program retrieves the detail from table.
The table layout is as following :
Code:
| Code: | Name(10 bytes) Address
------ ---------
sachin kumar x
kumar x1
arun x2
Jungle x3 |
The Query should be such that when the user enters |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 13, 2009 10:20 am Post subject: |
|
|
lenovo,
If the name is only 10 bytes how can you have a value of "sachin kumar" which is 12 bytes long? .
| Code: |
----+----1--
sachin kumar
|
|
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Mon Jul 13, 2009 12:06 pm Post subject: |
|
|
kolusu,
Thanks for the quick reply.
Sorry, it was a typo. The actual length is 24 bytes.
Please let me know if you still have any concern. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 13, 2009 12:20 pm Post subject: |
|
|
Lenovo,
I am not sure as to why you need to pad that many % on the LIKE statement.
It should be a simple one
| Code: |
SELECT NAME
,ADDRESS
FROM TABLE
WHERE NAME LIKE '%AR%'
;
|
When the pattern specified in a LIKE predicate is a parameter marker and a fixed-length character host variable is used to replace the parameter marker, specify a value for the host variable that is the correct length. If you do not specify the correct length, the select does not return the intended results. For example, if the host variable is defined as CHAR(10) and the value WYSE% is assigned to that host variable, the host variable is padded with blanks on assignment. The pattern used is 'WYSE% ', which requests DB2 to search for all values that start with WYSE and end with five blank spaces. If you intended to search for only the values that start with 'WYSE', you should assign the value 'WYSE%%%%%%' to the host variable. |
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Mon Jul 13, 2009 1:03 pm Post subject: |
|
|
I agree with your comment, but there is some other confussion.
1. If in place of %AR% we have %%% ( assuming Name field in the table contains % ). Now in place of 'AR' we have to search '%' . In this case the query should retrieve only Name having % and not any other value.
2. Also in SELECT query our value NAME LIKE '%AR%' is not hard coded, I have to accept the value from user at runtime, So at that user can enter any value which may be 'AR' or '%' may be. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 13, 2009 2:56 pm Post subject: |
|
|
Lenovo,
1. If your table has records with % then it needs to be dealt separately.
2. I showed you an example of coding. You can replace the "ar" with your host variable.
Ok here is how it works
| Code: |
IF USER-INPUT = '%'
MOVE '$' TO HOST-VAR
ELSE
MOVE USER-INPUT TO HOST-VAR
END-IF
|
and the query is
| Code: |
SELECT NAME
,ADDRESS
FROM TABLE
WHERE REPLACE(NAME,'%','$') LIKE '%HOST-VAR%'
;
|
This would pick both the values for you
read about Replace here
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnsqj12/3.2.89? |
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Tue Jul 14, 2009 1:02 pm Post subject: |
|
|
Kolosu,
I was unable to test this query today. But dont you think if the table contain $ in the Name field then this will not work.
Is it possible to have a general solution in which the table contains all special chars? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 14, 2009 1:52 pm Post subject: |
|
|
| lenovo wrote: | Kolosu,
I was unable to test this query today. But dont you think if the table contain $ in the Name field then this will not work.
Is it possible to have a general solution in which the table contains all special chars? |
Yes it will NOT and you need a find a character which does not exist in that column. can you also have numerical values in the column? if not then use them like converting the special character to x'05' and if it is a regular character it would be converted to the same alphabet
Untested code
| Code: |
IF USER-INPUT = special-char
MOVE special-char TO host-srch-VAR
MOVE X'05' TO host-repl-var
ELSE
MOVE USER-INPUT TO host-srch-var
host-repl-var
END-IF
MOVE USER-INPUT TO HOST-VAR
|
and the sql would be
| Code: |
SELECT NAME
,ADDRESS
FROM TABLE
WHERE REPLACE(NAME,host-srch-var,host-repl-var) LIKE '%HOST-VAR%'
;
|
PS: I would really appreciate if you can spell my name correctly, I corrected your prior post hoping that it was a typo , but you seem to repeat the same mistake.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Wed Jul 15, 2009 12:55 am Post subject: |
|
|
kolusu,
Firstly really sorry for spelling your name wrong. I make sure it wont happen again.
I tried the query using REPLACE clause in the program but when I Xped the program, I was receiving SQLCODE +100. However when I am using the same query in the QMF, it was working fine.
I am really unaware why this query is not working in the program.
Is there any specific reason for it? |
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Wed Jul 15, 2009 3:35 am Post subject: |
|
|
kolusu,
One thing more, I am using cursor in the program. Also when i run the query in QMF, there was a simple select stmt there. Is the problem is because of cursors?
If yes then what is the solution for this? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
|
| Posted: Wed Jul 15, 2009 11:10 am Post subject: |
|
|
lenovo,
It doesn't matter if you are using cursor or a singleton SQL. Do you have numerical numbers 0 thru 9 in that column?
Kolusu |
|
| Back to top |
|
|
lenovo
Beginner
Joined: 27 Nov 2008
Posts: 31
Topics: 9
Location: India
|
| Posted: Wed Jul 15, 2009 11:46 am Post subject: |
|
|
Kolusu,
The field Name(24 bytes) consists of name of the customer which may contain numbers as well as characters. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
