Automic Community

Expand all | Collapse all

Dynamic subvar with a static subvar inside??

Jump to Best Answer
  • 1.  Dynamic subvar with a static subvar inside??

    Posted 03-03-2020 06:49 PM

    I'm trying to find it if it would be possible to create a dynamic subvar with the equivalent of a static subvar inside of it....

    specifically  something like this...


    select to_char( to_date(#TESTmid,'DD-MON-YYYY'),'MM' ) from dual


    where I have a date in the current #TEST_mid   subvar  (such as  '01-NOV-2029')

    I have tried every variant one might think of syntactically
    {#TESTmid}   and most anything seemingly possible with quote, double quotes, backslashes, etc.

    I mainly get a variety of SQL errors.


    Am I to assume that a static subvar is not possible. If there is another way around this, I'd take it.



    ​​​

    ------------------------------
    Jeff Joseph
    ------------------------------


  • 2.  RE: Dynamic subvar with a static subvar inside??

    Posted 03-04-2020 01:53 AM
    Dear Jeff,

    do you mean you want to use a variable within a SQL VARA?

    For example you want to use a vara of type SQL_SEC, you can use a statement like this:

    select col from table where col2 = ?​


    The question mark is replaced by the entry in the "bind parameters" section in the SQL VARA. There you just enter your variable, like &myvariable#.
    Try this first - I am not sure if the question mark also works in the select part of the query instead of the where clause.

    By the way, it's a best practice to use variables with a #-sign at the end of the variable name. This way you prevent errors when using a second variable that has the same name, just longer, like this: &vara1 and &vara1_second.




    ------------------------------
    Regards, Nicole
    ------------------------------



  • 3.  RE: Dynamic subvar with a static subvar inside??

    Posted 03-04-2020 09:13 AM
    Edited by Jason Gilbert 03-04-2020 09:20 AM

    You have not specified what application you are asking about.  Assuming you are referring to Application Manager, yes, you can nest subvars.  You need curly brackets:

     

    select to_char( to_date({#TESTmid},'DD-MON-YYYY'),'MM' ) from dual

     

    You cannot test a subvar in the editor though, so you'll need to verify that it is working a different way.

     

    sorry, I didn't see your entire question before replying. and I don't see a way of removing this reply.  Whenever I'm working with to_date, it seems like I have to experiment to avoid some damn error.




  • 4.  RE: Dynamic subvar with a static subvar inside??

    Posted 03-04-2020 10:52 AM
    Hi Jeff,
    The syntax using {} is the correct answer

    select to_char( to_date({#TESTmid},'DD-MON-YYYY'),'MM' ) from dual

    However, this will not resolve correctly using the Subvars preview option because preview doesn't resolve embedded subvars.  Test by using your synamic subvar as the default for a prompt on a Job and it should resolve when you Request the job.


  • 5.  RE: Dynamic subvar with a static subvar inside??
    Best Answer

    Posted 03-04-2020 10:57 AM
    Hey Jeff,

    I got it to work with the following SQL:
    select to_char( to_date('{#TESTmid}','DD-MON-YYYY'),'MM' ) from dual​


    I think it treats it as a straight character replacement, so if you don't have the quotes, you're trying to run: to_date(01-NOV-2029, 'DD-MON-YYY').