Solved

Oracle Query

Posted on 2014-04-08
13
281 Views
Last Modified: 2014-04-13
Hi,

I have emp and config tables and their data like in the attachment.

--------------------------

I build the query like in the attachment (Query.txt) to fetch my data. And the query is working fine according to my requirement when I pass value in place of pram_in_value like below


 SELECT e1.*   FROM EMP e1
                     LEFT OUTER JOIN config cf
                        ON e1.conf_cd = cf.conf_cd                    
               WHERE  CASE
                           WHEN cf.flag_val IN ('ABC')
                           THEN
                              e1.mgr_id
                           WHEN cf.flag_val IN ('XYZ')
                           THEN
                              (SELECT MAX (e2.eid)
                                 FROM EMP e2
                                WHERE     e2.mgr_id =
                                             (SELECT e3.mgr_id
                                                FROM EMP e3
                                               WHERE     e3.eid =  '51'
                                                     AND e3.eid = e1.eid)
                                    )
                        END = '51'        

EID      ENO      ENAME      MGR_ID      CONF_CD
1      51      ename1      51      100

If I run the query with 54 value then I should get as below and also am getting correctly like below.

EID      ENO      ENAME      MGR_ID      CONF_CD
4      54      ename4      501      200
3      53      ename3      501      200
2      52      ename2      501      200

but I have a table (req_emp as in the attachement) where 54 and 64 are loaded. I need to link that table to this query and get the out put as

EID      ENO      ENAME      MGR_ID      CONF_CD
4      54      ename4      501      200
3      53      ename3      501      200
2      52      ename2      501      200
9      64      ename4      503      200
8      63      ename3      503      200
7      62      ename2      503      200

How to achieve this?
Am unable to link req_emp table with the query.
I tried like below
 SELECT e1.*   FROM EMP e1
               join req_emp re on e1.eno=re.empno
                     LEFT OUTER JOIN config cf
                        ON e1.conf_cd = cf.conf_cd                    
               WHERE  CASE
                           WHEN cf.flag_val IN ('ABC')
                           THEN
                              e1.mgr_id
                           WHEN cf.flag_val IN ('XYZ')
                           THEN
                              (SELECT MAX (e2.eno)
                                 FROM EMP e2
                                WHERE     e2.mgr_id =
                                             (SELECT e3.mgr_id
                                                FROM EMP e3
                                               WHERE     e3.ENO =  re.empno
                                                     AND e3.mgr_id = e1.mgr_id)
                                    )
                        END =  re.empno  

but getting output only 2 records as below
EID      ENO      ENAME      MGR_ID      CONF_CD
9      64      ename4      503      200
4      54      ename4      501      200

But I need 6 records as given before. Can you please suggest?

Thanks.
Query.txt
EE-DDL-DML.sql
0
Comment
Question by:GouthamAnand
  • 8
  • 5
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm not getting any results from your query using either '51' or '54' against the sample data
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Placing EMP.MGR_ID OR emp.EID into a column reveals the following
    SELECT
            e1.*  
          , CASE
                     WHEN cf.flag_val IN ('ABC') THEN  e1.mgr_id 
                     WHEN cf.flag_val IN ('XYZ') THEN  e1.eid
            END AS the_case
    FROM EMP e1
    INNER JOIN config cf ON e1.conf_cd = cf.conf_cd   ;
    
           /* there is NO point in LEFT JOIN 
              because to the where clause requires either 'ABC' or 'XYZ' (i.e. NULLS are not evaluated)
              use INNER JOIN */
    
**[Results][2]**:
    
    | EID | ENO |  ENAME | MGR_ID | CONF_CD | THE_CASE |
    |-----|-----|--------|--------|---------|----------|
    |   1 |  51 | ename1 |    500 |     100 |      500 |
    |   2 |  52 | ename2 |    501 |     200 |        2 |
    |   3 |  53 | ename3 |    501 |     200 |        3 |
    |   4 |  54 | ename4 |    501 |     200 |        4 |
    |   5 |  55 | ename5 |    502 |     100 |      502 |
    |   6 |  56 | ename6 |    502 |     100 |      502 |

Open in new window

so it cannot equal 51 or 54 - and hence I do not understand your question - sorry.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Hi,

Sorry. I gave wrong data for emp table. Please remove the data in the current emp table and attached is the correct data file.
Please find the attachment.
With this data file you will get the data from my query.

Thanks.
Emp-Inserts.sql
0
 

