Dynamic subvar with a static subvar inside??

[Jeff Joseph]

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
------------------------------

[Nicole Boeger]

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
------------------------------

[JasonGilbert]

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.

[Ray Dooley]

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.

[Byron Ordonez]

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').