Automic Community

Expand all | Collapse all

Applications Manager V9 - Using a Subvar value within another Subvar in the SQL statement

  • 1.  Applications Manager V9 - Using a Subvar value within another Subvar in the SQL statement

    Posted 03-29-2017 06:44 PM
    Hi, I am wondering if it is possible if I can use a defined Subvar within another Subvar while using it within the SQL Statement.
    I am currently using Ellucian's Banner software and trying to set a condition to skip if I set a value in a Banner table (RORALGS). I have to differentiate between two academic years since we run two at a time, so I am defining the aid year in the first Sub Var which equals 1617 and is named {#Aid_year_A}

    My 2nd Subvar then has a SQL statement which I want to use the {#Aid_year_A} value I defined. Example: 

    SELECT COMMENT_1 
    FROM RORALGS
    WHERE RORALGS_AIDY_CODE = {#Aid_year_A}

    I am wondering if there is a parameter I can place within the {#Aid_year_A} subvar to pass through as 1617 within the SQL statement.

    I can define the aid year within the 2nd subvar using the same SQL I used within the first to get the 1617 value, but hoping I didn't have to for simplification purposes. 

    Any help would be greatly appreciated!





  • 2.  Applications Manager V9 - Using a Subvar value within another Subvar in the SQL statement

    Posted 04-03-2017 10:32 AM
    Ian,

         Yes, you can use a subar within a subvar as you are indicating.  Consider this example:

    Subvar - #hr_pyrl_process_nbpencm_rdate:

    select to_char(ptrcaln_end_date + 1)
       from ptrcaln
      where ptrcaln_year = {#hr_pyrl_process_payroll_year}
        and ptrcaln_pict_code = '{#hr_pyrl_process_payroll_id}'
        and ptrcaln_payno = {#hr_pyrl_process_payroll_num}

    The 3 subvars used within this subvar are set by BEFORE conditions that use the SET SUBVAR action to set their
    values based on the parameter values provided when the Process Flow is ran.

    If you have any questions, please let me know.

    Ed.