|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-14 : 11:47:22
|
Rico writes "OK GUYS HERE IS ONE THAT WILL SURELY STUMP YOU
I have a pl/sql statement that iterates thorugh a temp table to fetch data. The issue is this. There are several unique business rules that requires the script to fetch the same columns and rows. The only diference is that the AND and GROUP BY inclusion will change. The WHERE statement is pretty much the same. Here is a sample of the script.
--This is the sql statement that I need to reuse
INSERT INTO wins_inv_temp_03
(bill_cust_id, dlvy_cust_id, bill_period, occur_period, prgm_id, version,
impl_code, wtr_type_id, class_id, meter_id, rec_type, vol)
(SELECT bill_cust_id, dlvy_cust_id, bill_period, occur_period, prgm_id,
version, impl_code, wtr_type_id, class_id, meter_id, 3, vol
FROM wins_acct_vol_trans
WHERE ((cert_id is not null and (substr(cert_id,1,2) = 'G2' or substr(cert_id,1,2) in ('GL', 'CL')))
--Here are the unique business rules (there are 7 in total)
-----------------------Rule # 1--------------------------------
and (trans_type in ('DLVY','CERT','STOR')
or (trans_type = 'PLAN' and prgm_id in ('CUP', 'CSP')))
and (cert_id is null or cert_id not in ('G2793','G2794','G2799','G2800','G2801', 'G2802','G28','G29', 'G230','G231','G2665','G2554','G21105'))
and not (prgm_id in ('CYC','CSP') and dispn_id = 'S')
and not (prgm_id in ('CYC') and bill_cust_id = 'CM' and occur_period > 199807 and occur_period < 199906)
and not (prgm_id in ('CSP','CUP') and planning is not null and planning = 'ACIN')
and prgm_id in ('CUP','DEMO','DRT','DS','LPPDS','LPPRE','GRP','CYC','CSP','MWDST')
and usage_type_id <> 'LOSS'
-- and bill_period <= 199901
and dlvy_mthd_id not in ('PT','WH'));
------------Rule # 2-----------------------------------------
and (trans_type in ('DLVY','CERT','STOR')
or (trans_type = 'PLAN' and prgm_id in ('CUP', 'CSP')))
and (cert_id is null or cert_id not in ('G2793','G2794','G2799','G2800','G2801', 'G2802','G28','G29', 'G230','G231','G2665','G2554','G21105'))
and not (prgm_id in ('CYC','CSP') and dispn_id = 'S')
and not (prgm_id in ('CYC') and bill_cust_id = 'CM' and occur_period > 199807 and occur_period < 199906)
and not (prgm_id in ('CSP','CUP') and planning is not null and planning = 'ACIN')
and prgm_id in ('CUP','DEMO','DRT','DS','CYC','CSP','MWDST')
and (usage_type_id <> 'LOSS' and prgm_id <> 'PRED')
-- and bill_period <= 199901
and bill_cust_id = o.org_id
and o.short_name = sBillingCustomer
and sProgram is null
and (sMeter is null or (sProgram = 'MWDST' and sMeter = meter_id))
and dlvy_mthd_id not in ('PT','WH'));
------------------Rule # 3----------------------------------
and p.period_date >= p.start_fiscal_date
and p.period_date <= p.end_fiscal_date
and u.occur_period <= nBillPeriod
GROUP BY u.bill_cust_id, u.dlvy_cust_id, u.prgm_id, u.version, u.impl_code,
To_Char(p.start_fiscal_date,'DD-MON-YYYY') || To_Char(p.end_fiscal_date,'DD-MON-YYYY'), u.class_id, u.meter_id, 4);
--------------------PLEASE PLEASE HELP-----------------------
I've tried cursors but they do not take unique parameters for the AND and GROUP BY Clause.
My environment: WINDOWS NT 4.0 SERVER and ORACLE DATABASE.
--------------------------------------------------------------" |
|
Oracle: How to reference SQL block and add unique