问将两个函数查询组合在一起时出现SQL0338N错误

EN

--Runs without the BRAND table added 8 lines down fine
SELECT 'B2C6M6' AS COMBO, SITE
FROM (
  select site.ROWID_OBJECT AS SITE, l1.CHAIN_IND AS UP, NVL(NVL(NVL(NVL(SUBSTR(L5.PARTY_LVL,2,1),SUBSTR(L4.PARTY_LVL,2,1)),SUBSTR(L3.PARTY_LVL,2,1)),SUBSTR(L2.PARTY_LVL,2,1)),SUBSTR(L1.PARTY_LVL,2,1)) AS HIGH_DUMMY, COUNT(L1.ROWID_OBJECT)
  from C_PARTY as site, C_REL_PARTY as sb, C_PARTY as bca, C_PARTY as cg, C_REL_PARTY as cl, C_PARTY as l1,



------------VVV Runs on it's own fine VVV------------
    (SELECT TAB2.BRAND, TAB2.L1_UP, TAB2.L1
    FROM (
    (select L5.PARTY_NM3_CLS as BRAND, L5.ROWID_OBJECT as BRAND_NODE, L5.PARTY_LVL BRAND_LVL, L5.IS_DUMMY NODE_DUMMY, L1.ROWID_OBJECT as L1, L1.PARTY_NM1_CLS AS L1_NAME, L1.CHAIN_IND AS L1_UP, L1.IS_DUMMY AS L1_DUMMY
    FROM C_PARTY AS L5, C_REL_PARTY AS L54, C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, 
    C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
    WHERE L5.ROWID_OBJECT = L54.PARENT_ID AND L4.ROWID_OBJECT = L54.CHILD_ID AND L54.HUB_STATE_IND = '1' AND L54.DELETED_IND IS NULL
    AND L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
    AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
    AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
    AND L5.PARTY_NM3_CLS IS NOT NULL AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' or L5.BO_CLASS_CODE = 'Ultimate Parent')
    AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node') AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node')
    AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node')
    )UNION(
    select L4.PARTY_NM3_CLS as BRAND, L4.ROWID_OBJECT as BRAND_NODE, L4.PARTY_LVL BRAND_LVL, L4.IS_DUMMY NODE_DUMMY, L1.ROWID_OBJECT as L1, L1.PARTY_NM1_CLS AS L1_NAME, L1.CHAIN_IND AS L1_UP, L1.IS_DUMMY AS L1_DUMMY
    FROM C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
    WHERE L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
    AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
    AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
    AND L4.PARTY_NM3_CLS IS NOT NULL AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node' OR L4.PARTY_TYPE_ID = '1' or L4.BO_CLASS_CODE = 'Ultimate Parent')
    AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node') AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') 
    AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node') AND L4.ROWID_OBJECT NOT IN (
      select L4.ROWID_OBJECT
      FROM C_PARTY AS L5, C_REL_PARTY AS L54, C_PARTY AS L4
      WHERE L5.ROWID_OBJECT = L54.PARENT_ID AND L4.ROWID_OBJECT = L54.CHILD_ID AND L54.HUB_STATE_IND = '1' AND L54.DELETED_IND IS NULL
      AND L5.PARTY_NM3_CLS IS NOT NULL AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' 
      or L5.BO_CLASS_CODE = 'Ultimate Parent') AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node'))
    )UNION(
    select L3.PARTY_NM3_CLS as BRAND, L3.ROWID_OBJECT as BRAND_NODE, L3.PARTY_LVL BRAND_LVL, L3.IS_DUMMY NODE_DUMMY, L1.ROWID_OBJECT as L1, L1.PARTY_NM1_CLS AS L1_NAME, L1.CHAIN_IND AS L1_UP, L1.IS_DUMMY AS L1_DUMMY
    FROM C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
    WHERE L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
    AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
    AND L3.PARTY_NM3_CLS IS NOT NULL AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node' OR L3.PARTY_TYPE_ID = '1' or L3.BO_CLASS_CODE = 'Ultimate Parent')
    AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node') 
    AND L3.ROWID_OBJECT NOT IN (
      (select L3.ROWID_OBJECT
      FROM C_PARTY AS L5, C_REL_PARTY AS L54, C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3
      WHERE L5.ROWID_OBJECT = L54.PARENT_ID AND L4.ROWID_OBJECT = L54.CHILD_ID AND L54.HUB_STATE_IND = '1' AND L54.DELETED_IND IS NULL
      AND L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L5.PARTY_NM3_CLS IS NOT NULL AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' or L5.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node') AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node')
      )UNION(
      select L3.ROWID_OBJECT
      FROM C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3
      WHERE L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L4.PARTY_NM3_CLS IS NOT NULL AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node' OR L4.PARTY_TYPE_ID = '1' 
      or L4.BO_CLASS_CODE = 'Ultimate Parent') AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node')))
    )UNION(
    select L2.PARTY_NM3_CLS as BRAND, L2.ROWID_OBJECT as BRAND_NODE, L2.PARTY_LVL BRAND_LVL, L2.IS_DUMMY NODE_DUMMY, L1.ROWID_OBJECT as L1, L1.PARTY_NM1_CLS AS L1_NAME, L1.CHAIN_IND AS L1_UP, L1.IS_DUMMY AS L1_DUMMY
    FROM C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
    WHERE L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
    AND L2.PARTY_NM3_CLS IS NOT NULL AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') 
    AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node') AND L2.ROWID_OBJECT NOT IN (
      (select L2.ROWID_OBJECT
      FROM C_PARTY AS L5, C_REL_PARTY AS L54, C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, 
      C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
      WHERE L5.ROWID_OBJECT = L54.PARENT_ID AND L4.ROWID_OBJECT = L54.CHILD_ID AND L54.HUB_STATE_IND = '1' AND L54.DELETED_IND IS NULL
      AND L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
      AND L5.PARTY_NM3_CLS IS NOT NULL AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' or L5.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node') AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node')
      AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node')
      )UNION(
      select L2.ROWID_OBJECT
      FROM C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2
      WHERE L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L4.PARTY_NM3_CLS IS NOT NULL AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node' OR L4.PARTY_TYPE_ID = '1' or L4.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node') AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') 
      )UNION(
      select L2.ROWID_OBJECT
      FROM C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2
      WHERE L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L3.PARTY_NM3_CLS IS NOT NULL AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node' OR L3.PARTY_TYPE_ID = '1' 
      or L3.BO_CLASS_CODE = 'Ultimate Parent') AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node'))) 
    )UNION(
    select L1.PARTY_NM3_CLS as BRAND, L1.ROWID_OBJECT as BRAND_NODE, L1.PARTY_LVL BRAND_LVL, L1.IS_DUMMY NODE_DUMMY, L1.ROWID_OBJECT as L1, L1.PARTY_NM1_CLS AS L1_NAME, L1.CHAIN_IND AS L1_UP, L1.IS_DUMMY AS L1_DUMMY
    FROM C_PARTY AS L1
    WHERE L1.PARTY_NM3_CLS is not null and (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node') AND L1.ROWID_OBJECT NOT IN (
      (select L1.ROWID_OBJECT
      FROM C_PARTY AS L5, C_REL_PARTY AS L54, C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, 
      C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
      WHERE L5.ROWID_OBJECT = L54.PARENT_ID AND L4.ROWID_OBJECT = L54.CHILD_ID AND L54.HUB_STATE_IND = '1' AND L54.DELETED_IND IS NULL
      AND L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
      AND L5.PARTY_NM3_CLS IS NOT NULL AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' or L5.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node') AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node')
      AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node')
      )UNION(
      select L1.ROWID_OBJECT
      FROM C_PARTY AS L4, C_REL_PARTY AS L43, C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
      WHERE L4.ROWID_OBJECT = L43.PARENT_ID AND L3.ROWID_OBJECT = L43.CHILD_ID AND L43.HUB_STATE_IND = '1' AND L43.DELETED_IND IS NULL
      AND L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
      AND L4.PARTY_NM3_CLS IS NOT NULL AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node' OR L4.PARTY_TYPE_ID = '1' or L4.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node') AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') 
      AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node')
      )UNION(
      select L1.ROWID_OBJECT
      FROM C_PARTY AS L3, C_REL_PARTY AS L32, C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
      WHERE L3.ROWID_OBJECT = L32.PARENT_ID AND L2.ROWID_OBJECT = L32.CHILD_ID AND L32.HUB_STATE_IND = '1' AND L32.DELETED_IND IS NULL
      AND L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
      AND L3.PARTY_NM3_CLS IS NOT NULL AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node' OR L3.PARTY_TYPE_ID = '1' or L3.BO_CLASS_CODE = 'Ultimate Parent')
      AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node')
      )UNION(
      select L1.ROWID_OBJECT
      FROM C_PARTY AS L2, C_REL_PARTY AS L21, C_PARTY AS L1
      WHERE L2.ROWID_OBJECT = L21.PARENT_ID AND L1.ROWID_OBJECT = L21.CHILD_ID AND L21.HUB_STATE_IND = '1' AND L21.DELETED_IND IS NULL
      AND L2.PARTY_NM3_CLS IS NOT NULL AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') 
      AND (L1.PARTY_TYPE_ID = '6' or L1.BO_CLASS_CODE = 'Base Customer Node'))
    ))) AS TAB2
    WHERE TAB2.BRAND_LVL = 'L1') as BRAND
------------^^^ Runs on it's own fine ^^^-----------



---------Rest of line 1 query that runs find---------
LEFT JOIN C_REL_PARTY L12 ON l1.ROWID_OBJECT = L12.CHILD_ID AND L12.DELETED_IND IS NULL AND L12.HUB_STATE_IND = '1'
LEFT JOIN C_PARTY L2 ON L12.PARENT_ID = L2.ROWID_OBJECT AND (L2.PARTY_TYPE_ID = '6' or L2.BO_CLASS_CODE = 'Base Customer Node') AND L2.IS_DUMMY = 'Y'
LEFT JOIN C_REL_PARTY L23 ON L2.ROWID_OBJECT = L23.CHILD_ID AND L23.DELETED_IND IS NULL AND L23.HUB_STATE_IND = '1'
LEFT JOIN C_PARTY L3 ON L23.PARENT_ID = L3.ROWID_OBJECT AND (L3.PARTY_TYPE_ID = '6' or L3.BO_CLASS_CODE = 'Base Customer Node' OR L3.PARTY_TYPE_ID = '1' or L3.BO_CLASS_CODE = 'Ultimate Parent') AND L3.IS_DUMMY = 'Y'
LEFT JOIN C_REL_PARTY L34 ON L3.ROWID_OBJECT = L34.CHILD_ID AND L34.DELETED_IND IS NULL AND L34.HUB_STATE_IND = '1'
LEFT JOIN C_PARTY L4 ON L34.PARENT_ID = L4.ROWID_OBJECT AND (L4.PARTY_TYPE_ID = '6' or L4.BO_CLASS_CODE = 'Base Customer Node' OR L4.PARTY_TYPE_ID = '1' or L4.BO_CLASS_CODE = 'Ultimate Parent') AND L4.IS_DUMMY = 'Y'
LEFT JOIN C_REL_PARTY L45 ON L4.ROWID_OBJECT = L45.CHILD_ID AND L45.DELETED_IND IS NULL AND L45.HUB_STATE_IND = '1'
LEFT JOIN C_PARTY L5 ON L45.PARENT_ID = L5.ROWID_OBJECT AND (L5.PARTY_TYPE_ID = '6' or L5.BO_CLASS_CODE = 'Base Customer Node' OR L5.PARTY_TYPE_ID = '1' or L5.BO_CLASS_CODE = 'Ultimate Parent') AND L5.IS_DUMMY = 'Y'
where (site.PARTY_TYPE_ID = '4' or site.BO_CLASS_CODE = 'Site') and (bca.PARTY_TYPE_ID = '20001' or bca.BO_CLASS_CODE = 'Base Customer Account')
and (cg.PARTY_TYPE_ID = '7' or cg.BO_CLASS_CODE = 'Customer Group') and (l1.PARTY_TYPE_ID = '6' or l1.BO_CLASS_CODE = 'Base Customer Node')
and site.ROWID_OBJECT = sb.PARENT_ID and bca.ROWID_OBJECT = sb.CHILD_ID
and l1.ROWID_OBJECT = cl.PARENT_ID and cg.ROWID_OBJECT = cl.CHILD_ID
and bca.SOURCE_ID = cg.SOURCE_ID and (
  ( bca.act_grp_type1 = cg.ACT_GRP_TYPE1 and bca.act_grp_field1 = cg.ACT_GRP_FIELD1 )
  or ( bca.act_grp_type2 = cg.ACT_GRP_TYPE1 and bca.act_grp_field2 = cg.ACT_GRP_FIELD1 )
  or ( bca.act_grp_type3 = cg.ACT_GRP_TYPE1 and bca.act_grp_field3 = cg.ACT_GRP_FIELD1 )
  or ( bca.act_grp_type4 = cg.ACT_GRP_TYPE1 and bca.act_grp_field4 = cg.ACT_GRP_FIELD1 )
  or ( bca.act_grp_type5 = cg.ACT_GRP_TYPE1 and bca.act_grp_field5 = cg.ACT_GRP_FIELD1 ))
and site.party_nm3_cls = brand.brand and l1.chain_ind = brand.l1_up and l1.rowid_object <> BRAND.L1
GROUP BY SITE.ROWID_OBJECT, l1.CHAIN_IND, NVL(NVL(NVL(NVL(SUBSTR(L5.PARTY_LVL,2,1),SUBSTR(L4.PARTY_LVL,2,1)),SUBSTR(L3.PARTY_LVL,2,1)),SUBSTR(L2.PARTY_LVL,2,1)),SUBSTR(L1.PARTY_LVL,2,1))
HAVING count(case when l1.IS_DUMMY = 'N' then 1 end) = 0 AND count(case when l1.IS_DUMMY = 'Y' then 1 end) > 1) AS TAB1
GROUP  BY SITE
HAVING COUNT(UP) = 1 AND COUNT(SITE) > 1
ORDER BY RAND()
FETCH FIRST 1 ROWS ONLY;