i didn't find dupes of question, if there 1 or more, i'm sorry - please comment link.
the question basic, answer. if i'm using perl execute & act upon database, avenue (perl vs. sql) should place burden on when shared functions involved?
functions - len, if/else, concat , many more arithmetic functions, example common both systems.
this sql statement loaded case blocks , other operations can replicated perl. so, if same logic implemented in perl, worth re-writing? conditions influence decision take burden of 1 system , place on other?
select distinct s.id stu_id, stu_id.fullname stu_name, p.major1 major, p.minor1 minor, s.reg_hrs, nvl(st.cum_earn_hrs,0) ttl_hrs, p.adv_id curr_adv_id, adv_id.fullname curr_adv_name, case when (p.adv_id <> 35808 , p.major1 = 'ns') (1165) when (p.adv_id = 35808 , p.major1 = 'ns') (35808) when (p.adv_id = 9179 , p.major1 = 'dart') (9179) when (p.minor1 in ('rt','resp') , st.cum_earn_hrs >= 24) (70897) when (p.major1 in ('cdsc','cdsd')) (52125) when (p.major1 in ('ca','cb')) (24702) when (p.minor1 = 'nurs') (51569) when (p.major1 = 'leg') (13324) when (p.major1 = 'cc') (73837) when (p.major1 = 'ccre') (1133) when ((p.adv_id in (select distinct id fac_rec stat = 'i')) or (st.cum_earn_hrs < 24 , (p.adv_id||p.major1) in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0 , min_hrs >= 24)) or (s.id not in (select distinct stu.id stu_acad_rec stu, sess_info si stu.yr = si.prev_yr , stu.sess = si.prev_sess , stu.reg_hrs > 0 , stu.reg_stat in ('c','r') , stu.prog = 'undg')) or ((p.adv_id||p.major1) in (select distinct (id||major) adv_detail stat <> 'a' or max_stu <= 0)) or ((p.adv_id||p.major1) not in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0))) (9238) else (p.adv_id) end new_adv_id, case when (p.adv_id <> 35808 , p.major1 = 'ns') ('deborah') when (p.adv_id = 35808 , p.major1 = 'ns') ('veronica') when (p.adv_id = 9179 , p.major1 = 'dart') ('stella') when (p.minor1 in ('rt','resp') , st.cum_earn_hrs >= 24) ('lisa') when (p.major1 in ('cdsc','cdsd')) ('joanne') when (p.major1 in ('ca','cb')) ('barbara') when (p.minor1 = 'nurs') ('karen') when (p.major1 = 'leg') ('nancy') when (p.major1 = 'cc') ('alberta') when (p.major1 = 'ccre') ('naomi') when ((p.adv_id in (select distinct id fac_rec stat = 'i')) or (st.cum_earn_hrs < 24 , (p.adv_id||p.major1) in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0 , min_hrs >= 24)) or (s.id not in (select distinct stu.id stu_acad_rec stu, sess_info si stu.yr = si.prev_yr , stu.sess = si.prev_sess , stu.reg_hrs > 0 , stu.reg_stat in ('c','r') , stu.prog = 'undg')) or ((p.adv_id||p.major1) in (select distinct (id||major) adv_detail stat <> 'a' or max_stu <= 0)) or ((p.adv_id||p.major1) not in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0))) ('staff') else (adv_id.fullname) end new_adv_name, case when (p.adv_id <> 35808 , p.major1 = 'ns') ('ns majors not assigned veronica go debbie') when (p.adv_id = 35808 , p.major1 = 'ns') ('ns majors stay veronica') when (p.adv_id = 9179 , p.major1 = 'dart') ('dart majors stay stella') when (p.minor1 in ('rt','resp') , st.cum_earn_hrs >= 24) ('rt-resp minors go lisa') when (p.major1 in ('cdsc','cdsd')) ('cdsc-cdsd majors go joanne') when (p.major1 in ('ca','cb')) ('ca-cb majors go barbara') when (p.minor1 = 'nurs') ('nurs minors go karen') when (p.major1 = 'leg') ('leg majors go nancy') when (p.major1 = 'cc') ('cc majors go alberta') when (p.major1 = 'ccre') ('ccre majors go naomi') when (p.adv_id in (select distinct id fac_rec stat = 'i')) ('current advisor inactive') when (st.cum_earn_hrs < 24 , (p.adv_id||p.major1) in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0 , min_hrs >= 24)) ('total credits student did not meet advisor reqs major') when (s.id not in (select distinct stu.id stu_acad_rec stu, sess_info si stu.yr = si.prev_yr , stu.sess = si.prev_sess , stu.reg_hrs > 0 , stu.reg_stat in ('c','r') , stu.prog = 'undg')) ('this student did not attend '||si.prev_sess||si.prev_yr) when ((p.adv_id||p.major1) in (select distinct (id||major) adv_detail (stat <> 'a' or max_stu <= 0))) ('current advisor not advising students major') when ((p.adv_id||p.major1) not in (select distinct (id||major) adv_detail stat = 'a' , max_stu > 0)) ('current advisor not advising students major') else ('student stay current advisor') end change_comm stu_acad_rec s, prog_enr_rec p, outer stu_stat_rec st, id_rec stu_id, id_rec adv_id, sess_info si s.id = p.id , s.id = st.id , s.id = stu_id.id , p.adv_id = adv_id.id , s.yr = si.curr_yr , s.sess = si.curr_sess , s.reg_hrs > 0 , s.reg_stat in ('c','r') , s.prog = 'undg' , p.prog = 'undg' , st.prog = 'undg' , s.id not in (3,287,9238,59999) {system test use ids} temp stu_list no log;
i @ performance perspective, , re-use perspective.
if try on both sides, may find 1 faster other - indicator prefer.
if re-use query in more 1 place, want incorporate of business logic in query possible, not need replicate in gui.
(and have say, although not strictly part of question, of case logic looks model in schema , replace case normal join associative tables)
Comments
Post a Comment