I am using Automation Designer in Toad for Oracle Ver 13.1. I have a Export Dataset action that uses what I guess is an bind variable. I am hoping to use the Like clause in the SQL text to look for the string in the bind variable at the beginning, the end and within a string. So far I have been able to do it at the end of the string but am unable match in the middle or beginning of the string.
This works
select E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME
from advance.entity E
where E.LAST_NAME like '&1%'
but this does not
select E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME
from advance.entity E
where E.LAST_NAME like '%&1'
In fact I have tried everything I can think of and can't get it to work.
Would anyone have a solution or woraround?
Thanks,
Bob
I got it to work just now using bind variables instead of substitution variables and putting the % as part of the variable value.
This is the result I get:
SQL Statement which produced this data:
select E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME
from advance.entity E
where E.LAST_NAME like :1;
LAST_NAME|FIRST_NAME|MIDDLE_NAME
I'll try again with 13.1. I was using the most recent version, 14.0
Wait a minute. You set it to VAR_LAST_NAME. Try it with '%H' or example, to find SMITH.
If VAR_LAST_NAME is a variable defined in automation designer, you need to choose "Environment variable" instead of literal in the dialog. And then wherever you set a value to VAR_LAST_NAME, that needs to be %H or whatever.
then you can run both like this:
Hello John,
I was using CreateVariable followed by VariablePrompt and trying to avoid having the user enter a % in the prompt string but it looks like trying to find a way around that might not be worth the effort at this time. Still it would be nice to know why this works:
where E.LAST_NAME like '&1%'
and this does not
where E.LAST_NAME like '%&1'
Thanks for your help,
Bob
Do you still have the "Literal" box checked?
I just set one up as you described using Toad 13.1 and for me it works correctly. See below.
