Solved

How to add the results from a subquery to the main query

Posted on 2016-09-11
9
77 Views
Last Modified: 2016-09-21
I have a main query that has many other fields being listed.
One of last remaining fields I need is the summation of the hours in a 12 month period.
The code I have below works, but I'm having a hard time adding it to the main query.

I would appreciate any help I can get in this.

I added the code below to the where clause, such as:
prt.hours =

//working subquery //

SELECT sum(prt.hours)  
FROM prod.prtime prt
WHERE prt.tr_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')  
AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)
and prt.pay_sum_grp in ('REG', 'PTO','OT')
But it caused the rest of the main query to not generate any data.
0
Comment
Question by:metalteck
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41793801
if you want to add a summary at the bottom, check the group by rollup syntax
https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

rollup has the advantage of not having to execute the query twice

people usually add a row with summaries with a union all and a group by
select a,b,c from table
union all
select 'summary', null, sum(c) from table

Open in new window


if you already have a group by in the initial query then it's very easy

change
group by a, b, c
to
group by rollup ((a,b,c));

if you use (a,b,c) instead of ((a,b,c)) you will get sub summaries too
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41793977
This will work.. Enjoy !

SELECT * , prt.hourrr
FROM yourtable y
CROSS APPLY
(
	SELECT sum(prt.hours) hourrr
	FROM prod.prtime prt
	WHERE prt.tr_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')  
	AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)
	and prt.pay_sum_grp in ('REG', 'PTO','OT')
) prt

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
ID: 41794006
Just as an aside, why are you doing this:

TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)

That is a really complicated way of doing this:

trunc(sysdate, 'mm')
0
 

Author Comment

by:metalteck
ID: 41794071
Couldn't think of a better way to get the last 12 months worth of data.
I will try your code.
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 24

Expert Comment

by:Pawan Kumar
ID: 41794082
Great ! Thank bro ! Also once you get the proper result please update the accepted & assisted solution.

Thanks !
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41794104
If you want the last 12 months, then you would need this:

add_months(trunc(sysdate, 'mm'), -12)

Your original date math will return the first day of the current month.
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
select TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1) your_original,
       trunc(sysdate, 'mm') better_first_of_month,
       add_months(trunc(sysdate, 'mm'), -12) first_of_month_year_ago
  from dual;

Open in new window

This will return:
YOUR_ORIGINAL       BETTER_FIRST_OF_MON FIRST_OF_MONTH_YEAR
------------------- ------------------- -------------------
09/01/2016 00:00:00 09/01/2016 00:00:00 09/01/2015 00:00:00

Open in new window

0
 

Author Comment

by:metalteck
ID: 41794353
Hey guys,

I've been trying to add the suggestions to my query, but I'm not making any progress.
Here is my full query that I'm trying to add the summary subquery to.
Any suggestions on how to get it to work?

