<![CDATA[
select usr.userid userid, decode(count(ba.account_number), '0', ' ', LISTAGG(ba.account_number,',') within group(order by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status)) accountnumber,
NVL(usr.firstname, ' ') firstname, NVL(usr.lastname, ' ') lastname, ar.name userrole,
NVL(sp.lock_reason, decode(sp.status,1,'global.label.lockoutReason.accountExpired','')) reason, 'global.label.reactivate' action
from edx_bsl_umf_user usr, edx_bsl_auth_secprofile sp, edx_bsl_sec_prof_roles_link prl, edx_bsl_auth_secrole ar,
edx_umf_user_acct_link ual, edx_omf_account ba
where usr.userid = sp.userid and sp.profileid = prl.profile_id and prl.role_id = ar.id
and (sp.islocked = 1 or (sp.status=1 and to_date('$reportContext.getCurrentDateTime()','mm/dd/yyyy hh24:mi:ss') > sp.DATE_PASSWD_EXP)) and usr.isactive = 1 and ar.type = 'B2C'
and usr.userid = ual.userid and ual.account_key = ba.account_key and ba.deletedat is null
group by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status
union
select usr.userid userid, decode(count(osa.account_number), '0', ' ', LISTAGG(osa.account_number,',') within group(order by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status)) accountnumber,
NVL(usr.firstname, ' ') firstname, NVL(usr.lastname, ' ') lastname, ar.name userrole,
NVL(sp.lock_reason, decode(sp.status,1,'global.label.lockoutReason.accountExpired','')) reason, 'global.label.reactivate' action
from edx_bsl_umf_user usr, edx_bsl_auth_secprofile sp, edx_bsl_sec_prof_roles_link prl, edx_bsl_auth_secrole ar,
user_service_agreement usa, edx_omf_serviceagreement osa
where usr.userid = sp.userid and sp.profileid = prl.profile_id and prl.role_id = ar.id
and (sp.islocked = 1 or (sp.status=1 and to_date('$reportContext.getCurrentDateTime()','mm/dd/yyyy hh24:mi:ss') > sp.DATE_PASSWD_EXP)) and usr.isactive = 1 and ar.type = 'B2B'
and usr.userid = usa.userid(+) and usa.service_agreement_id = osa.service_key(+)
and osa.delete_date is null
group by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status
]]>
decode：相当于 if else
nvl(args1,args2):当args=null返回，args2
listagg():
测试数据
SQL>
SQL> select empno,ename,deptno from scott.emp;
EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
SQL> SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
FROM scott.emp
GROUP BY deptno;
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD