AnsweredAssumed Answered

Workflow database query: How do you use If/Then?

Question asked by Shlomo Katz on Apr 2, 2019
Latest reply on Apr 3, 2019 by Mostafa Helmy

Like • Show 0 Likes0
Comment • 2

Hello

We have a workflow that looks for a new hires manager's OU in to determine the location of the new hire.

The lookup information is in T_MASTER_ENTERPRISE_USERS.

I need to provide "stub" data if the manager cannot be located in the database.

Use Case:

Manager and subordinate are hired on the same day.

Same Joiner rule creates their accounts.

Job(s) fail because the subordinate is looking for the manager, which does not yet exist.

I need to find existing information and provide a hard coded location if the manager cannot be located.

This is what I have so far - the biggest challenge right now is providing the hard coded location (assuming this query is working at all)

Declare
m_exst number;
mgrou varchar2(512);

begin
   select count(*)
   into m_exst
   FROM T_MASTER_ENTERPRISE_USERS
   WHERE USER_ID = ''
   and UNIQUE_ID != '<null>';
   if  m_exst = 0
then
   'CN=AveksaAdmin,OU={subou},DC=Company,DC=org'
else
   SELECT UNIQUE_ID
   into mgrou
FROM T_MASTER_ENTERPRISE_USERS
   WHERE USER_ID = '####'
   and UNIQUE_ID != '<null>';
end if;
end;

Mostafa Helmy

Apr 3, 2019 4:19 AM

Correct Answer

If I were to translate your exact PL/SQL block up there to a normal SELECT statement it would look like this:

SELECT
 CASE
    WHEN (
      SELECT
        COUNT(1)
      FROM
        t_master_enterprise_users
      WHERE
        user_id = 'UID'
        AND unique_id != 'UNQID'
    ) = 0 THEN 'CN=AveksaAdmin,OU={subou},DC=Company,DC=org'
    ELSE (
      SELECT
        unique_id
      FROM
        t_master_enterprise_users
      WHERE
        user_id = 'UID'
        AND unique_id != 'UNQID'
    )
  END AS mgrou
FROM
  dual;

See the reply in context

No one else had this question

Outcomes

Kristian Nordman Apr 3, 2019 2:29 AM

Hi!

This should be close enough that you can modify it for your purpose:

SELECT meu.USER_ID, 
CASE
  WHEN meu2.DEPARTMENT IS NOT NULL THEN meu2.DEPARTMENT
  ELSE 'Default Department'
END AS department 
FROM V_MASTER_ENTERPRISE_USERS meu
LEFT JOIN V_MASTER_ENTERPRISE_USERS meu2 ON meu.SUPERVISOR_ID = meu2.ID
WHERE meu.user_id = 'kk2001';

This returns 2 columns, the user id entered in the where clause and their supervisors department as long as it is not null. If supervisors department is null, it returns 'Default Department' instead.

You could modify this to only return the department (or location) into whatever variable you are want to use later in the workflows.

I've also used the recommended View for Master Enterprise User instead of the table as it is more resistant to change than the table.

Actions