Contribute from: Tim Hartman (TX3)

Note: this example has a WHERE clause limiting results to a single user. Is account active? Is SA? First/last session

DB TYPE:           ORACLE 11g

Query:

SELECT user_id,
CASE WHEN acc_is_active = 'Y' THEN 'TRUE' ELSE 'FALSE' END is_active,
CASE WHEN ur_role_id =10 THEN 'TRUE' ELSE 'FALSE' END is_siteadmin,
user_name,
us_dom_auth AS pxed_ou,
full_name,
email,
description,
TO_CHAR (last_update, 'YYYY-MM-DD') AS last_update,
TO_CHAR ((SELECT MIN (end_time) FROM sessions_history WHERE user_name = 'hartmat'), 'YYYY-MM-DD') AS first_session,
TO_CHAR ((SELECT MAX (end_time) FROM sessions_history WHERE user_name = 'hartmat'), 'YYYY-MM-DD') AS last_session
FROM users LEFT OUTER JOIN users_roles ON ur_user_id = user_id
where user_name = '<USERNAME>'
ORDER BY is_active DESC, user_name