Question :
I need to generate a report that displays the account status of all schemas, which I’ve got.
SELECT
''''|| USERNAME ||'''' USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||'''' PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;
However the requirement also states that they want the database name on every row in the report, so…
SELECT
''''|| NAME ||''''
FROM V$DATABASE
My SQL is pretty poor so I tried some sort of mismashed join, but realised that won’t work… any ideas?
Answer :
SELECT
''''|| USERNAME ||'''' USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||'''' PROFILE,
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;
or
SELECT
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME,
''''|| USERNAME ||'''' USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||'''' PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 2,3;