Modifying v7 SQL that uses Variable Data

While upgrading (if there are any more upgrades) you'll eventually encounter a piece of SQL that filters on, say PS_PERSON_COMMUNICATION.ADMIN_FUNCTION and ACAD_CAREER. The latter is now on many child tables; VAR_DATA_XXXX.

For example:

select
  to_char(a.comment_dttm,'dd/mm/yyyy hh24:mi:ss')
, a.cmnt_category
, a.deptid
, a.cmnt_id
, a.comment_dt
, a.comments
from
  ps_person_comment a
where a.emplid = :1
and a.admin_function = :4
and a.acad_career=:2
and a.stdnt_car_nbr = :3
;

This can be resolved to something like the following, where the subselect/temp-table/on-the-fly thingies there are populated by a union between all VAR_DATA_XXXX tables that use ACAD_CAREER and STDNT_CAR_NBR

select
  to_char(a.comment_dttm,'dd/mm/yyyy hh24:mi:ss')
, a.cmnt_category
, a.deptid
, a.cmnt_id
, a.comment_dt
, a.comments

from
  ps_person_comment a
,
    (select 'ADMA' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_ADMA
    union
    select 'ADMP' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_ADMP
    union
    select 'CASN' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_CASN
    union
    select 'IPT1' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_IPT1
    union
    select 'SPRG' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_SPRG
    union
    select 'THES' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_THES) x

where a.common_id = &1
and a.admin_function = &4
and a.cmnt_id = b.emplid
and x.admin_function = a.admin_function
and x.common_id = a.common_id
and x.var_data_seq = a.var_data_seq
and x.acad_career = &2
and x.stdnt_car_nbr = &3
;