Re: Query from hell

Option 4 : SELECT sd.sercs_data_id PLAN_ID ,sd2.sercs_data_id || country_code 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 ,(select TRIM(to_char(level,'00')) country_code from dual connect by level <= 97) 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 ----- Original Message ---- From: Mindaugas Navickas <mnavickas_at_yahoo.com> To: ineyman_at_perceptron.com; paulastankus_at_yahoo.com; oracle-l_at_freelists.org Sent: Thursday, October 19, 2006 2:13:48 PM Subject: Re: Query from hell Hi Paula, I could't find criteria that differs that country_factor_id - assuming that all queries returns the same data and then only appends '01','02'... option 1: you can put in a table those values '01','02'... 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 ,(select country_code from countru_codes) 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 Option 2: You can simulate that table with (agree - still doesn't look nice :-)) (select '01' from dual UNION ALL select '02' from dual UNION ALL ... ) Option 3: Simulate table with (if country codes are continious values) (select TRIM(to_char(rownum,'00')) from all_objects where rownum < 68) Regards Mindaugas

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