问题描述:

In the below query, I'm using Subquery1 and Subquery2 to get Account Number and Account Name. However the first Subquery and second joins same tables except an additional table account_nameinfo_t in Subquery 2 to get the account name. Is there a way I avoid selecting from other tables and just use the value of Subquery 1 i.e account# to get the account name in Subquery 2?

SELECT

(

SELECT acct.account_no

FROM group_t grp1, account_t acct

WHERE grp1.poid_id0 = grpbm.obj_id0

AND acct.poid_id0 = grp1.ACCOUNT_OBJ_ID0

) PARENT_ACCOUNT, -- (#Subquery 1 to get the parent account)

(

SELECT ant.Firstname || ' ' || ant.LastName

FROM group_t grp1, account_t acct,account_nameinfo_t ant

WHERE grp1.poid_id0 = grpbm.obj_id0

AND acct.poid_id0 = grp1.ACCOUNT_OBJ_ID0

AND ant.obj_id0 = acct.poid_id0

) "ACCOUNT NAME", -- (#Subquery 2 to get the parent account name which is in a different table)

bgs.REC_ID2 RECORD_TYPE,

bgs.current_bal VALUE

FROM group_t grp,

group_billing_members_t grpbm,

BAL_GRP_SUB_BALS_T bgs

WHERE poid_type = '/group/sharing/discounts'

AND grpbm.OBJECT_ID0 = grp.ACCOUNT_OBJ_ID0

AND bgs.obj_id0 = grp.BAL_GRP_OBJ_ID0

AND bgs.rec_id2 NOT IN (1000203,

1030001,

1000303,

1000306)

ORDER BY PARENT_ACCOUNT;

网友答案:

It looks like you can simplify this using simple joins rather than subqueries, either in the select list or as inline views:

SELECT acct.account_no AS "PARENT ACCOUNT",
  ant.first_name||' '||ant.last_name AS "ACCOUNT NAME",
  bgs.rec_id2 AS record_type,
  bgs.current_bal
FROM group_t grp
JOIN group_billing_members_t grpbm ON grpbm.obj_id0 = grp.account_obj_id0
JOIN group_t grp1 ON grp1.poid_id0 = grpbm.obj_id0
JOIN bal_grp_sub_bals_t bgs ON bgs.obj_id0 = grp.bal_grp_obj_id0
JOIN account_t acct ON acct.poid_id0 = grp1.account_obj_id0
JOIN account_nameinfo_t ant ON ant.obj_id0 = acct.poid_id0
WHERE grp.poid_type='/group/sharing/discounts'
AND bgs.rec_id2 not in (1000203, 1030001, 1000303, 1000306)
AND ant.rec_id = 1
ORDER BY "PARENT ACCOUNT";

You only seem to be using group_billing_members_t between two references to group_t, and it isn't clear if they both point to the same record, or if that expands to multiple rows. The column names seem a bit inconsistent, which may be from your retyping the code rather than copying and pasting it. If it is the same record then you seem to be able to remove that table and the rejoin:

SELECT acct.account_no AS "PARENT ACCOUNT",
  ant.first_name||' '||ant.last_name AS "ACCOUNT NAME",
  bgs.rec_id2 AS record_type,
  bgs.current_bal
FROM group_t grp
JOIN bal_grp_sub_bals_t bgs ON bgs.obj_id0 = grp.bal_grp_obj_id0
JOIN account_t acct ON acct.poid_id0 = grp.account_obj_id0
JOIN account_nameinfo_t ant ON ant.obj_id0 = acct.poid_id0
WHERE grp.poid_type='/group/sharing/discounts'
AND bgs.rec_id2 not in (1000203, 1030001, 1000303, 1000306)
AND ant.rec_id = 1
ORDER BY "PARENT ACCOUNT";

Without table structures, relationships, sample data and expected results that's rather speculative though.

网友答案:

Oracle supports a WITH clause which you may find useful: http://psoug.org/reference/with.html

Essentially, it allows you to create a temporary view within a query that can be accessed multiple times. In your case, the result of your common join can be "factored out" and the result can be reused.

网友答案:

As ALEXPOOL suggested here is what i tried with ANSI joins and it works. Any betterment is welcome.

SELECT C1."PARENT ACCOUNT",C1."ACCOUNT NAME", A1.RECORD_TYPE, A1.CURRENT_BAL
FROM 
    (SELECT  
      bgs.REC_ID2 RECORD_TYPE,
      bgs.current_bal,grpbm.OBJ_ID0
    FROM  group_t grp ,
          group_billing_members_t grpbm,
          BAL_GRP_SUB_BALS_T bgs
        WHERE
        poid_type='/group/sharing/discounts'   and
      grpbm.OBJECT_ID0 = grp.ACCOUNT_OBJ_ID0 and
      bgs.obj_id0 = grp.BAL_GRP_OBJ_ID0      and
      bgs.rec_id2 not in (                    1000203,
                        1030001,
                            1000303,
                            1000306) ) A1 JOIN

  ( SELECT grp1.ACCOUNT_OBJ_ID0,grp1.POID_ID0
     FROM group_t grp1
  )  B1 ON (B1.poid_id0 = A1.OBJ_ID0)
  JOIN 
  ( SELECT acct.account_no "PARENT ACCOUNT",
            ant.FIRST_NAME||' '||ant.LAST_NAME "ACCOUNT NAME",
            acct.poid_id0 
     FROM 
        account_t acct,
        account_nameinfo_t ant  
        WHERE acct.poid_id0 = ant.obj_id0 and 
              ant.rec_id=1) C1 ON (C1.poid_id0 = B1.ACCOUNT_OBJ_ID0)
  order by C1."PARENT ACCOUNT";
相关阅读:
Top