Results 1 to 11 of 11

Thread: Query

  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Query

    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!!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).

  5. #5
    Join Date
    Jul 2010
    Posts
    4
    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!!
    Attached Thumbnails Attached Thumbnails 7-6-2010 6-18-21 PM.png  

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wrote:
    If it would not work, please show DDL, sample data and expected result.
    What is data type of a.AVLB_RULE_ID?

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Angry

    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).
    Last edited by tonkuma; 07-06-10 at 11:56. Reason: Add "For example: ..."

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    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).
    This is better than mine.

    Lenny

  10. #10
    Join Date
    Jul 2010
    Posts
    4
    Thanks a loooooooooooootttt tonkuma and Lenny..... finally I got the results...I really appreciate your help....

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool

    Quote Originally Posted by vishal_m10 View Post
    Thanks a loooooooooooootttt tonkuma and Lenny..... finally I got the results...I really appreciate your help....
    No problem !

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •