SELECT   10000 AS parentindicator, NULL AS companyname, templatetype, seqnum,
         NAME, description, beneficiaryname, trxtype, companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates
   WHERE accountid IS NULL AND companyid = 20020423000007 AND templatetype IN ('2')
UNION ALL
SELECT   (SELECT COUNT (*)
            FROM company com
           WHERE a.accountid IS NOT NULL
             AND a.accountid = b.bankaccountid
             AND b.companyid = com.parentcompanyid) AS parentindicator,
         c.companyname AS companyname, templatetype, seqnum, NAME,
         description, beneficiaryname, trxtype, a.companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates a, bankaccount b, company c
   WHERE a.accountid IS NOT NULL
     AND a.accountid = b.bankaccountid
     AND b.companyid = c.companyid
     AND a.companyid = 20020423000007
     AND templatetype IN ('2')
ORDER BY parentindicator DESC, companyname, lastupdatedate DESC

Explain Plan
==============
Operation				Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
-----------------------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=CHOOSE			3 K	 	1560  	 	      	             	 
  SORT ORDER BY						3 K	309 K	1476  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      TABLE ACCESS FULL			TEMPLATES	3 K	305 K	716  	 	      	             	 
      NESTED LOOPS					22  	3 K	760  	 	      	             	 
        NESTED LOOPS					22  	2 K	738  	 	      	             	 
          TABLE ACCESS FULL		TEMPLATES	22  	2 K	716  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	BANKACCOUNT	1  	16  	1  	 	      	             	 
            INDEX UNIQUE SCAN		PK_BANKACCOUNT	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	COMPANY		1  	32  	1  	 	      	             	 
          INDEX UNIQUE SCAN		PK_COMPANY	1