Is there a better way to write this SQL?

Question

SELECT DISTINCT
     A.currency_code AS currency_code,
     NVL (B.Acct_ID, A.Acct_ID) SECND_Acct_ID,
     NVL (B.Type_currency_code, A.Type_currency_code) SECND_Type_currency_code,
     NVL (B.Seg_Type, A.Seg_Type) SECND_Seg_Type,
     NVL (B.B_Func_Code, A.B_Func_Code) SECND_B_Func_Code,
     A.Acct_ID AS PRIM_Acct_ID,
     A.Type_currency_code AS PRIM_Type_currency_code,
     A.Seg_Type AS PRIM_Seg_Type,
     A.B_Func_Code AS PRIM_B_FuncCode
FROM    (SELECT Acct_ID,
           Type_currency_code,
           Seg_Type,
           B_Func_Code,
           B_ID,
           B_NBR,
           currency_code
      FROM BAU
     WHERE P_A_IND IN ('Y')) A
LEFT OUTER JOIN
   (SELECT Acct_ID,
           Type_currency_code,
           Seg_Type,
           B_Func_Code,
           B_ID,
           B_NBR,
           currency_code
      FROM BAU
     WHERE P_A_IND IN ('N')) B
ON     A.B_NBR = B.B_NBR
   AND A.B_ID = B.B_ID
   AND A.currency_code = B.currency_code

• Purpose is to identify the distinct primary and secondary ( PRIM_Acct_ID, PRIM_Type_currency_code, PRIM_Seg_Type, PRIM_B_FuncCode) columns on the same table.
• The ones which have p_a_ind ( primary indicator ) as Y is primary, else secondary.
• b_nbr, b_id, and currency_code is the primary key where I did a self-join.

I would like to know/see if there is a better way of writing this query.

Answer 1

You could clarify the query by moving the conditions from subqueries to the where or the on clause, respectively:

SELECT  ...
FROM    BAU A
LEFT JOIN
        BAU B
ON      B.P_A_IND IN ('N')
        AND A.B_NBR = B.B_NBR
        AND A.B_ID = B.B_ID
        AND A.currency_code = B.currency_code
WHERE   A.P_A_IND IN ('Y')