Oracle Subquery?

Hi all. I have to develop a query that states each individual benefit plan for each employee.
The first query, pulls all the employee demographics: Query1.
The second query pulls the information for a particular one.

How can I merge these two?

Query 1
SELECT emp.employee,
       emp.last_name,
       emp.first_name,
       emp.addr1,
       emp.addr2,
       emp.city,
       emp.state,
       emp.zip,
       emp.department,
       emp.emp_status,
       emp.pay_rate,
       emp.pro_rate_a_sal,
       emp.job_code,
       emp.email_address,
       emp.position,
       pap.description,
       pap.end_date,
       pap.posit_status,
       emp.nbr_fte,
       emp.adj_hire_date
       

FROM PROD.EMPLOYEE emp,
     PROD.PAPOSITION pap
     
WHERE emp.COMPANY = pap.COMPANY
AND   emp.POSITION = pap.POSITION
AND   emp.department not in ('99900', '95001', '95130')
AND   emp.emp_status not in ('TP', 'AT', 'SR', 'T', 'PN')
AND   emp.job_code not in ('MDD01','MDD02','MDD03','MDD04','MDD06','MDD09','MDD10','MDD11','MDD12','MDD13',
                            'MDD14','MDD15','MDD16','MDD17','MDD18','MDD19','MDD21','MDDC1','MD-01','MD-02',
                            'MD-03','MD-04','MD-05','MD-06','MD-08','REM01','REM02','REM03','FEL01','FEL02',
                            'FEL03','FEL04','FEL05','FEL06','FEL07','FEL08','FEL09','FEL10','FEL12','FEL13',
                            'FEL14','FEL16','FEL17','FEL18','FEL19','VP-01','VP-02','VP-03','VP-04','VP-06',
                            'VP-07','VP-08','VP-09','VP-10','VP-11','VP-12','VP-13','VP-14','VP-15','VP-17',
                            'VP-18','VP-19','VP-20','VP-21','INT01','INT02','INT03','INT05','CONVP','ENVP01','EVPF1')
