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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la 
part de Igor Neyman
Envoyé : jeudi, 19. octobre 2006 09:56
À : paulastankus@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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,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

Other related posts: