Oracle-L: RE: SQL Power !!

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 09 May 2003 08:47:16 -0800
Message-ID: <F001.00595542.20030509084716@fatcity.com>

I have one that run to about 37 pages when printed.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

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

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,

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 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: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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).

text/plain attachment: ESPN_Disclaimer.txt

Received on Fri May 09 2003 - 11:47:16 CDT