Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Unanswered: Subquery in the select clause

    Hi guys im trying to run this query below but im getting this error "
    ORA-01427: single-row subquery returns more than one row"

    Select
    HB.HB_NO "House Number",
    (Select customer_ref
    from cust_ref
    where type = 'INO') AS "INO"
    from TSL.HB HB, TSL.Cust_reference CUST
    WHERE client_code = 1021254
    AND HB.ID_NO = CUST.ID_NO
    AND HB.BRANCH_CD = CUST.BRANCH_CD
    AND HB.MODE= CUST.MODE

    can somebody help im new to Oracle

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    Select customer_ref
    from cust_ref
    where type = 'INO'
    should return only record (but doesn't).

  3. #3
    Join Date
    Nov 2006
    Posts
    11
    Oops!! sorry for not being clear here the reason why im using the subquery it's becoz i will have more subqueries in the select clause. check the query that i wanna write:

    Select
    HB.HB_NO "Housebill Number",
    (Select customer_ref
    from cust_reference
    where ref_type = 'INO') "INO",
    (Select customer_ref
    from cust_reference
    where ref_type = 'EXF') "EXF",
    (Select customer_ref
    from cust_reference
    where ref_type = 'UTA') "UTA"
    from TSL.HB HB, TSL.Cust_reference CUST
    WHERE client_code = 1021254
    AND HB.ID_NO = CUST.ID_NO
    AND HB.BRANCH_CD = CUST.BRANCH_CD
    AND HB.MODE= CUST.MODE

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, but you can't do that unless there is only one 'INO' customer in the cust_reference table, only one 'EXF' customer, only one 'UTA' customer.

    Presumably you don't expect that there will be, and you thought the syntax meant something different to what it actually does.

    What is it that you actually want to achieve?

  5. #5
    Join Date
    Mar 2008
    Posts
    1
    Quote Originally Posted by T'zozo
    Oops!! sorry for not being clear here the reason why im using the subquery it's becoz i will have more subqueries in the select clause. check the query that i wanna write:

    Select
    HB.HB_NO "Housebill Number",
    (Select customer_ref
    from cust_reference
    where ref_type = 'INO') "INO",
    (Select customer_ref
    from cust_reference
    where ref_type = 'EXF') "EXF",
    (Select customer_ref
    from cust_reference
    where ref_type = 'UTA') "UTA"
    from TSL.HB HB, TSL.Cust_reference CUST
    WHERE client_code = 1021254
    AND HB.ID_NO = CUST.ID_NO
    AND HB.BRANCH_CD = CUST.BRANCH_CD
    AND HB.MODE= CUST.MODE
    Hi T'zozo, maybe you are not clear about the error. The meaning of return more than one row is that your one or more of your subquery is returning more than one row. If you want to use this query structure, you have to make sure that the subqueries of "INO", "EXF", or "UTA" is returning just one row.

    I suggest that you add more statement in your subqueries' where clauses to make sure that your subqueries is returning just one row.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Of course,
    Code:
    WHERE ROWNUM = 1
    would do the job (but I sincerely doubt that this is the solution).

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Let me add my part of insight into this case.

    Analyzing the query: in the main query you join HB with CUST_REFERENCE, however you do not use any column from CUST_REFERENCE. You just join again the CUST_REFERENCE in the nested subqueries, but without any join condition to the HB table, so taking all rows with given REF_TYPE.

    You could try to remove the CUST_REFERENCE from the main query and move the join condition to all nested subqueries.

    It seems like some kind of pivoting; you could try aggregation like
    Code:
    SELECT HB.HB_NO "Housebill Number",
    MAX(CASE WHEN ref_type = 'INO' THEN customer_ref END) "INO",
    MAX(CASE WHEN ref_type = 'EXF' THEN customer_ref END) "EXF",
    MAX(CASE WHEN ref_type = 'UTA' THEN customer_ref END) "UTA"
    from TSL.HB HB, TSL.Cust_reference CUST
    WHERE client_code = 1021254
      AND HB.ID_NO = CUST.ID_NO
      AND HB.BRANCH_CD = CUST.BRANCH_CD
      AND HB.MODE= CUST.MODE
    GROUP BY HB.HB_NO;
    Just note, that in case there are more rows in CUSTOMER_REFERENCE with the same REF_TYPE linked to one row in HB.HB_NO, you will get error in the re-worked query too. In the second query, you will not get error, however it will show you only one CUSTOMER_REF, although there may be more of them in CUST_REFERENCE.

    As you did not posted table structures with its constraints (primary and foreign keys, unique constraints), it is hard to say more.

    [Edit: In 4th paragraph (before the query), USE was replaced with TRY, seems more appropriate]

Posting Permissions

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