RE: Query from hell

Is there some criteria affecting which county_factor_id ('01', '02',
etc.) is concatenated? 
I couldn't find one looking at your query, must be missing something.
 
But, if there is one, you could use DECODE instead of 67 UNIONs:
 
sd2.sercs_data_id || DECODE(<criteria>, <val1>, '01', <val2>, '02',...)
 
Igor

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Paula Stankus
Sent: Thursday, October 19, 2006 9:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: re: Query from hell


Listed below is a sample of some horrific SQL.  As you can see it joins
multiple tables together (large ones) 67 times only to add a literal
(the county number) to a specific field each time.  I know that there is
a better way to do this.  I am thinking a temporary table with the
joined data then just reading through the data to produce the one
concatenated field but I was wondering if an inline view would be
better.  
 
Any suggestions would be greatly appreciated.
 
prompt create mv_plan_county_factor;
DROP MATERIALIZED VIEW MV_PLAN_COUNTY_FACTOR;
alter session set transaction use rollback segment=
CREATE MATERIALIZED VIEW MV_PLAN_COUNTY_FACTOR
TABLESPACE USERS
NOLOGGING
BUILD IMMEDIATE
REFRESH FORCE
NEXT TRUNC(SYSDATE) + 200 + 3 / 24
AS
SELECT sd.sercs_data_id PLAN_ID
,sd2.sercs_data_id || '01' COUNTY_FACTOR_ID
,ds.created_by_id
,SYSDATE date_created
FROM idcs.sercs_data sd
,idcs.sercs_data sd2
,idcs.sercs_data sd3
,idcs.data_submission ds
,idcs.data_collection dc
,ifass.filing f
,ifass.filing_component fc
,ifass.filing_authority fa
,edms.filing_main fm
WHERE ds.status_ind = 1 -- APPROVED
AND f.filing_status_id = 5 -- FINAL ACTION
AND ds.data_collection_id = 6 -- SERCS
AND ds.no_data_ind = 0 -- DATA FILING
AND NOT sd.column_fn IS NULL
AND sd2.column_bv = sd.column_fw
AND
SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd
3.column_d) = 1
AND fm.final_disposition = 'APPROVED'
-- JOINS
AND sd.data_submission_id = ds.data_submission_id
AND sd2.data_submission_id = sd.data_submission_id
AND sd3.data_submission_id = sd.data_submission_id
AND ds.data_collection_id = dc.data_collection_id
AND ds.filing_id = f.filing_id
AND f.filing_id = fc.filing_id
AND fc.filing_component_id = fa.filing_component_id
AND f.file_log_num = fm.file_log_num
UNION
SELECT sd.sercs_data_id PLAN_ID
,sd2.sercs_data_id || '02' COUNTY_FACTOR_ID
,ds.created_by_id
,SYSDATE date_created
FROM idcs.sercs_data sd
,idcs.sercs_data sd2
,idcs.sercs_data sd3
,idcs.data_submission ds
,idcs.data_collection dc
,ifass.filing f
,ifass.filing_component fc
,ifass.filing_authority fa
,edms.filing_main fm
WHERE ds.status_ind = 1
AND f.filing_status_id = 5
AND ds.data_collection_id = 6
AND ds.no_data_ind = 0
AND NOT sd.column_fn IS NULL
AND sd2.column_bv = sd.column_fw
AND
SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd
3.column_d) = 1
AND fm.final_disposition = 'APPROVED'
AND sd.data_submission_id = ds.data_submission_id
AND sd2.data_submission_id = sd.data_submission_id
AND sd3.data_submission_id = sd.data_submission_id
AND ds.data_collection_id = dc.data_collection_id
AND ds.filing_id = f.filing_id
AND f.filing_id = fc.filing_id
AND fc.filing_component_id = fa.filing_component_id
AND f.file_log_num = fm.file_log_num
UNION
SELECT sd.sercs_data_id PLAN_ID
,sd2.sercs_data_id || '03' COUNTY_FACTOR_ID
,ds.created_by_id
,SYSDATE date_created
FROM idcs.sercs_data sd
,idcs.sercs_data sd2
,idcs.sercs_data sd3
,idcs.data_submission ds
,idcs.data_collection dc
,ifass.filing f
,ifass.filing_component fc
,ifass.filing_authority fa
,edms.filing_main fm
WHERE ds.status_ind = 1
AND f.filing_status_id = 5
AND ds.data_collection_id = 6
AND ds.no_data_ind = 0
AND NOT sd.column_fn IS NULL
AND sd2.column_bv = sd.column_fw
AND
SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd
3.column_d) = 1
AND fm.final_disposition = 'APPROVED'
AND sd.data_submission_id = ds.data_submission_id
AND sd2.data_submission_id = sd.data_submission_id
AND sd3.data_submission_id = sd.data_submission_id
AND ds.data_collection_id = dc.data_collection_id
AND ds.filing_id = f.filing_id
AND f.filing_id = fc.filing_id
AND fc.filing_component_id = fa.filing_component_id
AND f.file_log_num = fm.file_log_num

Other related posts: