oracle - SubQuery added to existing Query in PL/SQL -
i sure simple , missing on formatting want add following query:
select s.stud_id, jb.region, jt.district pa_student s, (select su.stud_id, rf.user_desc region pa_stud_user su, pa_usrrf_stud rf su.col_num = rf.col_num , su.user_value = rf.user_id , su.col_num = 300) jb, (select su.stud_id, rf.user_desc district pa_stud_user su, pa_usrrf_stud rf su.col_num = rf.col_num , su.user_value = rf.user_id , su.col_num = 400) jt s.stud_id = jb.stud_id (+) , s.stud_id = jt.stud_id (+) to existing query (both of these work fine themselves)
select sqc.cpnt_typ_id cpnttypeid , sqc.cpnt_id cpntid , sqc.rev_dte revdate, sqc.compl_dte compldate, sqc.cmpl_stat_id cmplstatid, sqc.req_dte reqdate, cpt.cpnt_title cpntttile, cpt.rtyp_id , cpt.dmn_id , cpt.del_mth_id , cpt.cpnt_src_id , cpt.notactive , cpt.safety_rel , cpt.create_dte, cpt.rev_num cpntrevnum, cpt.reviser , cpt.apprvr , cpt.apprvd , cpt.apprvl_dte , cpt.min_enrl , cpt.max_enrl , cpt.cpnt_len , cpt.prep_len , cpt.les_plan , cpt.contact , cpt.credit_hrs, cpt.cpe_hrs , cpt.contact_hrs , cpt.stud_mats , cpt.inst_mats , cpt.cpnt_desc , cpt.tgt_audnc , cpt.comments , cpt.grade_opt , cpt.enrl_threshold_days , cpt.shipping_required, cpt.auto_fill_enrl , cpt.waitlist_remdr_sent, cpt.auto_competency , cpt.cpnt_classification , cpt.chgbck_method , cpt.catalog_sku , cpt.schedule_can_override_price , cpt.production_ready, cpt.cpnt_goals , cpt.self_record_lrngevt , cpt.sub_record_lrngevt , cpt.tap_def_id , cpt.approval_reqd , cpt.init_period_type_id , cpt.init_number , cpt.init_basis_type_id, cpt.retrng_period_type_id , cpt.retrng_number , cpt.retrng_basis_type_id , cpt.rating , cpt.self_enrl , cpt.user_req_enabled , cpt.user_can_waitlist , cpt.cpnt_key, cpt.register_online , cpt.esig_enabled , cs.cmpl_stat_desc cmplstatdesc, s.stud_id, s.fname, s.lname, s.mi, s.stud_id studid, s.lname lastname, s.fname firstname, s.mi middlename, s.emp_stat_id , s.emp_typ_id, s.jl_id , s.jp_id , s.target_jp_id , s.job_title , s.dmn_id , s.org_id, s.region_id, s.co_id , decode(s.notactive, 'y','n','n','y') studactive , s.addr , s.city , s.state , s.postal , s.cntry , s.super , s.coach_stud_id , s.hire_dte, s.term_dte , s.email_addr, s.resume_locn , s.comments , s.shipping_name , s.shipping_contact_name, s.shipping_addr , s.shipping_addr1 , s.shipping_city , s.shipping_state, s.shipping_postal , s.shipping_cntry , s.shipping_phon_num , s.shipping_fax_num , s.shipping_email_addr , s.stud_pswd , s.pin , s.pin_date, s.encrypted , s.has_access , s.billing_name , s.billing_contact_name , s.billing_addr , s.billing_addr1 , s.billing_city , s.billing_state , s.billing_postal, s.billing_cntry , s.billing_phon_num , s.billing_fax_num , s.billing_email_addr , s.self_registration , s.self_registration_date, s.access_to_org_fin_act , s.notify_dev_plan_item_add , s.notify_dev_plan_item_mod , s.notify_dev_plan_item_remove , s.notify_when_sub_item_complete , s.notify_when_sub_item_failure , s.locked , s.password_exp_date, s.security_question , s.security_answer , s.role_id , s.image_id , s.gender , s.past_service, s.lst_unlock_tstmp, s.manage_sub_sp, s.manage_own_sp, nvl(userpref.preferred_timezone,pkg_state.get_default_timezone) preferred_timezone, sq.qual_id qualid, sq.assgn_dte assigndate, sq.qual_id_root qualidroot, q.qual_title qualtitle, q.dmn_id qualdomain, q.qual_typ_id qualtypeid , q.notactive qualnotactive , q.create_dte qualcreatedate, q.qual_desc qualdesc, q.force_incomplete qualforceincomplete, q.basis_date qualbasisdate, q.esig_enabled qualesigenabled, pkg_student.get_stud_qual_status (sq.stud_id, sq.qual_id, sq.qual_id_root) complete pa_stud_qual_cpnt sqc, pa_cpnt cpt, pa_cmpl_stat cs, pa_student s, pa_user_preference userpref, pa_qual q, (select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root, row_number() on (partition sq.stud_id,sq.qual_id order sq.assgn_dte) rnum (select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root pa_stud_qual_relation sq, ( select sq.stud_id pa_stud_qual sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ union select sq.stud_id pa_stud_qual_relation sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ ) students sq.stud_id = students.stud_id /** , sq.qual_id in [curriculumsearch]*/ union select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root pa_stud_qual_relation sq, pa_qual_relation qr, ( select sq.stud_id pa_stud_qual sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ union select sq.stud_id pa_stud_qual_relation sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ ) students sq.stud_id = students.stud_id , qr.qual_id_child = sq.qual_id /** , qr.qual_id_parent in [curriculumsearch]*/ ) sq ) sq sqc.stud_id (+) = sq.stud_id , sqc.qual_id (+) = sq.qual_id , sqc.qual_id_root(+) = sq.qual_id_root , sqc.cpnt_typ_id = cpt.cpnt_typ_id (+) , sqc.cpnt_id = cpt.cpnt_id (+) , sqc.rev_dte = cpt.rev_dte (+) , sqc.cmpl_stat_id = cs.cmpl_stat_id (+) , s.stud_id = sq.stud_id , s.stud_id = userpref.user_id(+) , userpref.user_type(+) = 's' , sq.rnum = 1 , sq.qual_id = q.qual_id /** , [security:pa_student s]*/ i have tried many different formats confused beyond knowledge of sql... ideas? in advance.
i combined it. took great pain. please try query , luck:
select sqc.cpnt_typ_id cpnttypeid, sqc.cpnt_id cpntid, sqc.rev_dte revdate, sqc.compl_dte compldate, sqc.cmpl_stat_id cmplstatid, sqc.req_dte reqdate, cpt.cpnt_title cpntttile, cpt.rtyp_id, cpt.dmn_id, cpt.del_mth_id, cpt.cpnt_src_id, cpt.notactive, cpt.safety_rel, cpt.create_dte, cpt.rev_num cpntrevnum, cpt.reviser, cpt.apprvr, cpt.apprvd, cpt.apprvl_dte, cpt.min_enrl, cpt.max_enrl, cpt.cpnt_len, cpt.prep_len, cpt.les_plan, cpt.contact, cpt.credit_hrs, cpt.cpe_hrs, cpt.contact_hrs, cpt.stud_mats, cpt.inst_mats, cpt.cpnt_desc, cpt.tgt_audnc, cpt.comments, cpt.grade_opt, cpt.enrl_threshold_days, cpt.shipping_required, cpt.auto_fill_enrl, cpt.waitlist_remdr_sent, cpt.auto_competency, cpt.cpnt_classification, cpt.chgbck_method, cpt.catalog_sku, cpt.schedule_can_override_price, cpt.production_ready, cpt.cpnt_goals, cpt.self_record_lrngevt, cpt.sub_record_lrngevt, cpt.tap_def_id, cpt.approval_reqd, cpt.init_period_type_id, cpt.init_number, cpt.init_basis_type_id, cpt.retrng_period_type_id, cpt.retrng_number, cpt.retrng_basis_type_id, cpt.rating, cpt.self_enrl, cpt.user_req_enabled, cpt.user_can_waitlist, cpt.cpnt_key, cpt.register_online, cpt.esig_enabled, cs.cmpl_stat_desc cmplstatdesc, s.stud_id, s.fname, s.lname, s.mi, s.stud_id studid, s.lname lastname, s.fname firstname, s.mi middlename, s.emp_stat_id, s.emp_typ_id, s.jl_id, s.jp_id, s.target_jp_id, s.job_title, s.dmn_id, s.org_id, s.region_id, s.co_id, decode(s.notactive, 'y','n','n','y') studactive, s.addr, s.city, s.state, s.postal, s.cntry, s.super, s.coach_stud_id, s.hire_dte, s.term_dte, s.email_addr, s.resume_locn, s.comments, s.shipping_name, s.shipping_contact_name, s.shipping_addr, s.shipping_addr1, s.shipping_city, s.shipping_state, s.shipping_postal, s.shipping_cntry, s.shipping_phon_num, s.shipping_fax_num, s.shipping_email_addr, s.stud_pswd, s.pin, s.pin_date, s.encrypted, s.has_access, s.billing_name, s.billing_contact_name, s.billing_addr, s.billing_addr1, s.billing_city, s.billing_state, s.billing_postal, s.billing_cntry, s.billing_phon_num, s.billing_fax_num, s.billing_email_addr, s.self_registration, s.self_registration_date, s.access_to_org_fin_act, s.notify_dev_plan_item_add, s.notify_dev_plan_item_mod, s.notify_dev_plan_item_remove, s.notify_when_sub_item_complete, s.notify_when_sub_item_failure, s.locked, s.password_exp_date, s.security_question, s.security_answer, s.role_id, s.image_id, s.gender, s.past_service, s.lst_unlock_tstmp, s.manage_sub_sp, s.manage_own_sp, nvl(userpref.preferred_timezone,pkg_state.get_default_timezone) preferred_timezone, sq.qual_id qualid, sq.assgn_dte assigndate, sq.qual_id_root qualidroot, q.qual_title qualtitle, q.dmn_id qualdomain, q.qual_typ_id qualtypeid, q.notactive qualnotactive, q.create_dte qualcreatedate, q.qual_desc qualdesc, q.force_incomplete qualforceincomplete, q.basis_date qualbasisdate, q.esig_enabled qualesigenabled, pkg_student.get_stud_qual_status (sq.stud_id, sq.qual_id, sq.qual_id_root) complete, /**-- added smaller query */ jb.region, jt.district /**-- added smaller query */ pa_stud_qual_cpnt sqc, pa_cpnt cpt, pa_cmpl_stat cs, pa_student s, pa_user_preference userpref, pa_qual q, ( select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root, row_number() on (partition sq.stud_id,sq.qual_id order sq.assgn_dte) rnum ( select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root pa_stud_qual_relation sq, ( select sq.stud_id pa_stud_qual sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ union select sq.stud_id pa_stud_qual_relation sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ ) students sq.stud_id = students.stud_id /** , sq.qual_id in [curriculumsearch]*/ union select sq.stud_id, sq.qual_id, sq.assgn_dte, sq.qual_id_root pa_stud_qual_relation sq, pa_qual_relation qr, ( select sq.stud_id pa_stud_qual sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ union select sq.stud_id pa_stud_qual_relation sq 1=1 /** , sq.stud_id in [usersearch] */ /** , sq.qual_id in [curriculumsearch] */ ) students sq.stud_id = students.stud_id , qr.qual_id_child = sq.qual_id /** , qr.qual_id_parent in [curriculumsearch]*/ ) sq ) sq, /**-- added smaller query */ ( select su.stud_id, rf.user_desc region pa_stud_user su join pa_usrrf_stud rf on rf.col_num = su.col_num su.user_value = rf.user_id , su.col_num = 300 ) jb, ( select su.stud_id, rf.user_desc district pa_stud_user su join pa_usrrf_stud rf on rf.col_num = su.col_num su.user_value = rf.user_id , su.col_num = 400 ) jt /**-- added smaller query */ sqc.stud_id = sq.stud_id , sqc.qual_id = sq.qual_id , sqc.qual_id_root = sq.qual_id_root , sqc.cpnt_typ_id = cpt.cpnt_typ_id , sqc.cpnt_id = cpt.cpnt_id , sqc.rev_dte = cpt.rev_dte , sqc.cmpl_stat_id = cs.cmpl_stat_id , s.stud_id = sq.stud_id , s.stud_id = userpref.user_id , userpref.user_type = 's' , sq.rnum = 1 , sq.qual_id = q.qual_id /**-- added smaller query */ , s.stud_id = jb.stud_id , s.stud_id = jt.stud_id /**-- added smaller query */ /** , [security:pa_student s]*/
Comments
Post a Comment