Mega Code Archive
Use user-defined type to combine query logic
<![CDATA[
SQL> create or replace type myScalarType as object( cnt number, average number )
2 /
Type created.
SQL>
SQL> select username, a.data.cnt, a.data.average
2 from (
3 select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
4 /
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
RNTSOFT 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>
SQL>
]]>