Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Red face Unanswered: Subquery join using concatenation

    I'm trying to join a subquery by the ENTITY_NO but the inner select concatenates '_01'. Is this possible?

    So it should be returning 132509_01 but it's returning no rows.


    SELECT A.* --ENTITY_NO IS 132509_01
    FROM fc_uat.WBS_ENTITIES A
    WHERE A.OBJECT_TYPE = 'financial_trx'
    AND A.ENTITY_NO IN
    (SELECT SUBSTR(B.ENTITY_NO, 1, 6) || '_01' ENTITY_NO
    FROM FC_UAT.WBS_ENTITIES B
    WHERE B.ENTITY_NO LIKE '132509_%'
    AND B.OBJECT_TYPE = 'finance_request')
    /* SHOULD RETURN 1 ROW for ENTITY_NO 132509_01 */

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    WHERE B.ENTITY_NO LIKE '132509%' -- might work (better)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2007
    Posts
    84
    That still doesn't return the data for the outer join. It's not recognizing the
    SUBSTR(B.ENTITY_NO, 1, 6) || '_01' ENTITY_NO. But when I run the inner join it returns:

    132509_01

  4. #4
    Join Date
    Feb 2007
    Posts
    84
    That still doesn't return the data for the outer join. It's not recognizing the
    SUBSTR(B.ENTITY_NO, 1, 6) || '_01' ENTITY_NO. But when I run the inner join it returns:

    132509_01

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Are you sure that there are BOTH records in a table which look like this:
    Code:
    entity_no  object_type
    ---------  ---------------
    132509_01  finance_request
    132509_01  financial_trx
    If any of them is missing, query will return no rows.

  6. #6
    Join Date
    Feb 2007
    Posts
    84
    The outer query of:
    SELECT A.ENTITY_NO
    FROM fc_uat.WBS_ENTITIES A
    WHERE A.OBJECT_TYPE = 'financial_trx'
    AND A.ENTITY_NO IN ('132509_01')

    Returns 132509_01.

    The inner subquery of:
    SELECT B.ENTITY_NO
    FROM FC_UAT.WBS_ENTITIES B
    WHERE B.ENTITY_NO LIKE '132509%'
    AND B.OBJECT_TYPE = 'finance_request'

    Returns 132509.

    I'm trying to concatenate the "_01" to the inner subquery so it joins the subquery. If I cannot do it this way, do you know of a way to join the queries. The problem is the ENTITY_NO for the OBJECT_TYPE "finance_request" is 6 chars. But the ENTITY_NO for OBJECT_TYPE "financial_trx" has 6 chars + "_01" or "_02" or "_03" etc...

    Somehow I need to join these together. Right now I also have a Union statement joining them. But the "finance_request" OBJECT_TYPE has a date criteria, which works correctly. But it returns all of the data for the "financial_trx" OBJECT_TYPE and it should return only the rows that relate to the "finance_request" OBJECT_TYPE ENTITY_NOs.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I have that strange feeling that the solution is very simple, but we are just blindly guessing around.

    OK, here is my test case:
    Code:
    SQL> CREATE TABLE wbs_entities (entity_no VARCHAR2(20), object_type VARCHAR2(20));
    
    Table created.
    
    SQL> INSERT ALL
      2    INTO wbs_entities VALUES ('132509_01', 'financial_trx')
      3    INTO wbs_entities VALUES ('132509'   , 'finance_request')
      4  SELECT * FROM dual;
    
    2 rows created.
    
    SQL> SELECT a.entity_no
      2    FROM wbs_entities a
      3   WHERE a.object_type = 'financial_trx' AND a.entity_no IN ('132509_01');
    
    ENTITY_NO
    --------------------
    132509_01
    
    SQL> SELECT b.entity_no
      2    FROM wbs_entities b
      3   WHERE b.entity_no LIKE '132509%' AND b.object_type = 'finance_request';
    
    ENTITY_NO
    --------------------
    132509
    Finally, your query which - as you've said - does not work:
    Code:
    SQL> SELECT a.*
      2    FROM wbs_entities a
      3   WHERE a.object_type = 'financial_trx'
      4     AND a.entity_no IN (
      5            SELECT b.entity_no || '_01'
      6              FROM wbs_entities b
      7             WHERE b.entity_no LIKE '132509%'
      8               AND b.object_type = 'finance_request');
    
    ENTITY_NO            OBJECT_TYPE
    -------------------- --------------------
    132509_01            financial_trx
    So, what is wrong with that (if anything)?

    BTW, if length(entity_no) [where object_type = 'finance_request'] = 6, why do you need a SUBSTR function? I kicked it out.

Posting Permissions

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