Toad World® Forums

Question regarding non-prompted Bind Variables


#1

CREATE TABLE t_weekly_temp

AS

a.mazda_region,

a.product,

COUNT (a.acct_nbr) AS booked_nbr,

WHERE a.system_ind = ‘XXX’

AND a.note_mo = 201003 'Insert variable here

AND a.co_type_cd = ‘NEW’

Hi there,

I can’t seem to locate any articles on this forum that refer to hard-coded bind variables. Is this possible in TOAD for DA?

I’d like to set the variables in the beginning via some type of declare statement then re-use them.

Example:

SELECT a.note_mo,FROM t_XXX_daily_booked aAND a.note_week_nbr IN (‘WK1’,‘WK2’) 'Insert variable here


#2

create table DEBTEST.test2

as select * from address

where address_id = &a

and CITY = ‘&b’

If you are using Oracle, use the SQL*PLus replacement var symbal (&a). You will be prompted for the bind value when executed and this value will be used for all replacements of that name.

IE:
ReplaceVars.png


#3

Hi there,

I did get this to work but it’s not 100% valuable yet :slight_smile:

When I make the change and run it in Quest ScriptRunner instead of only having to enter the variable once it prompts for it after each SQL statement.

Anyway to tweak it to just prompt once and re-use the variables?

I attached a screenprint of ScriptRunner.


#4

It doesn’t look like it retains the values. I haven’t looked at the Script Runner in a long time. Are you not using TDA 2.6? TDA 2.6 supports the ‘&’ symbol and can be installed side by side to any other version of TDA. Would you consider upgrading?

Debbie


#5

WHERE a.system_ind = ‘XXX’

and a.ln_note_dt between &startdt and &enddt

I’ve attached screenshots. Any thoughts?

Hi there,

So I’m finally getting back to this solution and I was able to upgrade TOAD to 2.6.1.568.

I am able to add the &startdt and &enddt to my code but I am still getting errors. I need to re-use dates not characters so will this work?

FROM t_mazda_daily_booked a
untitled2.jpeg


#6

WHERE a.system_ind = ‘XXX’

and a.ln_note_dt between &startdt and &enddt

I’ve attached screenshots. Any thoughts?

Hi there,

So I’m finally getting back to this solution and I was able to upgrade TOAD to 2.6.1.568.

I am able to add the &startdt and &enddt to my code but I am still getting errors. I need to re-use dates not characters so will this work?

FROM t_mazda_daily_booked a
untitled.jpeg


#7

create table debtest.newtable ASSELECT

FROM QUEST_STAGE.ORDERS ORDERS

‘&enddate’)

You have to get there where condition date column to filter as a string. See if this works for you.

Debbie

To do this in Oracle with a Date you could do this -->

ORDERS.ORDER_DATE, ORDERS.ORDER_IDWHERE (to_char(ORDERS.ORDER_DATE) BETWEEN ‘&startdate’ AND


#8

AND (TO_CHAR (a.ln_note_dt) BETWEEN ‘&startdate’ AND ‘&enddate’)

Hi Debbie,

No luck :frowning: I tried your suggestion and the code runs but no prompts come up. The code just runs straight through appending 0 records.

I tried other ways to get the dates to converts to chars but no luck.
screenprint.jpg


#9

select hiredate from SCOTT.EMP

where to_char(hiredate, ‘MM/DD/YYYY’) between ‘&startdate’ and ‘&enddate’

I use a regular expression to find the replacement vars. If you get the prompt on this statement but not yours it is possible that my regular expression has an error in it. Can you send the full SQL statement to me as an attached text file?

Debbie
Post or private email is fine.
dpeabody@quest.com

Do you get the prompt with this statement?


#10

No luck it just runs straight through with no prompts. When I take the single quotes off, ex &startdt it says bind variables not allowed. When I do :startdt it says illegal variable name/number.

I’ve attached the sql in a Text File :wink:
qry_prompts.txt (1.13 KB)


#11

I created a t_mazda_daily_booked table and using TDA 2.6 I can successfully execute this SQL and get the bind prompt. Of course there isn’t any data in this table so I am not checking for accuracy of the bind filter.

I tested the same script in 2.5 and it behaves as you describe, no prompts.

So if you are using 2.6.1.568 and not getting the prompt then there must be some settings that are different in your envirnoment. Go to the Help about menu and generate a support bundle and email me the ToadSupportBundle.zip. Change the address to dpeabody@quest.com

Debbie
PromptForBinds.png


#12

Hi Debbie,

My apologies I was accidentally using 2.5 to try this once I switched to 2.6 it worked like a charm. Yey!!! Thanks.


#13

Good to know:)