CODEWITHSUNDEEP
sqloracle-database
BreenDeen
BreenDeen

Reputation: 760

Oracle SQL Retrieving Columns based on conditions all in the same table

I have an Oracle view which needs to be used to populate different names on a page. The names of the page are filled based on the search criteria using the same database columns. The value assigned to the name simply depends on the search criteria. To make this more concrete, here is an example

EMPLOYEE_ID  DEPT    ROLE_TYPE       GROUP_TYPE
 123          IT     DEVELOPER        SUPPORT
 111          IT     DEVELOPER        APPL_SERVICES
 145          IT     QA               APPL_SERVICES
 222          IT     WEB              APPL_SERVICES
 322          IT     WEB              SUPPORT

The field names/categories to be populated by this table are as follows

     WEB SUPPORT SERVICES 
     WEB APPLICATION SERVICES 
     DEVELOPER APPLICATION SERVICES
     DEVELOPER SUPPORT SERVICES
     QA APPLICATION SERVICES
     QA SUPPORT SERVICES

The logic is as follows for using SQL Filters:

 where ROLE_TYPE='DEVELOPER' AND GROUP_TYPE='APPL_SERVICES'
 Then assign employee 111 to  DEVELOPER APPLICATION SERVICES

 where ROLE_TYPE='DEVELOPER' AND GROUP_TYPE='SUPPORT'
 Then assign employee 123 to  DEVELOPER SUPPORT SERVICES

 where ROLE_TYPE='WEB' AND GROUP_TYPE='SUPPORT'
 Then assign employee 322 to  WEB SUPPORT SERVICES

 where ROLE_TYPE='WEB' AND GROUP_TYPE='APPL_SERVICES'
 Then assign employee 222 to  WEB APPLICATION SERVICES

 and so on..

My question is what's the best way to structure the query without having to make multiple queries? I don't want to write an individual query for each condition.

To clarify what I mean by here's what the UI display would look like:

USER           FUNCTION         POSITION            GROUP







123      DEVELOPER SUPPORT SERVICES          DEVELOPER       SUPPORT SERVICES

Thank you

Upvotes: 0

Views: 51

Answers (2)

Rnsthota
Rnsthota

Reputation: 11

The DECODE function will compare each column value one by one. You can use it if you want to compare based on a single statement:

select 
  a.*,
  decode(a.employee_id,
    123,'DEVELOPER SUPPORT SERVICES',
    111,'DEVELOPER APPLICATION SERVICE',
    145,'QA Application services',
    222,'WEB Application service',
    322,'WEB SUPPORT SERVICES'
  ) as new_group 
from table_name a;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You just use case:

select t.*, 
       (case when ROLE_TYPE = 'DEVELOPER' AND GROUP_TYPE = 'APPL_SERVICES'
             then 'DEVELOPER APPLICATION SERVICES'
             when  ROLE_TYPE ='DEVELOPER' AND GROUP_TYPE = 'SUPPORT'
             then 'DEVELOPER SUPPORT SERVICES'
             when ROLE_TYPE = 'WEB' AND GROUP_TYPE = 'SUPPORT'
             then 'WEB SUPPORT SERVICES'
             when ROLE_TYPE = 'WEB' AND GROUP_TYPE = 'APPL_SERVICES'
             then 'WEB APPLICATION SERVICES'
        end) as new_group
from . . .

Upvotes: 1

Related Questions