Re: SQL Power !!

Online queries in Forms, and no one is complaining of performance ? Hemant
At 08:21 AM 09-05-03 -0800, you wrote:
>Here is what was caught by my co-worker recently.
>One of the Developers, of our Preferred Vendor, actually wrote this stuff.
>This is invoked by the one of the forms on the intranet Web
>application. I was told there are quite a few scripts like this one.
>Performance issues? No one is complaining (yet) .... so who cares :)
>Enjoy...
>- Kirti
>SELECT DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa,
>' '),
>NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
>NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '),
>NVL(LIST1.listed_tn_line_no, ' '),
>NVL(ADDR1.house_no, ' '), NVL(ADDR1.house_no_suff, ' '),
>NVL(ADDR1.pre_directional, ' '),
>NVL(ADDR1.street_name, ' '), NVL(ADDR1.post_directional, ' '),
>NVL(ADDR1.sub_location, ' '),
>NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five, ' '),
>NVL(ADDR1.state_abbr_override, ' '),
>LIST1.listing_status, NVL(CUST1.customer_type, ' '),
>NVL(CUST1.home_data_base, ' '),
>NVL(CUST1.default_market_code, ' '),
>NVL(TELCO.TELCO_CODE, ' '), NVL(TELCO.TELCO_NAME, ' '),
>NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),' '), NVL((select 'Y' from
>query q, dir_curr_issue
>where q.customer_id = cust1.customer_id
>and q.query_status in (1,3,4)
>and q.product_code = dir_curr_issue.directory_code
>and q.product_issue_num >= dir_curr_issue.directory_issue_num
>and rownum = 1), 'N' ) ,
>NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
>NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
>NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0),
>NVL(CUST1.PI_AMOUNT, 0),
>HEADING.short_heading_name,
>NVL(CUST1.CUST_PROSPECT_CODE, 0),
>NVL(CUST1.RETIREMENT_STATUS, ' '),
>(SELECT nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
>FROM
>DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0,
>ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE
>DIR_CURR_ISSUE0
>WHERE
>DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id
>and DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code
>and DIRECTORY_SUMMARY0.product_issue_num =
>DIR_CURR_ISSUE0.directory_issue_num
>and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
>and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num
>and e.directory_del_ind != :1
>and DIRECTORY_SUMMARY0.canvass_code = ''
>and DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
>and DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+)
>AND :2 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))
>,(CASE WHEN EXISTS( SELECT 1
>FROM
>DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS
>WHERE
>CUST1.customer_id = DS.customer_id
>AND DS.product_code = DC.directory_code
>AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
>AND DS.curr_special_flag = 20 and rownum = 1)
>THEN 'Y'
>ELSE 'N' END )
>, (CASE WHEN EXISTS ( SELECT 1
>FROM
>ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1
>WHERE
>IC.customer_id = CUST1.customer_id
>AND DIR1.customer_id = CUST1.customer_id
>AND IC.PRODUCT = DIR1.product_code
>AND IC.issue_number = DIR1.product_issue_num
>AND IC.DELETE_INDICATOR ='N' )
>THEN 'Y'
>ELSE 'N' END ),
>(CASE WHEN EXISTS ( SELECT 1
>FROM
>DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1
>WHERE
>DIR1.customer_id = CUST1.customer_id
>AND DIR1.product_code = DI.directory_code
>AND DIR1.product_issue_num = DI.directory_issue_num
>AND ADD_MONTHS(TO_DATE(:3,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE
>AND TO_DATE(:4,'YYYYMMDD') < DI.TELCO_CLOSE_DATE
>AND DI.TELCO_CLOSE_DATE IS NOT NULL)
>THEN 'Y'
>ELSE 'N' END )
>, NVL((select CONTACT.CONTACT_NAME from CONTACT
>where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ')
>FROM LISTING LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY,
>TELCO, DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR
>WHERE
>CUST1.main_main_listing_id = LIST1.listing_id(+) AND
>LIST1.last_version_ind(+) = :5 AND
>LIST1.listed_address_id = ADDR1.address_id(+) AND
>TELCO.TELCO_CODE = LIST1.TELCO_ID AND
>DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND
>DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND
>DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM AND
>ASSIGNMENT.SALES_REP = :6 AND
>CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND
>NOT EXISTS
>(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment
>ASSIGNMENT_T,DIR_CURR_ISSUE DC
>WHERE DS.customer_id = CUST1.customer_id
>AND DS.PRODUCT_CODE = DC.DIRECTORY_CODE
>AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
>AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
>AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+)
>AND :7 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)
>AND DS.RETIREMENT_STATUS!= :8 ) AND
>DIRECTORY_SUMMARY.RETIREMENT_STATUS in (:9, :10) AND
>CUST1.customer_id = ITEM_DIR.customer_id AND
>DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND
>DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND
>ITEM_DIR.LAST_VERSION_IND = 'Y' AND
>( ITEM_DIR.heading_code = :11) AND
>rownum <= :12
>AND DIRECTORY_SUMMARY.CURRENT_ASSIGN_ID = ASSIGNMENT.ASSIGNMENT_ID UNION
>SELECT
>DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, ' '),
>NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
>NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '),
>NVL(LIST1.listed_tn_line_no, ' '),
>NVL(ADDR1.house_no, ' '), NVL(ADDR1.house_no_suff, ' '),
>NVL(ADDR1.pre_directional, ' '),
>NVL(ADDR1.street_name, ' '), NVL(ADDR1.post_directional, ' '),
>NVL(ADDR1.sub_location, ' '),
>NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five, ' '),
>NVL(ADDR1.state_abbr_override, ' '),
>LIST1.listing_status, NVL(CUST1.customer_type, ' '),
>NVL(CUST1.home_data_base, ' '),
>NVL(CUST1.default_market_code, ' '),
>NVL(TELCO.TELCO_CODE, ' '), NVL(TELCO.TELCO_NAME, ' '),
>NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),' '), NVL((select 'Y' from
>query q, dir_curr_issue
>where q.customer_id = cust1.customer_id
>and q.query_status in (1,3,4)
>and q.product_code = dir_curr_issue.directory_code
>and q.product_issue_num >= dir_curr_issue.directory_issue_num
>and rownum = 1), 'N' ) ,
>NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
>NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
>NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0),
>NVL(CUST1.PI_AMOUNT, 0),
>HEADING.short_heading_name,
>NVL(CUST1.CUST_PROSPECT_CODE, 0),
>NVL(CUST1.RETIREMENT_STATUS, ' '),
>(SELECT nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
>FROM
>DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0,
>ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE
>DIR_CURR_ISSUE0
>WHERE
>DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id
>and DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code
>and DIRECTORY_SUMMARY0.product_issue_num =
>DIR_CURR_ISSUE0.directory_issue_num
>and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
>and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num
>and e.directory_del_ind != :13
>and DIRECTORY_SUMMARY0.canvass_code = ''
>and DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
>and DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+)
>AND :14 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))
>,(CASE WHEN EXISTS( SELECT 1
>FROM
>DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS
>WHERE
>CUST1.customer_id = DS.customer_id
>AND DS.product_code = DC.directory_code
>AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
>AND DS.curr_special_flag = 20 and rownum = 1)
>THEN 'Y'
>ELSE 'N' END )
>, (CASE WHEN EXISTS ( SELECT 1
>FROM
>ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1
>WHERE
>IC.customer_id = CUST1.customer_id
>AND DIR1.customer_id = CUST1.customer_id
>AND IC.PRODUCT = DIR1.product_code
>AND IC.issue_number = DIR1.product_issue_num
>AND IC.DELETE_INDICATOR ='N' )
>THEN 'Y'
>ELSE 'N' END ),
>(CASE WHEN EXISTS ( SELECT 1
>FROM
>DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1
>WHERE
>DIR1.customer_id = CUST1.customer_id
>AND DIR1.product_code = DI.directory_code
>AND DIR1.product_issue_num = DI.directory_issue_num
>AND ADD_MONTHS(TO_DATE(:15,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE
>AND TO_DATE(:16,'YYYYMMDD') < DI.TELCO_CLOSE_DATE
>AND DI.TELCO_CLOSE_DATE IS NOT NULL)
>THEN 'Y'
>ELSE 'N' END )
>, NVL((select CONTACT.CONTACT_NAME from CONTACT
>where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ')
>FROM LISTING LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY,
>TELCO, DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR
>WHERE
>CUST1.main_main_listing_id = LIST1.listing_id(+) AND
>LIST1.last_version_ind(+) = :17 AND
>LIST1.listed_address_id = ADDR1.address_id(+) AND
>TELCO.TELCO_CODE = LIST1.TELCO_ID AND
>DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND
>DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND
>DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM AND
>ASSIGNMENT.SALES_REP = :18 AND
>CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND
>NOT EXISTS
>(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment
>ASSIGNMENT_T,DIR_CURR_ISSUE DC
>WHERE DS.customer_id = CUST1.customer_id
>AND DS.PRODUCT_CODE = DC.DIRECTORY_CODE
>AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
>AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
>AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+)
>AND :19 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)
>AND DS.RETIREMENT_STATUS!= :20 ) AND
>DIRECTORY_SUMMARY.RETIREMENT_STATUS in (:21, :22) AND
>CUST1.customer_id = ITEM_DIR.customer_id AND
>DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND
>DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND
>ITEM_DIR.LAST_VERSION_IND = 'Y' AND
>( ITEM_DIR.heading_code = :23) AND
>rownum <= :24
>AND DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID is not NULL AND
>DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID = ASSIGNMENT.ASSIGNMENT_ID
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).