Query

vishal_m10 · #1 (**permalink**) 07-05-10, 12:02

I have two tables- A and B. I need to check if a particular module exist in table A then need to pick module-id from A. means

select a.mdl-id
from A
where mdl = 'xxx'

in table B I have a string value under a column say C. The mdl-id from above query comes somewhere in between the string and If a match found I need another sec-id (obviousl another column in table B). keyword like is not working here because I couldnt make a query that can take dynamic LIKE.
what I written is-

select a.mdl, a.mdl-id, b.sec-id
from A a, B b
where a.mdl = 'XXX'
and b.C like '%a.mdl-id%

But its not working. Please help....

Thanks a lot!!

tonkuma · #2 (**permalink**) 07-05-10, 12:55

How about this?

Code:

SELECT a.mdl , a.mdl_id
     , b.sec_id 
  FROM A a
     , B b
 WHERE a.mdl = 'XXX'
   AND LOCATE(a.mdl_id , b.C) > 0
;

vishal_m10 · #3 (**permalink**) 07-06-10, 05:33

Thanks tonkuma.....

I tried suggested query but its not giving me any rows. I forgot to mention but actually I need to search in multiple databsaes....

select a.AVLB_RULE_MDL_ID, a.AVLB_RULE_ID , b.AVLB_FRML_TX
from &db.AVLB_RULE a,
&db.AVLB b
where a.AVLB_RULE_MDL_ID in ('BA4OX321', ' BA4OX344')
AND LOCATE ('a.AVLB_RULE_ID', b.AVLB_FRML_TX) > 0

Any suggestions???

Thanks again

tonkuma · #4 (**permalink**) 07-06-10, 08:09

Remove quotation marks around a.AVLB_RULE_ID in LOCATE function, like:

Code:

select a.AVLB_RULE_MDL_ID , a.AVLB_RULE_ID
     , b.AVLB_FRML_TX
  from &db.AVLB_RULE a
     , &db.AVLB      b
 where a.AVLB_RULE_MDL_ID
       in ('BA4OX321', ' BA4OX344') 
   AND LOCATE (a.AVLB_RULE_ID, b.AVLB_FRML_TX) > 0

If it would not work, please show DDL, sample data and expected result.

I couldn't understand &db.,
because ampersand("&") outside quotation marks in DB2 SQL will result an error(invalid character).

vishal_m10 · #5 (**permalink**) 07-06-10, 08:51

Actually we have some customized tool in our organisation and &db denotes All Databases.

Second thing when I try to run it without quotation marks around a.AVLB_RULE_ID I got http://www.dbforums.com/attachment.p...1&d=1278420548 error.

and when I ran it with quotes then no result but obviously there is data present on the tables. Any other suggestions?

Thank you!!

tonkuma · #6 (**permalink**) 07-06-10, 09:00

I wrote:

Quote:

If it would not work, please show DDL, sample data and expected result.

What is data type of a.AVLB_RULE_ID?

Lenny77 · #7 (**permalink**) 07-06-10, 10:20

More universal:

Code:

select a.AVLB_RULE_MDL_ID , a.AVLB_RULE_ID
     , b.AVLB_FRML_TX
  from &db.AVLB_RULE a
     , &db.AVLB      b
 where a.AVLB_RULE_MDL_ID
       in ('BA4OX321', ' BA4OX344') 
   AND LOCATE ( char(a.AVLB_RULE_ID), char(b.AVLB_FRML_TX) ) > 0

Lenny

tonkuma · #8 (**permalink**) 07-06-10, 10:48

If data type of a.AVLB_RULE_ID was numeric,
it may be better to use RTRIM, like this:
LOCATE ( RTRIM( CHAR(a.AVLB_RULE_ID) ) , b.AVLB_FRML_TX )

Because, the result of CHAR is padded with blanks.

For example:
If data type of a.AVLB_RULE_ID was INTEGER, length of the result is 11.
And if the value was 1, then the result of CHAR(a.AVLB_RULE_ID) would be

Code:

 '1          '

('1' padded with 10 blanks).

Lenny77 · #9 (**permalink**) 07-06-10, 11:00

Quote:

Originally Posted by tonkuma

If data type of a.AVLB_RULE_ID was numeric,
it may be better to use RTRIM, like this:
LOCATE ( RTRIM( CHAR(a.AVLB_RULE_ID) ) , b.AVLB_FRML_TX )

Because, the result of CHAR is padded with blanks.

For example:
If data type of a.AVLB_RULE_ID was INTEGER, length of the result is 11.
And if the value was 1, then the result of CHAR(a.AVLB_RULE_ID) would be

Code: