| View previous topic :: View next topic |
| Author |
Message |
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
 Posted: Mon Dec 11, 2006 8:24 am Post subject: Problem with wild character in DB2 select |
 |
|
I am not sure why the below query is not returning any records. Can anyone please explain the problem. I have already searched the MVS FORMS but I coudnt get any help for this.
Please let me know why my Query 3,4,5 not returning any records.
| Code: | SELECT * FROM RUSH_PARTS;
---------+---------+---------+---------+---------+---------+
P# PNAME COLOR WEIGHT CITY
---------+---------+---------+---------+---------+---------+
P1 NUT RED 12 LONDON
P2 BOLT GREEN 17 PARIS
P5 CAN BLUE 12 PARIS
P3 SCREW BLUE 17 ROME
P4 SCREW RED 14 LONDON
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LOND%';
---------+---------+---------+---------+---------+---------+
P# PNAME COLOR WEIGHT CITY
---------+---------+---------+---------+---------+---------
P1 NUT RED 12 LONDON
P4 SCREW RED 14 LONDON
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LO%DON';
---------+---------+---------+---------+---------+---------
P# PNAME COLOR WEIGHT CITY
---------+---------+---------+---------+---------+---------
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LO_DON';
---------+---------+---------+---------+---------+---------+
P# PNAME COLOR WEIGHT CITY
---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LONDO_';
---------+---------+---------+---------+---------+---------+
P# PNAME COLOR WEIGHT CITY
---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
_________________
murthy |
|
| Back to top |
|
 |
sriramla
Beginner
Joined: 22 Feb 2003
Posts: 74
Topics: 1
|
| Posted: Mon Dec 11, 2006 8:53 am Post subject: |
|
|
In your query #3,4 & 5 your LIKE critieria is incomplete. CITY field has a lot of spaces at the end and you need to provide them in your queries.
| Code: |
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LO_DON '; (Number of spaces depends on the size of CITY field).
|
Your second query worked fine becuase you had '%' at the end. To make all queries work, you need to have '%' at the end as well (or provide exact number of spaces). |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Dec 11, 2006 8:55 am Post subject: |
|
|
mvenkatesha,
| Quote: |
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LO%DON';
|
The reason you did not get any results for your query is because there are spaces after DON and you are only looking for a wild character in pos 3. so change your query as follows. Add another % at the end.
| Code: |
SELECT * FROM RUSH_PARTS
WHERE CITY LIKE 'LO%DON%';
|
or
| Code: |
SELECT * FROM RUSH_PARTS
WHERE SUBSTR(CITY,1,6) LIKE 'LO%DON';
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
| Posted: Thu Dec 14, 2006 2:22 am Post subject: |
|
|
sriramla / kolusu
Thanks a lot for your explanation. You have explained the problem if such a fashion I will remember this in my dreams.
Once again Thanks a lot.
_________________
murthy |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