AND   emp.nbr_fte > 0.499
AND   pap.end_date = to_date( '01-jan-1700 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
AND   emp.adj_hire_date <= to_date( '30-nov-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )


ORDER BY pap.end_date


Query 2

SELECT ben.company,
       emp.employee,
       ben.start_date,  -- needs to be max
       ben.stop_date,
       ben.plan_type,
       ben.plan_code,
       CASE WHEN (ben.plan_code = 'DDEN') THEN ('PPO')
            WHEN (ben.plan_code = 'DHDP') THEN ('HMO EXTENDED')
            WHEN (ben.plan_code = 'DHMF') THEN ('HMO')
            WHEN (ben.plan_code = 'DPDP') THEN ('PPO EXTENDED')
            WHEN (ben.plan_code = 'LTD1') THEN ('BIWEEKLY')
            WHEN (ben.plan_code = 'LTD2') THEN ('BIWEEKLY')
            WHEN (ben.plan_code = 'STD') THEN ('BIWEEKLY')
            WHEN (ben.plan_code = 'STD1') THEN ('BIWEEKLY')
            WHEN (ben.plan_code = 'UHDP') THEN ('HMO EXTENDED')
            WHEN (ben.plan_code = 'UHMO') THEN ('HMO')
            WHEN (ben.plan_code = 'UPDP') THEN ('PPO EXTENDED')
            WHEN (ben.plan_code = 'UPPO') THEN ('PPO')
            WHEN (ben.plan_code = 'VEDP') THEN ('EXTENDED')
            WHEN (ben.plan_code = 'VEYE') THEN ('VISION')
            WHEN (ben.plan_code IS NULL) THEN ('')
           END AS "R_DESC",
       ben.plan_option,
       ben.cov_option,  -- needs to be max
       bnp.cov_desc,   --needs to be max
       ben.emp_pre_cont,  --needs to be summed up
       ben.emp_aft_cont,  --needs to be summed up
       ben.comp_cont,   --needs to be summed up
       emp.adj_hire_date        
       

FROM PROD.employee emp,
     PROD.benefit ben,
     PROD.bncovopt bnp,
     PROD.r_plan rpl
     
     
WHERE emp.employee = ben.employee
AND   ben.plan_type = rpl.plan_type
AND   ben.plan_code = rpl.plan_code
AND   ben.plan_code = bnp.plan_code
AND   ben.cov_option = bnp.coverage_opt
AND   emp.adj_hire_date <= to_date( '30-nov-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
AND   ben.stop_date = to_date( '01-jan-1700 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
AND   ben.plan_type = 'HL'
AND   ben.plan_option = 25
AND   ben.start_date=(select max(ben.start_date) from PROD.benefit)
--AND   ben.cov_option=(select max(ben.cov_option) from PROD.benefit)

order by emp.employee, ben.plan_code
metalteckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It is pretty difficult to take two queries from a database that you have never seen and that you have no idea how all the tables join and create an entirely new query that"works".

Off the top of my head, I would make the two queries you have, inline views then join them in the new query:

select q1.col1, q2.col2, q1.col3
from (...entire_select_from_query1...) q1 join (...entire_select_from_query2...) q2 on q1.common_column1=q2.common_column1 and q1.common_column2=q2.common_column2
/

The issue there is you hit the emp table twice.  You may not need to do that but if the queries run fast enough individually, it might not matter.

What columns should the new query have?
0
PortletPaulfreelancerCommented:
Is the intention to have the second query produce one row per employee?

I assume it is, so you need to take that second query and make it do that.

e.g.

SELECT
      ben.company
    , emp.employee
    , MAX(ben.start_date)  -- needs to be max
    , MAX(ben.stop_date) -- probably also need to be max() 
    , MAX(bnp.cov_desc)  -- needs to be max
    , MAX(ben.emp_pre_cont)  --needs to be max
    , SUM(ben.emp_aft_cont) --needs to be summed up
    , SUM(ben.comp_cont) --needs to be summed up
    , MAX(emp.adj_hire_date) --needs to be summed up ??? max() probably

FROM PROD.employee emp
INNER JOIN PROD.benefit ben  ON emp.employee = ben.employee
INNER JOIN PROD.bncovopt bnp ON ben.plan_code = bnp.plan_code AND ben.cov_option = bnp.coverage_opt
INNER JOIN PROD.r_plan rpl   ON ben.plan_type = rpl.plan_type AND ben.plan_code = rpl.plan_code
WHERE emp.adj_hire_date <= to_date('30-nov-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
AND ben.stop_date = to_date('01-jan-1700 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
AND ben.plan_type = 'HL'
AND ben.plan_option = 25

GROUP BY
      ben.company
    , emp.employee

Open in new window

Now for all of the columns I have missed in that query, you need to add those. If a column will not increase the number of rows then include it in the select list and the group by list.

If a column will increase the number of rows, then you have to decide what aggregation function is appropriate for the column instead. e.g. MIN() MAX() SUM() AVG()

Note I have assumed:

MAX(ben.stop_date) -- probably also need to be max()
MAX(emp.adj_hire_date) -- you cannot sum a date column
you really should use ANSI join syntax
neither of these are required
--AND   ben.start_date=(select max(ben.start_date) from PROD.benefit)
--AND   ben.cov_option=(select max(ben.cov_option) from PROD.benefit)

Once this subquery gets to one row per employee then adding it as a subquery is quite simple.

SELECT
      emp.employee
    , emp.last_name
    , emp.first_name
...
FROM PROD.EMPLOYEE emp
INNER JOIN PROD.PAPOSITION pap
      ON emp.COMPANY = pap.COMPANY
      AND emp.POSITION = pap.POSITION
INNER JOIN (

      --updated query 2 here

        ) ben
              ON emp.COMPANY = ben.COMPANY
              AND emp.employee = ben.employee

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
note I agree utterly with slightwv

without these benefits:
1. knowing exactly what is wanted (you have that in your head probably)
2. access to the tables and data
3. the ability to test

I find it really hard to stitch 2 disparate queries together.

I have also suggested the use of an inline view like slightwv

What is critical when using that approach is that the second query has columns that join back to the other tables accurately. at lines 14,15,16 above I have guessed that this join will work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

metalteckAuthor Commented:
@slightwv, the first query is to pull an active listing of all the employees.
The second query pulls only those that have enrolled in that specific benefit plan.
The main joins would be company and employee.

@Paul Maxwell, since the benefits table contains multiple years of benefit coverage, we need to use the max function to select the most current one.
In your first query, I tried to use the max functions in the select statement, but get the following error:
SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

I was able to get the benefit query to compile by putting the max function in the where clause.
I then tried to join both queries, but got the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Looking at the big picture here...we have about 30 benefits and I think I may need to have 30 additional queries that all connect to the first query. If you can think of a better way to accomplish this, I'm open to suggestions. I'm fairly new at Oracle programming so, any suggestions you can provide will be greatly appreciated.

Thank you
0
slightwv (䄆 Netminder) Commented:
I'm not understanding what it is you want.

Can you come up with a simplified model with sample data and expected results?  This way we can set up a test case on our end and provide working SQL.
0
metalteckAuthor Commented:
@slightwv
Employee        HL Benefits       Dental Benefits
12345               656.91                85.00
23456                323                    
34567                                           85.00

Above is a sample of what I would like to get.
Each row will be designated for 1 employee and all the benefits they have opted for.
An employee can have, some or no benefits.
Since each benefit has a start date, the most recent one needs to be selected.

The first query pulls the employee's demographics.
The second and subsequent queries will be used to identify each benefit.

I hope this helps. Let me know if you need anything else.

Thanks
0
slightwv (䄆 Netminder) Commented:
>>Since each benefit has a start date, the most recent one needs to be selected.

I assume what you posted was the expected results.

I need to see the raw sample data that goes with the results.

Looks like you are moving towards a pivot table for benefits.

>>we have about 30 benefits

There will need to be a known maximum number of benefits before you can make them individual columns.  I will also need to know the maximum number of benefits.  The word "about" won't work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.