Solved

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

Posted on 2016-09-11
9
67 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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
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
Comment Utility
Couldn't think of a better way to get the last 12 months worth of data.
I will try your code.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 16

Expert Comment

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

Thanks !
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

7 Experts available now in Live!

Get 1:1 Help Now