SET NEWPAGE 0
SET SPACE 0
SET PAGES 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET COLSEP "|"
SET TRIMSPOOL ON
SET LINES 20000
SPOOL C:\WORK\hartf.txt



        select to_char(replace(emp.fica_nbr,'-'),'999999999')
       ||rpad(substr(emp.employee,1,15),15) 
       ||rpad(substr(emp.last_name,1,30),30,' ')
       ||rpad(substr(emp.first_name,1,30),30,' ')
       ||nvl(emp.middle_name,' ')
       ||lpad(substr(emp.addr1,1,40),40)
       ||lpad(substr(emp.addr2,1,40),40)
       ||lpad(substr(emp.city,1,30),30)
       ||lpad(substr(emp.zip,1,10),10)
       ||'001' 
       ||'  '-- Province Code 
       ||'  '-- Foreign Provinice Name
       ||to_char(pem.birthdate,'YYYYMMDD')
       ||regexp_replace(pem.hm_phone_nbr,'[^0-9]') 
       ||pem.true_mar_stat
       ||pem.sex
       ||rpad(CASE
            WHEN emp.emp_status in ('FB','PN','PB','LW','LP','LS','LT','LE','LI','WA') THEN 'A'
            WHEN emp.emp_status in ('LF','LM','LO','LU','LA','LR') THEN 'L'
            ELSE null
          END,20)-- "Employee Status Code",
       ||to_char(ben.start_date,'YYYYMMDD')
       ||to_char(emp.adj_hire_date,'YYYYMMDD')
       ||to_char(emp.date_hired,'YYYYMMDD')
       ||to_char(emp.annivers_date,'YYYYMMDD')
       ||(CASE
            WHEN emp.nbr_fte < 1 then 'P '
            ELSE 'F '
          END)
       ||to_char(prh.beg_date,'YYYYMMDD')
       ||emp.pro_rate_total
       ||'A' -- Salary Basis
       ||'FL' --Work State Code
       ||'                                        '  --Employee Work Address 1   LPAD( case expression ... end, 20 )
       ||'                                        '  --Employee Work Address 2
       ||'                              '  --Employee Work City
       ||'  '  --Employee Work State
       ||'          '  --Employee Work Postal Code
       ||'   '  --Employee Work Country
       ||'          '  --Employee Work Phone#
       ||lpad(substr(emp.email_address,1,50),50)
       ||lpad(substr(pos.description,1,25),25)
       ||' ' -- Job Code
       ||lpad(substr(pos.job_code,1,10),10)
       ||(CASE
            WHEN emp.exempt_emp = 'Y' then 'E'
            ELSE 'N'
          END)
       ||' '  --Union Member
       ||'                              '  --Union Name
       ||lpad(substr(emp.department,1,10),10)
       ||'          '  --Division ID
       ||rpad(substr(pem.locat_code,1,10),10)  --Location ID
       ||'          '  --Account ID
       ||'          '  --Region ID
       ||'          '  --Company ID
       ||lpad(substr(emp1.last_name,1,30),30,' ')
       ||lpad(substr(emp1.first_name,1,30),30,' ')
       ||lpad(substr(emp1.email_address,1,50),50,' ')
       ||'          '  --Filler
       ||'          '  --Supervisor Phone#
       ||lpad(substr(zzw.lastname,1,30),30,' ')
       ||lpad(substr(zzw.firstname,1,30),30,' ')
       ||lpad(substr(zzw.email,1,50),50,' ')
       ||'           '  --Filler
       ||'          '  --HR Partner Phone#
       ||'LOA                           '
       ||'Administrator                 '
       ||'FMLA@MCH.COM                                      '
       ||'         '  --EFT ABA NUMBER
       ||'                                   '  --EFT Account Number
       ||' '  --EFT Account Type
       ||to_char(ben.start_date,'YYYYMMDD')
       ||'        '
       ||rpad(substr(emp.ot_plan_code,1,5),5)
       ||to_char(ben.start_date,'YYYYMMDD')
       ||'        '
       ||rpad(substr(ben.plan_code,1,5),5)
       ||'   '  --Disability Flex
       ||'   '  --Disability Felx
       ||'   '  --Disability Flex Duration
       ||'      '  --Disability Flex Benefit Amount
       ||'    '  --Filler
       ||'               '  --Last 8 weeks Earning
       ||'               '  --Client Specific field: job Code
       ||'                    '  --Client Specific field
       ||'                    '  --Client Specific Field
       ||rpad(CASE
            WHEN pco.r_type = 'LO' then pco.description
            ELSE null
          END,30)  --Client Specific Field
       ||'                              '  --Client Specific Field
       ||'Y'  --Leave Management Services Indicator
       ||'Y'  --Employee Covered by FML 50/75 Rule
       ||' '  --Key Employee Indicator
       ||'    '  --Sick Leave Hours Remaining
       ||'    '  --Vacation Hours Remaining
       ||' '  --Foreced Eligibility 
       ||'      '  --Last 12 Month Paid Hours
       ||'W  '  --Work Schedule Type
       ||'        '  --Work Schedule from Date
       ||'    '  --Schedule Hours Per Week
       ||'    '  --WS Monday
       ||'    '  --WS Tuesday
       ||'    '  --WS Wednesday
       ||'    '  --WS Thursday
       ||'    '  --WS Friday
       ||'    '  --WS Saturday
       ||'    '  --WS Sunday
       ||'                                                                                                                                                                                                                                                                              '
       
   --     "Output"
 from PROD.EMPLOYEE emp,
      PROD.PAEMPLOYEE pem,
      PROD.BENEFIT ben,
      PROD.PRRATEHIST prh,
      PROD.PAPOSITION pos,
      PROD.HRSUPER hsu,
      PROD.EMPLOYEE emp1,
      PROD.ZZWEBUSER zzw,
      PROD.PCODES pco
--      PROD.PRTIME prt
      
