Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Usage of like between columns with wild card entries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Mon Jun 22, 2009 8:03 pm    Post subject: Usage of like between columns with wild card entries
Reply with quote

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:
Code:

Col2   
-----   
A%%
C1%


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
View user's profile Send private message

vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Mon Jun 22, 2009 8:05 pm    Post subject:
Reply with quote

Please note that we are using 'DB2 version 7.2'

Thanks,
Balu
Back to top
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Wed Jun 24, 2009 9:42 am    Post subject:
Reply with quote

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 icon_smile.gif

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Jun 24, 2009 12:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Wed Jun 24, 2009 1:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Jun 24, 2009 1:56 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vvmanyam

New User


Joined: 16 Apr 2008
Posts: 86
Location: Bangalore

PostPosted: Wed Jun 24, 2009 2:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts )SEL and )ENDSEL usage cvnlynn TSO/ISPF 4 Thu Jun 15, 2017 1:15 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us