Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-11
9
Medium Priority
?
106 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 32

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 32

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

636 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