Author Comment

by:GouthamAnand
Comment Utility
Sorry. In hurry, also query attached is not proper.

Please find the updated correct attached query.

My question is my query is fetching the records when I pass the values one by one as below

declare
cursor c1 is
select empno from req_emp;

Cursor c2(pram_in_value number) is
 SELECT e1.*   FROM EMP e1
                     LEFT OUTER JOIN config cf
                        ON e1.conf_cd = cf.conf_cd                    
               WHERE  CASE
                           WHEN cf.flag_val IN ('ABC')
                           THEN
                              e1.mgr_id
                           WHEN cf.flag_val IN ('XYZ')
                           THEN
                              (SELECT MAX (e2.eid)
                                 FROM EMP e2
                                WHERE     e2.mgr_id =
                                             (SELECT e3.mgr_id
                                                FROM EMP e3
                                               WHERE     e3.eid =  pram_in_value
                                                     AND e3.eid = e1.eid)
                                    )
                        END = param_in_values ;
begin

for i in c1
loop
for j in c2(i.empno)
loop
dbms_output.put_line(j.eid);
end loop
end loop
end;
-------------------------------------------
But I want to achive this in a single query by clubbing the req_table with my complex query.

If the complexity of the base query is reduced with the same output also helpful to me. But I cannot be able to reduce the complexity without distubing the required output.
Actually there is another similar query in my production database.
I convered that for better explantion of the problem. That is why I kept the LEFT JOIN on config table as it is in production db.

Thanks.
EEQueryWithValues.sql
0
 

Author Comment

by:GouthamAnand
Comment Utility
I have many other tables also in the from clause in the actual query.
I need to fetch the data from all those tables for this passed employee number(eno).

So I cannot move the WHERE clause in the SELECT clause (as givne in comment above) because of the complexity of the acutal my base query in the production database.

Thanks.
0
 

Author Comment

by:GouthamAnand
Comment Utility
This is the correct pl/sql block which I want to replace with a single query. Sorry for confusion.

declare
cursor c1 is
select empno from req_emp;

Cursor c2(pram_in_value number) is
 SELECT e1.*   FROM EMP e1
                     LEFT OUTER JOIN config cf
                        ON e1.conf_cd = cf.conf_cd                    
               WHERE  CASE
                           WHEN cf.flag_val IN ('ABC')
                           THEN
                              e1.mgr_id
                           WHEN cf.flag_val IN ('XYZ')
                           THEN
                              (SELECT MAX (e2.eno)
                                 FROM EMP e2
                                WHERE     e2.mgr_id =
                                             (SELECT e3.mgr_id
                                                FROM EMP e3
                                               WHERE     e3.ENO =  pram_in_value
                                                     AND e3.mgr_id = e1.mgr_id)
                                    )
                        END =  pram_in_value  
begin

for i in c1
loop
for j in c2(i.empno)
loop
dbms_output.put_line(j.eid);
end loop
end loop
end;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
the combination of query and sample data still does not align with your question. This is the summary so far:
table EMP
    | EID | ENO |  ENAME | MGR_ID | CONF_CD |
    |-----|-----|--------|--------|---------|
    |   1 |  51 | ename1 |    500 |     100 |
    |   2 |  52 | ename2 |    501 |     200 |
    |   3 |  53 | ename3 |    501 |     200 |
    |   4 |  54 | ename4 |    501 |     200 |
    |   5 |  55 | ename5 |    502 |     100 |
    |   6 |  56 | ename6 |    502 |     100 |

table CONFIG
    | CONF_CD | FLAG_VAL |
    |---------|----------|
    |     100 |      ABC |
    |     200 |      XYZ |

table req_emp
    | EMPNO |
    |-------|
    |    54 |
    |    64 |
    
If you search by value 51 you expect:

EID  ENO      ENAME      MGR_ID   CONF_CD
1      51     ename1      51      100    << see point a. below


If you search by a value of 54 you expect:

EID  ENO      ENAME      MGR_ID   CONF_CD
4      54      ename4      501      200
3      53      ename3      501      200
2      52      ename2      501      200

but you have a table req_emp where 54 and 64 are loaded; need to link that table to get the output as:

EID      ENO      ENAME      MGR_ID      CONF_CD
4      54      ename4      501      200
3      53      ename3      501      200
2      52      ename2      501      200
9      64      ename4      503      200 << see point b. below
8      63      ename3      503      200
7      62      ename2      503      200 << see point c. below

