AppWorx, Dollar Universe and Sysload Community

  • 1.  Substiution Variable Help

    Posted Dec 12, 2018 12:12 PM

    I am attempting to create a substitution variable for a number incrementer.  We have this one in place already, but it only returns a single digit number.  I need it to return a number with leading zeros.

     

    For example:

    Current query returns: 1

    Need query to return:   001

     

    Here is how the query is currently written:

     

    select to_char(to_number(so_var_value)+1)
    from so_sub_vars
    where so_var_name = '#RDC_file_number'

     

     

    Thanks in advance for any assistance!



  • 2.  Re: Substiution Variable Help

    Broadcom Employee
    Posted Dec 13, 2018 05:06 PM

    You could also using LPAD. Not sure what the query returns but it seems possible to add leading zeros this way.

    example from a site I looked at:

    SQL> SELECT LPAD(1, 2, '0') FROM DUAL;

    LP
    --
    01



  • 3.  Re: Substiution Variable Help

    Posted Dec 20, 2018 01:51 PM

    TESTING CASE:

    SELECT TO_CHAR(76, 'FM000')
    FROM DUAL;

     

     

    Suggested code for you:

    SELECT TO_CHAR(TO_NUMBER(SO_VAR_VALUE) + 1, 'FM000')
    FROM SO_SUB_VARS
    WHERE SO_VAR_NAME = '#RDC_file_number';

     

     

    The "FM" portion of the formatting string tells oracle to remove extraneous spaces that can find it's way into the character string when you do these types of conversions.

     

    Simply add/remove the 0's in the TO_CHAR to account for more or less padding as desired.