Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql in oracle

Posted on 2013-11-06
3
Medium Priority
?
419 Views
Last Modified: 2013-11-06
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.
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39629236
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39629295
thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39629308
no problem; thank you! cheers, Paul
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question