?
Solved

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

Posted on 2016-09-11
9
Medium Priority
?
98 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
[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
  • 2
  • 2
  • +2
9 Comments
 
LVL 38

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 29

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 35

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
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

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
 
LVL 29

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 35

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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

777 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