Re: sql select sum stored procedure

From: Ken Chesak <datavector_at_hotmail.com>
Date: 12 Feb 2003 07:11:12 -0800
Message-ID: <3f2f39c4.0302120711.4faef16@posting.google.com>

Dirk I was able to resolve this by changing the order of things... Thanks
New SQL that works...

select rh.case_no into v_ct
FROM w_wage_employer rwe, w_rg101 rh, w_wage_case rwc

WHERE rwe.cd_disposition         = 'NONE'         AND
 rh.cd_case_category       IN ('06','08','09')    AND
 rh.case_status             = '2'                 AND
 rwc.id_rg101               = rh.id_rg101         AND 
 rwc.id_wage                = rwe.id_wage         AND

/* moved amt2 first and then SELECT */
 amt2 <= (SELECT rwe.amt_wages - SUM(gwsc.amt_payment)  FROM w_gws_client gwsc, w_wage_employer rwe, w_wage_case rwc

    WHERE gwsc.cd_source            = rwe.cd_source              AND
          gwsc.case_no              = rwe.case_no                AND
          gwsc.dt_run               = rwe.dt_run                 AND
          gwsc.log_no               = rwe.log_no                 AND
          gwsc.cd_income            = '1'                        AND
	  gwsc.is_income            = 'I'                        AND
          gwsc.dt_claim             >= rwc.dt_month_1            AND
          gwsc.dt_claim             <= rwc.dt_month_3            AND
          gwsc.client_no             = rwe.client_no             AND
          substr(rwe.employer_name,1,4) =
substr(gwsc.employer_name,1,4) AND
          soundex(rwe.employer_name) = soundex(gwsc.employer_name) AND
          gwsc.gws_seq = (SELECT MAX(gws_seq)
              FROM w_gws_client gwsc0
              WHERE gwsc0.id_gws_client        = gwsc.id_gws_client   
    AND
                    gwsc0.client_no            = gwsc.client_no       
    AND
		    gwsc0.employer_name        = gwsc.employer_name        AND
                    EXISTS (SELECT 1
                               FROM w_gws_client gwsc1
                               WHERE gwsc1.id_gws_client        =
gwsc0.id_gws_client        AND
                                     gwsc1.gws_seq              =
gwsc0.gws_seq              AND
                                     gwsc1.client_no            =
gwsc0.client_no            AND
		                     gwsc1.employer_name        =
gwsc0.employer_name        AND
                                     gwsc1.dt_claim             =
rwc.dt_month_1)            AND
                    EXISTS (SELECT 1
                               FROM w_gws_client gwsc2
                               WHERE gwsc2.id_gws_client        =
gwsc0.id_gws_client        AND
                                     gwsc2.gws_seq              =
gwsc0.gws_seq              AND
                                     gwsc2.client_no            =
gwsc0.client_no            AND
		                     gwsc2.employer_name        =
gwsc0.employer_name        AND
                                     gwsc2.dt_claim             =
rwc.dt_month_2)            AND
                    EXISTS (SELECT 1
                               FROM w_gws_client gwsc3
 			       WHERE gwsc3.id_gws_client        = gwsc0.id_gws_client     
  AND
                                     gwsc3.gws_seq              =
gwsc0.gws_seq              AND
                                     gwsc3.client_no            =
gwsc0.client_no            AND
		                     gwsc3.employer_name        =
gwsc0.employer_name        AND
                                     gwsc3.dt_claim             =

rwc.dt_month_3)));

dbms_output.put_line('proc_FS_E01: Records updated: ??'); Received on Wed Feb 12 2003 - 09:11:12 CST