|
View previous topic :: View next topic
|
| Author |
Message |
vvmanyam
New User
Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
|
|
|
|
Hi ,
I have two tables which needs to be joined based on COL2. The issue here is One of the tables has wild card entries. How to compare these 2 columns while joining.
Sample Table 1:
| Code: |
COL1 COL2
----- -----
AAA A23
BBB A34
CCC BB0
DDD C10
EEE C04
|
Table2:
Output of the query should be:
| Code: |
COL1 COL2
----- -----
AAA A23
BBB A34
DDD C10
|
'%' will be cosidered as wild card and any character can be replaced for matching
Thanks,
Balu |
|
| Back to top |
|
 |
vvmanyam
New User
Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
|
|
|
|
Please note that we are using 'DB2 version 7.2'
Thanks,
Balu |
|
| Back to top |
|
|
vvmanyam
New User
Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
|
|
|
|
I am surprised!!
This is first time in last one year, I have not received any replies for my posts!!
This is my first post in DB2 forum 
I finally used Sort to generate the multiple "LIKE" condition query in runtime and submit that query!!
Can this requirement be achieved in a single query?
Thanks,
Balu |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Well, you can't join since your relationship is LIKE and not EQUAL or NOT EQUAL.
what you proposed is a SELECT FROM table a WHERE A.COL2 LIKE (SELECT COL2 FROM table b).
did not have time to use my own machine time experimenting, did you? |
|
| Back to top |
|
|
vvmanyam
New User
Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
|
|
|
|
This is what I have tried!!
| Code: |
SELECT A.COL1 A.COL2
FROM TABLE1 A, TABLE2 B
WHERE A.COL2 LIKE(SELECT COL2 FROM TABLE2)
|
This is not working
So, I had to use below query for that!!
| Code: |
SELECT A.COL1 A.COL2
FROM TABLE1 A
WHERE
(A.COL2 LIKE 'A%%' OR
A.COL2 LIKE 'C1%');
|
Since the no of records in table2 is not constant, I have first extracted the Table2 content and generated the Where condition with list of LIKE commands dynamically using 'SORT' step and concatenated with the remaining query!!
Thanks,
Balu |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
from the book:
| Quote: |
LIKE pattern-expression An expression that specifies the pattern of characters to be matched. The expression can be specified by any one of the following:- A constant
- A special register
- A host variable (including a LOB locator variable)
- A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
- A CAST specification whose arguments are any of the above
- An expression that concatenates (using CONCAT or ||) any of the above
The expression must also meet these restrictions:- The maximum length of pattern-expression must not be larger than 4000 bytes. Predicates
- If a host variable is used in pattern-expression, the host variable must be defined in accordance with the rules for declaring string host variables and must not be a structure.
- If escape-expression is specified, pattern-expression must not contain the escape character identified by escape-expression except when immediately followed by the escape character, '%', or '_'. For example, if '+' is the escape character, any occurrences of '+' other than '++', '+_', or '+%' in the pattern is an error
|
the bold was added by me. Though you can generate IN-lists, there apparently is no LIKE-list capability.
either you- build one (1) query as you are doing (and hope table 2 does not contain many rows)
- you have a loop, using a CURSOR on table 2 (or rowset retrieval)
- to access and build your host-structure pattern-expressions
- use a second cursor (or rowset retrieval) to retrieve matching rows from table 1 for each pattern-expression.
maybe others read the manual and decided not to answer you.
I knew that you can't have LIKE-lists,
but was intrigued by the potential of the correlated sub-query,
but rechecking the manual tells me that you cannot reference a pattern-expression contained in a column. |
|
| Back to top |
|
|
vvmanyam
New User
Joined: 16 Apr 2008
Posts: 86
Location: Bangalore
|
|
|
|
I thank you for all the information provided!
The second solution is a feasible one, but I have to code this in a JCL, which is not possible.
I am going with first solution itself as my second table will have maximum of 10 records.
Thanks,
Balu |
|
| Back to top |
|
|
|
|
