0

I am building a SQL query in Oracle 10g that needs to sum overtime hours for the base table, but needs fields from the parent tables as well. I am looking for the fastest way to build the query. The Project_ID in the first three tables is the same, where the Work_ID is one-to-many to the fourth table.

Table J
Project_ID VARCHAR(6)
J_Title VARCHAR(25)

Table K
Project_ID VARCHAR(6)
K_Title VARCHAR(25)

Table L
Project_ID VARCHAR(6)
L_Title VARCHAR(25)
Work_ID VARCHAR(6)

Table C
Work_ID VARCHAR(6)
L_Title VARCHAR(25)
CHRG_DT DATE
WRK_HR NUMBER(5,2)

My best guess looks like this (It's simplified, and the date is actually passed as a variable to the stored procedure):

SELECT
 J.J_Title,
 K.K_Title,
 L.L_Title,
 L.Work_ID,
SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
FROM C,
L,
K,
J
WHERE J.Project_ID = 'ABC'
AND K.Project_ID = J.Project_ID
AND L.Project_ID = K.Project_ID
AND C.Work_ID = L.Work_ID
GROUP BY L.Work_ID,
L.Project_ID,
K.Project_ID,
J.J_Title,
K.K_Title,
L.L_Title

UPDATE: Omitted two fields in Table C. Changed Group By fields, and added Work_ID to output for clarity. Sorry for changes, I have to type this from another network, can't just cut and paste. Also trying to leave out unrelated fields.

This is the output I am getting:

J_Title K_Title L_Title Work_ID WRK_HR
------- ------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC  DEF         30 
JOB_ABC KO_ABC  LO_ABC  DFG          3

This is the output I am looking for:

J_Title K_Title L_Title WRK_HR
------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC      33
5
  • Have you actually tried that? Does it work? Nov 25, 2013 at 15:05
  • Yes, but it groups by Work_ID, and I need it to group by Project_ID. I need help with the structure and optimizing; I am thinking a subquery that SELECTS from table C for each row of Table L will be really slow.
    – CigarDoug
    Nov 25, 2013 at 15:28
  • Can you post the complete table definitions. Your query is referencing columns that are not part of your definitions. Nov 25, 2013 at 15:33
  • My mistake. Added omitted fields.
    – CigarDoug
    Nov 25, 2013 at 15:56
  • @CigarDoug: Would you be able to add a simple sample of the current output, and a simple sample of the desired output? Nov 25, 2013 at 15:58

1 Answer 1

1

I think you might be able to get the results you want with:

select j_title, k_title, l_title, sum(wrk_hr) from
(
  SELECT
   J.J_Title,
   K.K_Title,
   L.L_Title,
   L.Work_ID,
  SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
  FROM C, L, K, J
  WHERE J.Project_ID = 'ABC'
  AND K.Project_ID = J.Project_ID
  AND L.Project_ID = K.Project_ID
  AND C.Work_ID = L.Work_ID
  GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
)
group by j_title, k_title, l_title;
2
  • That works, thanks. I understand you have to include fields in the Group By statement to pass them to the parent query, but does the order of the Group By matter?
    – CigarDoug
    Nov 26, 2013 at 12:33
  • @CigarDoug: The order of columns in GROUP BY normally does not matter (try it out with some test data and see!), but if you start including grouping functions (oracle-base.com/articles/misc/…) such as ROLLUP or CUBE then it can matter. Nov 26, 2013 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.