Using SQL & Perl together - Which should be used for common functions?

Question

I didn't find any dupes of this question, but if there is one or more, I'm sorry - please comment with link.

The question is basic, as probably is the answer. If I'm using Perl to execute & act upon a database, which avenue (Perl vs. SQL) should I place the burden on when shared functions are involved?

Functions like - LEN, IF/ELSE, CONCAT and many more as well as arithmetic functions, for example are common to both systems.

This SQL statement is loaded with case blocks and other operations that can be replicated with Perl. So, if the same logic could be implemented in Perl, is it worth re-writing? What conditions influence the decision to take the burden of one system and place it on the 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 AND p.major1 = 'NS')                 THEN    (1165)
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    (35808)
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    (9179)
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    (70897)
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    (52125)
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    (24702)
                            WHEN    (p.minor1 = 'NURS')                                     THEN    (51569)
                            WHEN    (p.major1 = 'LEG')                                      THEN    (13324)
                            WHEN    (p.major1 = 'CC')                                       THEN    (73837)
                            WHEN    (p.major1 = 'CCRE')                                     THEN    (1133)
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    (9238)
                            ELSE    (p.adv_id)
                    END     new_adv_id,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('Deborah')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('Karen')
                            WHEN    (p.major1 = 'LEG')                                      THEN    ('Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('Naomi')
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    ('Staff')
                            ELSE    (adv_id.fullname)
                    END     new_adv_name,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('NS majors not assigned to Veronica go to Debbie')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('NS majors stay with Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('DART majors stay with Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('RT-RESP minors go to Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('CDSC-CDSD majors go to Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('CA-CB majors go to Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('NURS minors go to Karen')
                            WHEN    (p.major1 = 'LEG')                                      THEN    ('LEG majors go to Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('CC majors go to Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('CCRE majors go to Naomi')
                            WHEN    (p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                            THEN    ('Current advisor is inactive')
                            WHEN    (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                            THEN    ('Total credits for this student did not meet the advisor reqs for this major')
                            WHEN    (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                            THEN    ('This student did not attend '||si.prev_sess||si.prev_yr)
                            WHEN    ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE (stat <> 'A' OR max_stu <= 0)))
                            THEN    ('Current advisor is not advising students with this major')
                            WHEN    ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0))
                            THEN    ('Current advisor is not advising students with this major')
                            ELSE    ('Student will stay with current advisor')
                    END     change_comm
FROM                stu_acad_rec s,
                    prog_enr_rec p,
                    OUTER stu_stat_rec st,
                    id_rec stu_id,
                    id_rec adv_id,
                    sess_info si
WHERE               s.id = p.id
                    AND s.id = st.id
                    AND s.id = stu_id.id
                    AND p.adv_id = adv_id.id
                    AND s.yr = si.curr_yr
                    AND s.sess = si.curr_sess
                    AND s.reg_hrs > 0
                    AND s.reg_stat IN ('C','R')
                    AND s.prog = 'UNDG'
                    AND p.prog = 'UNDG'
                    AND st.prog = 'UNDG'
                    AND s.id NOT IN (3,287,9238,59999) {System test use IDs}
INTO TEMP           stu_list
WITH NO LOG;

Answer 1

i would look at this from a performance perspective, and a re-use perspective.

If you try it on both sides, you may find one to be much faster than the other - that would be a good indicator for which to prefer.

If you will re-use some query in more than one place, then you would want to incorporate much of the business logic in the query as possible, so you do not need to replicate that in the GUI.

(and i have to say, although not strictly part of your question, that much of that case logic looks like you could do a good model in the schema and replace the case with a normal join to some associative tables)

Answer 2

Simple functions like LEN or SUM or COUNT are usually best done on SQL.

Those monster list of cases, on the other hand, I would probably keep in the application (and use a dictionary rather than loads of switches)

EDIT - or join to another table to eliminate many of those cases. good answer Randy. when you have that many cases it gets to the point that its data rather than logic, and the query shouldnt be holding data itself.