I generated alternatives for a sql statement and then tried to ‘run selected’. The original statement runs ok, but the 1st alternate didn’t finish. I copied the first alternate to toad and it runs fine returning data in about 5 seconds–so the alt 1 statement it generates is valid. In the tuning lab, if I right click the the alt 1 sql statement and select ‘retrieve results’, the window appears with no sql statement in it. This is the statement:
select distinct SPRIDEN_PIDM
from SPRIDEN,
(select SORLCUR_PIDM “SAVCCUR_PIDM”,
SORLCUR_SEQNO “SAVCCUR_SEQNO”,
SORLCUR_LMOD_CODE “SAVCCUR_LMOD_CODE”,
SORLCUR_TERM_CODE “SAVCCUR_TERM_CODE”,
SORLCUR_KEY_SEQNO “SAVCCUR_KEY_SEQNO”,
SORLCUR_PRIORITY_NO “SAVCCUR_PRIORITY_NO”,
SORLCUR_CACT_CODE “SAVCCUR_CACT_CODE”,
SORLCUR_USER_ID “SAVCCUR_USER_ID”,
SORLCUR_DATA_ORIGIN “SAVCCUR_DATA_ORIGIN”,
SORLCUR_ACTIVITY_DATE “SAVCCUR_ACTIVITY_DATE”,
SORLCUR_LEVL_CODE “SAVCCUR_LEVL_CODE”,
SORLCUR_COLL_CODE “SAVCCUR_COLL_CODE”,
SORLCUR_DEGC_CODE “SAVCCUR_DEGC_CODE”,
SORLCUR_TERM_CODE_CTLG “SAVCCUR_TERM_CODE_CTLG”,
SORLCUR_TERM_CODE_END “SAVCCUR_TERM_CODE_END”,
SORLCUR_CAMP_CODE “SAVCCUR_CAMP_CODE”,
SORLCUR_PROGRAM “SAVCCUR_PROGRAM”,
SORLCUR_START_DATE “SAVCCUR_START_DATE”,
SORLCUR_END_DATE “SAVCCUR_END_DATE”,
SORLCUR_CURR_RULE “SAVCCUR_CURR_RULE”,
SORLCUR_ROLLED_SEQNO “SAVCCUR_ROLLED_SEQNO”,
SORLCUR.ROWID “SAVCCUR_ROWID”,
SOKCCUR.f_order(sorlcur_pidm, sb_curriculum_str.f_admissions, sorlcur_seqno, saradap_term_code_entry, saradap_appl_no) “SAVCCUR_ORDER”,
substr(sb_learnercurricstatus.f_is_active(SORLCUR_CACT_CODE), 1, 1) “SAVCCUR_ACTIVE_IND”,
SARADAP1.ROWID “SAVCCUR_ADAP_ROWID”,
SARADAP_APPL_DATE “SAVCCUR_APPL_DATE”,
SARADAP_APST_CODE “SAVCCUR_APST_CODE”,
SARADAP_APST_DATE “SAVCCUR_APST_DATE”,
SARADAP_MAINT_IND “SAVCCUR_MAINT_IND”,
SARADAP_ADMT_CODE “SAVCCUR_ADMT_CODE”,
SARADAP_STYP_CODE “SAVCCUR_STYP_CODE”,
SARADAP_SITE_CODE “SAVCCUR_SITE_CODE”,
SARADAP_RESD_CODE “SAVCCUR_RESD_CODE”,
SARADAP_FULL_PART_IND “SAVCCUR_FULL_PART_IND”,
SARADAP_SESS_CODE “SAVCCUR_SESS_CODE”,
SARADAP_WRSN_CODE “SAVCCUR_WRSN_CODE”,
SARADAP_INTV_CODE “SAVCCUR_INTV_CODE”,
SARADAP_FEE_IND “SAVCCUR_FEE_IND”,
SARADAP_FEE_DATE “SAVCCUR_FEE_DATE”,
SARADAP_RATE_CODE “SAVCCUR_RATE_CODE”,
SARADAP_EGOL_CODE “SAVCCUR_EGOL_CODE”,
SARADAP_EDLV_CODE “SAVCCUR_EDLV_CODE”,
SARADAP_SBGI_CODE “SAVCCUR_SBGI_CODE”,
SARADAP_RECR_CODE “SAVCCUR_RECR_CODE”,
SARADAP_RTYP_CODE “SAVCCUR_RTYP_CODE”,
SARADAP_WEB_ACCT_MISC_IND “SAVCCUR_WEB_ACCT_MISC_IND”,
SARADAP_WEB_CASHIER_USER “SAVCCUR_WEB_CASHIER_USER”,
SARADAP_WEB_TRANS_NO “SAVCCUR_WEB_TRANS_NO”,
SARADAP_WEB_AMOUNT “SAVCCUR_WEB_AMOUNT”,
SARADAP_WEB_RECEIPT_NUMBER “SAVCCUR_WEB_RECEIPT_NUMBER”,
SARADAP_WAIV_CODE “SAVCCUR_WAIV_CODE”,
SARADAP_APPL_PREFERENCE “SAVCCUR_APPL_PREFERENCE”
from “PUBLIC”.saradap SARADAP1,
“PUBLIC”.sorlcur
where saradap_term_code_entry = sorlcur_term_code
and saradap_appl_no = sorlcur_key_seqno
and saradap_pidm = sorlcur_pidm
and sorlcur_lmod_code = sb_curriculum_str.f_admissions
and sorlcur_pidm > 0) SAVCCUR,
(SELECT SARADAP_PIDM “SAVAPDC_PIDM”,
SARADAP_TERM_CODE_ENTRY “SAVAPDC_TERM_CODE”,
SARADAP_APPL_NO “SAVAPDC_APPL_NO”,
SARADAP_APST_CODE “SAVAPDC_APST_CODE”,
STVAPST_DESC “SAVAPDC_APST_DESC”,
A1.SARAPPD_APDC_CODE “SAVAPDC_APDC_CODE”,
STVAPDC_DESC “SAVAPDC_APDC_DESC”,
STVAPDC_SIGNF_IND “SAVAPDC_SIGNF_IND”,
STVAPDC_INST_ACC_IND “SAVAPDC_INST_ACC_IND”,
STVAPDC_STDN_ACC_IND “SAVAPDC_STDN_ACC_IND”,
STVAPDC_APPL_INACT “SAVAPDC_APPL_INACT”
FROM “PUBLIC”.STVAPDC STVAPDC1,
“PUBLIC”.SARAPPD A1,
“PUBLIC”.STVAPST STVAPST1,
“PUBLIC”.SARADAP SARADAP2
WHERE STVAPST_CODE (+) = SARADAP_APST_CODE
AND A1.SARAPPD_PIDM (+) = SARADAP_PIDM
AND A1.SARAPPD_TERM_CODE_ENTRY (+) = SARADAP_TERM_CODE_ENTRY
AND A1.SARAPPD_APPL_NO (+) = SARADAP_APPL_NO
AND (to_char(A1.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || A1.SARAPPD_SEQ_NO = (SELECT MAX(to_char(B1.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || B1.SARAPPD_SEQ_NO)
FROM “PUBLIC”.SARAPPD B1
WHERE B1.SARAPPD_PIDM = A1.SARAPPD_PIDM
AND B1.SARAPPD_TERM_CODE_ENTRY = A1.SARAPPD_TERM_CODE_ENTRY
AND B1.SARAPPD_APPL_NO = A1.SARAPPD_APPL_NO)
OR A1.SARAPPD_APDC_DATE IS NULL)
AND STVAPDC_CODE = A1.SARAPPD_APDC_CODE
UNION ALL
SELECT SARADAP_PIDM “SAVAPDC_PIDM”,
SARADAP_TERM_CODE_ENTRY “SAVAPDC_TERM_CODE”,
SARADAP_APPL_NO “SAVAPDC_APPL_NO”,
SARADAP_APST_CODE “SAVAPDC_APST_CODE”,
STVAPST_DESC “SAVAPDC_APST_DESC”,
A2.SARAPPD_APDC_CODE “SAVAPDC_APDC_CODE”,
NULL “SAVAPDC_APDC_DESC”,
NULL “SAVAPDC_SIGNF_IND”,
NULL “SAVAPDC_INST_ACC_IND”,
NULL “SAVAPDC_STDN_ACC_IND”,
NULL “SAVAPDC_APPL_INACT”
FROM “PUBLIC”.SARAPPD A2,
“PUBLIC”.STVAPST STVAPST2,
“PUBLIC”.SARADAP SARADAP3
WHERE STVAPST_CODE (+) = SARADAP_APST_CODE
AND A2.SARAPPD_PIDM (+) = SARADAP_PIDM
AND A2.SARAPPD_TERM_CODE_ENTRY (+) = SARADAP_TERM_CODE_ENTRY
AND A2.SARAPPD_APPL_NO (+) = SARADAP_APPL_NO
AND (to_char(A2.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || A2.SARAPPD_SEQ_NO = (SELECT MAX(to_char(B2.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || B2.SARAPPD_SEQ_NO)
FROM “PUBLIC”.SARAPPD B2
WHERE B2.SARAPPD_PIDM = A2.SARAPPD_PIDM
AND B2.SARAPPD_TERM_CODE_ENTRY = A2.SARAPPD_TERM_CODE_ENTRY
AND B2.SARAPPD_APPL_NO = A2.SARAPPD_APPL_NO)
OR A2.SARAPPD_APDC_DATE IS NULL)
AND (NOT EXISTS (SELECT ‘X’
FROM “PUBLIC”.STVAPDC STVAPDC2
WHERE STVAPDC_CODE = A2.SARAPPD_APDC_CODE))) SAVAPDC
where (SPRIDEN_PIDM IN (select SRBRECR_PIDM
from SRBRECR)
OR SPRIDEN_PIDM IN (select SARADAP_PIDM
from SARADAP SARADAP21))
and SAVCCUR_PIDM = SPRIDEN_PIDM
and SAVAPDC_PIDM = SPRIDEN_PIDM
and SAVAPDC_APPL_NO = SAVCCUR_KEY_SEQNO
and SAVAPDC_TERM_CODE = SAVCCUR_TERM_CODE
and SAVCCUR_TERM_CODE = :1
and SAVCCUR_ORDER = 1
AND SAVCCUR_LEVL_CODE = :2
and NVL(SAVAPDC_STDN_ACC_IND, ‘N’) = ‘Y’