Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SQL Power !!

SQL Power !!

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 09 May 2003 08:21:44 -0800
Message-ID: <F001.00595456.20030509082144@fatcity.com>


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).
Received on Fri May 09 2003 - 11:21:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US