sql in oracle

Hello Experts,

I have few tables as like below:

CREATE TABLE test_aud 
(AUDITOR_ASSIGNMENTS       VARCHAR2(1000 CHAR) ) ; 


CREATE TABLE 
TEST_CATEGORY
(CATEGORY_ID           NUMBER             
,CATEGORY_NAME         VARCHAR2(255 CHAR) );


CREATE TABLE TEST_SECTION
(SECTION_NAME          VARCHAR2(255 CHAR) 
,SECTION_ID            NUMBER       );




Insert into test_aud (AUDITOR_ASSIGNMENTS) values ('F-0');
Insert into test_aud (AUDITOR_ASSIGNMENTS) values ('S-1');
Insert into test_aud (AUDITOR_ASSIGNMENTS) values ('S-3');
Insert into test_aud (AUDITOR_ASSIGNMENTS) values ('C-1040');
Insert into test_aud (AUDITOR_ASSIGNMENTS) values ('C-1000');




INSERT INTO TEST_CATEGORY (CATEGORY_ID,CATEGORY_NAME) VALUES (1040,'KP cat 102');
INSERT INTO TEST_CATEGORY (CATEGORY_ID,CATEGORY_NAME) VALUES (1000,'Antidiscrimination');


INSERT INTO TEST_SECTION (SECTION_ID,SECTION_NAME) VALUES (1,'Labor & Human Rights');
INSERT INTO TEST_SECTION (SECTION_ID,SECTION_NAME) VALUES (3,'Environment');

Open in new window



Now I have to diaplay the data from "test_aud" table  but with certain condition:

SQL> select * from test_aud;
 
AUDITOR_ASSIGNMENTS
--------------------------------------------------------------------------------
F-0
S-1
S-3
C-1040
C-1000

Open in new window


If the record is "F-0" THEN display "Facility"
If the record starts with 'S-%' then go to "TEST_SECTION" table and get the section name.
For example if it is 'S-1' then the section id will be "1" .
Similarly if the record starts with 'C-%' then go to "TEST_CATEGORY" table and get the category name table.
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
the query below should meet the need:
**Query 1**:

    select
              a.auditor_assignments
            , coalesce(c.category_name,s.section_name,'Facility') as label
    from test_aud a
    left join test_category c
           on substr(a.auditor_assignments,3,255) =  c.category_id
    left join test_section s
           on substr(a.auditor_assignments,3,255) =  s.section_id
    

**[Results][2]**:
    
    | AUDITOR_ASSIGNMENTS |                LABEL |
    |---------------------|----------------------|
    |                 F-0 |             Facility |
    |                 S-1 | Labor & Human Rights |
    |                 S-3 |          Environment |
    |              C-1040 |           KP cat 102 |
    |              C-1000 |   Antidiscrimination |



  [1]: http://sqlfiddle.com/#!4/e0a53/3

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
thanks
0
 
PortletPaulfreelancerCommented:
no problem; thank you! cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.