Open in new window

PROBLEMS:
b. there is no mgr_id of 51 in the sample data (typo? should it have been 501??)
b. there is no ENO of 62, 63, 64 in the sample data
c. there is no EID of 7,8,9 in the sample data
0
 

Author Comment

by:GouthamAnand
Comment Utility
I attached in the first commet EMP-iNSERTS file which is having correct data.
Please remove the exising data and load the correct as per my attachment or as below.

Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (1, 51, 'ename1', 51, 100);
Insert into FIA.EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (2, 52, 'ename2', 501, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (3, 53, 'ename3', 501, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (4, 54, 'ename4', 501, 200);
Insert into FIA.EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (5, 55, 'ename5', 55, 100);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (6, 56, 'ename6', 56, 100);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (7, 62, 'ename2', 503, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (8, 63, 'ename3', 503, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (9, 64, 'ename4', 503, 200);
COMMIT;



I attached all files for your reference.
EE-DDL-DML.sql
plsqlblock.txt
EEQueryWithValues.sql
0
 

Author Comment

by:GouthamAnand
Comment Utility
The requirement in the query is
if the passed value is having config  value 'ABC' then directly match with the mgr_id and fetch the records.
if the passed value is having config value 'XYZ' then check
    if that passed value is maximum value of ENO under his manager
        then show all the employees under that same manager.

if the passed value is having config value 'XYZ' then check if that passed value is NOT maximum value of ENO then do not show anything.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
Could you try this please?
SELECT
      emp.EID
    , emp.ENO
    , emp.ENAME
    , emp.MGR_ID
    , emp.CONF_CD
    , sq.FLAG_VAL
FROM emp
      INNER JOIN (
            SELECT
                  COALESCE(abc.empno, emp.mgr_id) AS mgr_id
                , config.FLAG_VAL
            FROM EMP
                  INNER JOIN CONFIG
                        ON emp.CONF_CD = config.CONF_CD
                  LEFT JOIN req_emp abc
                        ON emp.mgr_id = abc.empno
                              AND config.FLAG_VAL = 'ABC'
                  LEFT JOIN req_emp xyz
                        ON emp.eno = xyz.empno
                              AND config.FLAG_VAL = 'XYZ'
            WHERE abc.empno IS NOT NULL
                  OR xyz.empno IS NOT NULL
      ) sq
            ON emp.mgr_id = sq.mgr_id
;

Open in new window

Against the latest sample data the result from that query is:
| EID | ENO |  ENAME | MGR_ID | CONF_CD | FLAG_VAL |
|-----|-----|--------|--------|---------|----------|
|   2 |  52 | ename2 |    501 |     200 |      XYZ |
|   3 |  53 | ename3 |    501 |     200 |      XYZ |
|   4 |  54 | ename4 |    501 |     200 |      XYZ |
|   7 |  62 | ename2 |    503 |     200 |      XYZ |
|   8 |  63 | ename3 |    503 |     200 |      XYZ |
|   9 |  64 | ename4 |    503 |     200 |      XYZ |
		

--Latest sample data:

CREATE TABLE EMP
(
  EID     number primary key,
  ENO     NUMBER,
  ENAME   VARCHAR2(400 BYTE),
  MGR_ID  NUMBER,
  CONF_CD number
);

CREATE TABLE CONFIG
(
  CONF_CD   NUMBER,
  FLAG_VAL  VARCHAR2(10 BYTE)
);

insert into config values(100,'ABC'); 

insert into config values(200,'XYZ');

Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (1, 51, 'ename1', 51, 100);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (2, 52, 'ename2', 501, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (3, 53, 'ename3', 501, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (4, 54, 'ename4', 501, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (5, 55, 'ename5', 55, 100);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (6, 56, 'ename6', 56, 100);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (7, 62, 'ename2', 503, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (8, 63, 'ename3', 503, 200);
Insert into EMP
   (EID, ENO, ENAME, MGR_ID, CONF_CD)
 Values
   (9, 64, 'ename4', 503, 200);
COMMIT;


 create table req_emp(empno number);
                        
insert into req_emp values(54);

insert into req_emp values(64);

Open in new window

0
 

Author Comment

by:GouthamAnand
Comment Utility
Thank you very much. This exactly matches my requirement.
0
 

Author Closing Comment

by:GouthamAnand
Comment Utility
Thanks a lot.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
It's a pleasure, thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

743 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now