where emp.company = pem.company
and   emp.employee = pem.employee
and   emp.company = ben.company
and   emp.employee = ben.employee
and   emp.company = prh.company 
and   emp.employee = prh.employee
and   emp.company = pos.company
and   emp.position = pos.position
and   emp.company = hsu.company
and   emp.supervisor = hsu.code
and   hsu.company = emp1.company 
and   hsu.employee = emp1.employee 
and   zzw.company = hsu.company
and   upper(zzw.userid) = hsu.user1
and   pem.locat_code = pco.code
--and   emp.company = prt.company 
--and   emp.employee = prt.employee
and   emp.emp_status not in('T','TP','SR','LG','LU')
and   emp.employee = 19330
and   ben.plan_code in ('LTD','LTD1','LTD2')  --is STD not being counted?
AND   ben.STOP_DATE=TO_DATE ('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
and   prh.beg_date = (select max(prh.beg_date)
          from prod.prratehist prh
          where prh.employee = emp.employee)
and   pos.effect_date = (select max(pos.effect_date)
          from prod.paposition pos
          where pos.position = emp.position)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41803492
Off topic items:

I would like to encourage you to stop trying to solve all the formatting problems UNTIL you have solved ALL the LOGIC problems. It is soooooo much easier to add/debug a simple query than it is to deal with both at the same time. Leave formatting (e.g. to_char, lpad,rpad ) to the very last thing. You can even do that "layer" of formatting "on top" of a "derived table" e.g.
SELECT
      to_char(REPLACE(d.fica_nbr, '-'), '999999999')
    ||rpad(substr(emp.employee,1,15),15) 
FROM ( /* now the "simple query" goes here, no formats are applid, just gather the needed data */
      SELECT
            emp.fica_nbr, emp.employee
      FROM emp
    ) d

Open in new window

Also, way back in 1992 ANSI standards adopted an improved syntax for joining tables (~24 years ago). A simple way to implement this standard is to deny yourself the use of commas in the FROM clause. What this standard achieves is a more maintainable query and it also vastly simplifies the WHERE clause because join predicates are placed into the JOIN syntax.
SELECT
    *
FROM PROD.EMPLOYEE emp
    INNER JOIN PROD.PAEMPLOYEE pem ON emp.company = pem.company AND emp.employee = pem.employee
    INNER JOIN PROD.BENEFIT ben ON emp.company = ben.company AND emp.employee = ben.employee
    INNER JOIN PROD.PRRATEHIST prh ON emp.company = prh.company AND emp.employee = prh.employee
    INNER JOIN PROD.PAPOSITION pos ON emp.company = pos.company AND emp.position = pos.position
    INNER JOIN PROD.HRSUPER hsu ON emp.company = hsu.company AND emp.supervisor = hsu.code
    INNER JOIN PROD.EMPLOYEE emp1 ON hsu.company = emp1.company AND hsu.employee = emp1.employee
    INNER JOIN PROD.ZZWEBUSER zzw ON hsu.user1 = UPPER(zzw.userid) AND hsu.company = zzw.company
    INNER JOIN PROD.PCODES pco ON pem.locat_code = pco.code
WHERE emp.emp_status NOT IN ('T', 'TP', 'SR', 'LG', 'LU')
    AND emp.employee = 19330
    AND ben.plan_code IN ('LTD', 'LTD1', 'LTD2')  --is STD not being counted?
    AND ben.STOP_DATE = TO_DATE('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
    AND prh.beg_date = (
          SELECT
                MAX(prh.beg_date)
          FROM prod.prratehist prh
          WHERE prh.employee = emp.employee
    )
    AND pos.effect_date = (
          SELECT
                MAX(pos.effect_date)
          FROM prod.paposition pos
          WHERE pos.position = emp.position
    )

Open in new window

ps: I placed your 165 line query into a code block to simplify navigation (try to read questions to a phone you will soon learn why 165 lines of code is a pain).
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41803495
On Topic:

You don't tell us where or how you want to include that "subquery":
as a new column?
is it a new row? (like a "grand total"?)

Perhaps most mysterious of all is how does the table PROD.PRTIME relate to PROD.EMPLOYEE?
e.g. would adding the subquery as another join work for the query (hours_worked becomes a new column)
INNER JOIN (
          SELECT
                prt.employee
              , SUM(prt.hours) HOURS_WORKED
          FROM PROD.PRTIME prt
          WHERE (prt.tr_date >= add_months(trunc(sysdate, 'mm'), -12)
                AND prt.tr_date < trunc(sysdate)
                )
                AND prt.pay_sum_grp IN ('REG', 'PTO', 'OT')
          GROUP BY
                prt.employee
        ) t ON emp.employee = t.employee

Open in new window

nb, I looked at other questions for this suggestion

https://www.experts-exchange.com/questions/28970417/Number-Format.html
it looks like you want a new column called HOURS_WORKED

https://www.experts-exchange.com/questions/28970492/Last-12-months-of-data.html
it appears the date range calculation is incorrect, so I have used my suggested alternative
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Sums/Grouping 7 51
Fastest way to replace data in Oracle 5 51
Query Peformance + mulitple query plans 9 48
Shredding xml into an oracle 11g Database 2 27
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 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

16 Experts available now in Live!

Get 1:1 Help Now