RE: Query from hell

I got the impression, from the QFH (Query From Hell (TM)), that the user wanted the identical bunch of rows back 67 times, with one value being changed in each 67th occurrence, from $VALUE to $VALUE_01, $VALUE_02, etc.. I would change the QFT to this (adding a cartesian join to 67 rows selected from dual):  

SELECT sd.sercs_data_id PLAN_ID
,sd2.sercs_data_id || to_char (counter_table.rn, 'FM09') as 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

/*** addition here ***/ , (select rn from (select rownum as rn from dual connect by level <= 67)) counter_table 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,sd3.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

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Igor Neyman Envoyé : jeudi, 19. octobre 2006 09:56
À : paulastankus_at_yahoo.com; oracle-l_at_freelists.org Objet : 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paula Stankus Sent: Thursday, October 19, 2006 9:52 AM To: oracle-l_at_freelists.org
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,sd3.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,sd3.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,sd3.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

--
http://www.freelists.org/webpage/oracle-l