RE: SQL Power !!

Been there, done that, dubugging is a REAL pain. Many times performance is as well!

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Friday, May 09, 2003 1:59 PM
To: Multiple recipients of list ORACLE-L

John & Raj,

 Now, I feel more comfortable :)

 No, I have not seen such large SQLs till now.. and I wish to stay away from them in the future.   

• Kirti

-----Original Message-----
Sent: Friday, May 09, 2003 12:27 PM
To: Multiple recipients of list ORACLE-L

Kirti,

You haven't seen the Oracle Applications code from our 'preferred vendor' yet!! How about 100's of such SQLs, all written in Redw**d Sh*res?

John

> -----Original Message-----
> From: Deshpande, Kirti [mailto:kirti.deshpande_at_verizon.com]
> Sent: Friday, May 09, 2003 9:22 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Power !!
>
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: DGoulet_at_vicr.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).