SQL guru's, please help

From: Ken Halsted <kenman_at_mail.snider.net>
Date: Tue, 23 Mar 1999 08:22:29 -0600
Message-ID: <922198927.960.13_at_news.remarQ.com>

I came up with a SQL expression for a Report and I was needing some expert advice on a faster way to do it. I'm using Oracle Reports v3.0.

The tables and columns involved that I'm trying to join are:

LABOR_HIS_EARN_DED
   control_1 (#)
   control_2 (#)
   employee_number (#)

   h_l_multiple_no (#)
   hed_nbr_l_h (#)
   hed_amount_l_h

EMPLOYEE_PAYMT_LH
   control_1 (#)
   control_2 (#)
   employee_number (#)
   h_l_multiple_no (#)
   record_group
   paymnt_date

CYB_GAP_01
   control_1 (#)
   control_2 (#)
   employee_number (#)
   hed (#)
   cyb_gap_02_vendor_number
   reference
   case_number

Notice that CYB_GAP_01 ties an HED with a vendor number. This is what I want to report on based on a certain payment date range.

My question is, how can I join these tables (without doing a FULL table scan) so that I can see all the employee's by Vendor Number within a certain payment date range. Within each vendor number I want to see the hed_amount from LABOR_HIS_EARN_DED. And only a record group of 'H' from the EMPLOYEE_PAYMT_LH table. The way I did it is below:

SELECT

       EPL.CONTROL_1,
       EPL.CONTROL_2,
       EPL.EMPLOYEE_NUMBER,
       LHED.HED_NBR_L_H,
       LHED.HED_AMOUNT_L_H,
       EPL.RECORD_GROUP,
       EPL.PAYMNT_DATE,
       CYB1.CYB_GAP_02_VENDOR_NUMBER "VENDOR_NUMBER",
       CYB1.REFERENCE,
       CYB1.CASE_NUMBER
FROM EMPLOYEE_PAYMT_LH EPL,
     LABOR_HIS_EARN_DED LHED,
     CYB_GAP_01 CYB1

WHERE ( EPL.CONTROL_1 = LHED.CONTROL_1
    AND LHED.CONTROL_1 = CYB1.CONTROL_1
    AND EPL.CONTROL_2 = LHED.CONTROL_2
    AND LHED.CONTROL_2 = CYB1.CONTROL_2
    AND EPL.EMPLOYEE_NUMBER = LHED.EMPLOYEE_NUMBER     AND LHED.EMPLOYEE_NUMBER = RPAD(CYB1.EMPLOYEE_NUMBER,10,' ')     AND EPL.H_L_MULTIPLE_NO = LHED.H_L_MULTIPLE_NO )     AND LHED.HED_NBR_L_H = CYB1.HED
    AND ( EPL.PAYMNT_DATE >= :P_START_DATE AND EPL.PAYMNT_DATE <= :P_END_DATE )
    AND SUBSTR(EPL.RECORD_GROUP,2,1) = 'H'     AND EPL.CONTROL_1 = :P_CORPORATION
    AND EPL.CONTROL_2 = :P_COMPANY
    &P_COND1
ORDER BY EPL.CONTROL_1, EPL.CONTROL_2, EPL.EMPLOYEE_NUMBER, EPL.PAYMNT_DATE Then in REPORTS 3.0, I put Vendor_Number in it's own group and broke on that.

Your help would be very much appreciated.

--
_________________
Kenneth W. Halsted
Mountaire Corporation
ph: (501) 399-8812
url: http://www.mountaire.com
email: kenman_at_mail.snider.net

Received on Tue Mar 23 1999 - 15:22:29 CET