If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Pattern Match in a 'like' clause

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2005
Posts: 127
Pattern Match in a 'like' clause

My cursor's Select query has the following like clause in its WHERE clause, but the procedure failed to compile, because of this like clause.

Code:
SELECT table1.colid,  col3 	FROM   table1, table3
	WHERE    table1.colid like 'table3.colid'
	ORDER BY table1.colid;
here the colid of both the table are of type varchar(2)

But DB2 allows only string constants and not a column value in the pattern match of a like clause.
(ie. the above query works fine if the WHERE clause is, table1.colid like 'aa')

could any one suggest me on how to use the column value here in the pattern match of the like clause..

Regards
Sn
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
Try LOCATE() or SUBSTR() functions
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2005
Posts: 127
Quote:
Originally Posted by n_i
Try LOCATE() or SUBSTR() functions

i dint find SUBSTRING() in DB2 .. and i think LOCATE will not replace the table column as the exact equivalent.. kindly explain me with an example if i am wrong.

thanks
Sn
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2005
Posts: 273
might be something like this works:

SELECT table1.colid, col3
FROM table1, table3

WHERE SUBSTR(table3.colid,1,1) in (SUBSTR(table1.colid,1,1),'_','%')
AND SUBSTR(table3.colid,2,1) in (SUBSTR(table1.colid,2,1),'_','%')

ORDER BY table1.colid;
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2005
Posts: 127
I tried your suggestion with SUBSTR umayer, but the query fails in execution throwing the following exeception :

No authorized routine named 'SUBSTR' of type 'FUNCTION' having compatible arguments is found.
SQL State : 42884


Pls note that my DB 2 version is 8.1.3 and it runs on Windows 2000

thanks
Sn
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
If you just want to match the two COLID columns, and both are VARCHAR(2), what's wrong with
Code:
SELECT table1.colid,  col3
FROM   table1, table3
WHERE  table1.colid = table3.colid
ORDER BY table1.colid;
(or am I misunderstanding your problem)?
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On