CODEWITHSUNDEEP
sqloracle-database
Priyank Bhasin
Priyank Bhasin

Reputation: 7

Oracle Case statement with Name and empID

I have two tables one table contains a role type of the engineer and the second table is the emp table.

My column Primary_eng and secondary_ENG contains EMP_ID, I need the name instead of the EMP_ID.

MY Employee table contains the name as FIRST NAME and LAST NAME. I also want to pass a Parameter with ENG Name.

My condition:

CASE WHEN (PRIMARY_ENG = :P_DBA AND secondary_ENG != :P_DBA) THEN 1 
            WHEN (PRIMARY_ENG != :P_DBA AND secondary_ENG = :P_DBA) THEN 2 
            ELSE 3 END

Upvotes: 0

Views: 127

Answers (1)

vishnudattan
vishnudattan

Reputation: 476

Welcome to Stack Overflow!
As other user's have pointed out, please consider posting more details such as:

  • structure of your tables
  • sample data
  • expected output

Assuming the following based on the limited info you've provided:

1. You have an employee table with emp_id, first_name and last_name

|EMP_ID |FIRST_NAME |LAST_NAME  |
|100    |John       |Smith      |
|200    |Peter      |Scott      |
|300    |Priyank    |Bhasin     |


2. You have a set of roles ( let's say a table role_types ) with each role having a primary engineer's emp_id ( primary_eng ) and secondary engineer's emp_id ( secondary_eng ) 

|ROLE_TYPE  |PRIMARY_ENG    |SECONDARY_ENG  |
|DBA        |100            |200            |
|DEV        |200            |300            |
|TESTER     |300            |100            |


3. By passing an emp_id you need:
(a) the employee's full name
(b) a custom parameter which is a priority based list of roles to which the employee is assigned

Solution:

  • You can fetch the engineer's name by joining both tables concatenating first_name and last_name
  • The parameter you've defined can also be fetched using a direct join of both tables
select r.role_type,
e.first_name||' '||e.last_name as engineer_name,
CASE WHEN (PRIMARY_ENG = 100 AND secondary_ENG != 100) THEN 1 
            WHEN (PRIMARY_ENG != 100 AND secondary_ENG = 100) THEN 2 
            ELSE 3 END as my_param
from role_types r
  join employee e on r.primary_eng = e.emp_id
  order by 3
;

Output:

|ROLE_TYPE  |ENGINEER_NAME  |MY_PARAM   |
|DBA        |John Smith     |1          |
|TESTER     |Priyank Bhasin |2          |
|DEV        |Peter Scott    |3          |


Demo here

If this doesn't work, please enhance your question with additional details
If this suits your requirement, feel free to accept and vote

Upvotes: 3

Related Questions