0

I am attempting to build a query but am lost on one thing. I need to build a column that outputs true or false depending on what another query outputs. The issue I'm having is that the other query has a table's output worth of data. All i need from that query is that if it returns something, be true, else false.

If it helps I am working with Oracle's OIM's database Below is the query before any changes I need to make. This outputs a list of users that have been termed in the last 7 days. 

select usr_login as "User Login", usr_emp_no as "Employee Number", usr_first_name as "First Name", usr_last_name as "Last Name", usr_status as "User Status", 
  to_char(USR_AUTOMATICALLY_DELETE_ON, 'MM/DD/YYYY') USR_AUTOMATICALLY_DELETE_ON, 
  to_char(usr_end_date, 'MM/DD/YYYY') usr_end_date,
  USR_DISPLAY_NAME,
  act.act_name as "Organization Name",
  USR_UDF_PS_EMPL_STATUS as "Employee Status"
from usr, act
where usr_end_date >= to_date(to_char(sysdate - interval '8' day, 'MM/DD/YYYY'), 'MM/DD/YYYY')
  and usr_end_date <= to_date(to_char(sysdate - interval '1' day, 'MM/DD/YYYY'), 'MM/DD/YYYY')
  and usr.act_key = act.act_key
order by usr_end_date, usr_login;

For each of those users, I need the column that it is in to return based off that user. So if one user has a role, return true, while the next user without roles returns false.

Unless there is a better way, I am trying to use this query to return the true or false:

define usr_login = '&usr_login'
select ugp.ugp_name, ugp.ugp_key, usr.usr_key, usr.usr_login, usr.usr_status
from usg, ugp, usr
where usr.usr_key = usg.usr_key and ugp.ugp_key = usg.ugp_key and
    upper(usr.usr_login) = upper('&usr_login')
    and ugp.ugp_name != 'ALL_USERS' -- I need to exclude the role "All Users"
order by usr.usr_status, ugp.ugp_name, usr.usr_login, usr.usr_status;

The train of thought I am working off of is this:

(NVL((select usr_login from usr where usr_login=UPPER('non-existantValue')),'False')) as "dumb", --Working if null(expression,ifnull, do this)This outputs as false

But I am very open to suggestions.

To make this more complex, I really need this to work using the second query above. If the user has a role, return true. If the User does not, return false.

Note: I am working within Oracle SQL Developer

Any help or suggestions? Thanks!

Dean013
  • 323
  • 1
  • 3
  • 10

1 Answers1

0

You could include a CASE statement

select usr_login as "User Login", usr_emp_no as "Employee Number", usr_first_name as "First Name", usr_last_name as "Last Name", usr_status as "User Status", 
  to_char(USR_AUTOMATICALLY_DELETE_ON, 'MM/DD/YYYY') USR_AUTOMATICALLY_DELETE_ON, 
  to_char(usr_end_date, 'MM/DD/YYYY') usr_end_date,
  USR_DISPLAY_NAME,
  act.act_name as "Organization Name",
  USR_UDF_PS_EMPL_STATUS as "Employee Status",
  CASE WHEN (SELECT unique USR_KEY FROM USG WHERE UGP_KEY > 3 and USG.USR_KEY = USR.USR_KEY) is null THEN 'FALSE' ELSE 'TRUE' END AS "HAS ROLE"
from usr, act
where usr_end_date >= to_date(to_char(sysdate - interval '8' day, 'MM/DD/YYYY'), 'MM/DD/YYYY')
  and usr_end_date <= to_date(to_char(sysdate - interval '1' day, 'MM/DD/YYYY'), 'MM/DD/YYYY')
  and usr.act_key = act.act_key
order by usr_end_date, usr_login;
Berkley Lamb
  • 283
  • 2
  • 12