# Variable trouble **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [October 6, 2009, 11:40am UTC](https://forums.toadworld.com/t/variable-trouble/6054 "2009-10-06T11:40:35Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Conrad](https://avatars.discourse-cdn.com/v4/letter/c/ebca7d/32.png) [@Conrad](https://forums.toadworld.com/u/Conrad)\ **Post date:** [October 6, 2009, 11:40am UTC](https://forums.toadworld.com/t/variable-trouble/6054/1 "2009-10-06T11:40:35Z")
DROP TABLE CPSS\_ID; CREATE TABLE CPSS\_ID AS SELECT DISTINCT GROUP\_ID FROM trnsl\_gl\_co\_chk\_stock\_id\_cd AS UNIT,primy\_grp\_id\_cd origtg\_primy\_grp\_id\_cd AS ORIG\_GRP,grp\_origtg\_src\_cd grp\_sell\_ovr\_dT AS GRP\_SELL\_OVR\_DT,grp\_name trnsl\_cstmr\_typ\_cd AS CUST\_TYP, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘02’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS DENTAL\_MBRS, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘10’,WHOLE\_MBR\_CNT, **0** )), **0** ) AS DRUG\_MBRS, NVL(SUM(pd\_amt), **0** ) AS CLAIMS\_PAID FROM dwa\_vw.ices\_denorm\_vw @RRA\_TRG\_PRD AND TRNSL\_GL\_CO\_CHK\_STOCK\_ID\_CD IN (‘ID01’,‘ID99’) dwa\_proc\_earnd\_mo\_key, trnsl\_gl\_co\_chk\_stock\_id\_cd, primy\_grp\_id\_cd, origtg\_primy\_grp\_id\_cd, grp\_origtg\_src\_cd, grp\_sell\_ovr\_dT, grp\_name) WHERE ORIG\_GRP\_SRC IN (‘TRG’); Hello, I have a query that I use to run in SQL Nav. There were define variables in the query that do not work in TOAD for Data Analysts. I now need to convert these define statements in the SQL so that TOAD will prompt for new inputs. I have a prompt that asks for the earned mnth, such as ‘200908’ or ‘200909’. I keep getting the same error: ORA-01036: illegal variable name/number I can change the type of Bind Variable, but nothing seems to work. I need help…here is some of the SQl I am using: (SELECT dwa\_proc\_earnd\_mo\_key AS EARNED\_MONTH, AS GROUP\_ID,AS ORIG\_GRP\_SRC, AS GROUP\_NAME, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘01’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS MEDICAL\_MBRS,NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘06’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS CAP\_DENTAL\_MBRS,NVL(SUM(incm\_amt), **0** ) AS PREMIUM, WHERE dwa\_proc\_earnd\_mo\_key = :EARNED\_MNTHGROUP BY trnsl\_cstmr\_typ\_cd, ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 6, 2009, 12:30pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/2 "2009-10-06T12:30:47Z")
In the statement below you have a Create Table from a select that uses a dblink and a bind variable. I can not get a DDL statement with dblink AND bind variable to work in SQL Navigator, Toad for Oracle or Toad for Data Analysts. Each give different ORA errors. How are you executing the statement below in SQL Nav? Debbie ---
**Author:** ![Conrad](https://avatars.discourse-cdn.com/v4/letter/c/ebca7d/32.png) [@Conrad](https://forums.toadworld.com/u/Conrad)\ **Post date:** [October 6, 2009, 1:08pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/3 "2009-10-06T13:08:17Z")
DEFINE &&EARNED = ‘200908’ DEFINE &&DATABASE = ‘@RRA\_TRG\_PRD’ –\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* – STEP ONE - – CREATE A TABLE TO ISOLATE THE CPSS – NEW AND SELLOVER GROUPS –\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* DROP TABLE CPSS\_ID; CREATE TABLE CPSS\_ID AS SELECT DISTINCT GROUP\_ID FROM trnsl\_gl\_co\_chk\_stock\_id\_cd AS UNIT,primy\_grp\_id\_cd origtg\_primy\_grp\_id\_cd AS ORIG\_GRP,grp\_origtg\_src\_cd grp\_sell\_ovr\_dT AS GRP\_SELL\_OVR\_DT,grp\_name trnsl\_cstmr\_typ\_cd AS CUST\_TYP, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘02’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS DENTAL\_MBRS, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘10’,WHOLE\_MBR\_CNT, **0** )), **0** ) AS DRUG\_MBRS, NVL(SUM(pd\_amt), **0** ) AS CLAIMS\_PAID FROM dwa\_vw.ices\_denorm\_vw &&DATABASE AND TRNSL\_GL\_CO\_CHK\_STOCK\_ID\_CD IN (‘ID01’,‘ID99’) dwa\_proc\_earnd\_mo\_key, trnsl\_gl\_co\_chk\_stock\_id\_cd, primy\_grp\_id\_cd, origtg\_primy\_grp\_id\_cd, grp\_origtg\_src\_cd, grp\_sell\_ovr\_dT, grp\_name) WHERE ORIG\_GRP\_SRC IN (‘TRG’); Hi Debbie, In SQL Nav I would run the query with my define variables in the beginning of the query and then referenced in the body of the query. I do not have access to create tables off the database I am referencing so I use the DB link from another database where I can. I could revise the query to not create tables, but my real problem is TOAD not accepting ‘200908’ as a variable. I hope this helps. (SELECT dwa\_proc\_earnd\_mo\_key AS EARNED\_MONTH, AS GROUP\_ID,AS ORIG\_GRP\_SRC, AS GROUP\_NAME, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘01’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS MEDICAL\_MBRS,NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘06’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS CAP\_DENTAL\_MBRS,NVL(SUM(incm\_amt), **0** ) AS PREMIUM, WHERE dwa\_proc\_earnd\_mo\_key = &&EARNEDGROUP BY trnsl\_cstmr\_typ\_cd, ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 6, 2009, 1:19pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/4 "2009-10-06T13:19:51Z")
This is different. The & is a SQL\*Plus replacement symbol. I am currently working on a CR to treat the ‘&’ symbol as a bind var. In the case below, treating ‘&’ as a bind will not work. I may have to rethink the enhancement I am working on. Currently, in TDA 2.5 you would need to do an export and import to accomplish this. Debbie ---
**Author:** ![Conrad](https://avatars.discourse-cdn.com/v4/letter/c/ebca7d/32.png) [@Conrad](https://forums.toadworld.com/u/Conrad)\ **Post date:** [October 6, 2009, 2:17pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/5 "2009-10-06T14:17:47Z")
Debbie, So can you assist me with some pointers of how I would go about either declaring the value of the variable earned\_mnth in the beginning of the query or creating prompts for the variables in the query that would handle the input of ‘200908’? I have tried some of the other suggestions in other discussions, but nothing seems to work. It seems really simple to prompt for a variable, input the value for that variable, and then perhaps reference that variable in the query more than once. I’m new to TOAD and I am use to defining the variables in the start of the query so as to reduce the amount of editing done in the body of the query. Thank you, Conrad ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 6, 2009, 2:46pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/6 "2009-10-06T14:46:12Z")
Here is my suggestion. SQL\*Plus compatibility is supported by using the Quest Script Runner. This is set up by default in your options for running an external program. See first screenshot. Go back to using the '&' or '&&' and then when ready to run click on the external execute button. See second screenshot. This will load your script into the Quest Script Runner and handle the variable replacement. Debbie ![QuestScriptRunner.png](https://global.discourse-cdn.com/quest/original/2X/3/3ceef88fb8d0975e6d2b619b96f86f0c4b049e72.png) ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 6, 2009, 2:46pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/7 "2009-10-06T14:46:12Z")
Here is my suggestion. SQL\*Plus compatibility is supported by using the Quest Script Runner. This is set up by default in your options for running an external program. See first screenshot. Go back to using the '&' or '&&' and then when ready to run click on the external execute button. See second screenshot. This will load your script into the Quest Script Runner and handle the variable replacement. Debbie ![QuestScriptRunner1.png](https://global.discourse-cdn.com/quest/original/2X/2/258838ed82b1d8375b8e147e0d56b832acb34be9.png) ---
**Author:** ![Henrik\_Johnson](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/henrik_johnson/32/14_2.png) [@Henrik\_Johnson](https://forums.toadworld.com/u/Henrik_Johnson)\ **Post date:** [October 6, 2009, 3:00pm UTC](https://forums.toadworld.com/t/variable-trouble/6054/8 "2009-10-06T15:00:18Z")
–TOAD: SET EARNED = ‘200908’ –TOAD: SET DATABASE = ‘@RRA\_TRG\_PRD’ CREATE TABLE CPSS\_ID AS SELECT DISTINCT GROUP\_ID FROM trnsl\_gl\_co\_chk\_stock\_id\_cd AS UNIT,primy\_grp\_id\_cd origtg\_primy\_grp\_id\_cd AS ORIG\_GRP,grp\_origtg\_src\_cd grp\_sell\_ovr\_dT AS GRP\_SELL\_OVR\_DT,grp\_name trnsl\_cstmr\_typ\_cd AS CUST\_TYP, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘02’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS DENTAL\_MBRS, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘10’,WHOLE\_MBR\_CNT, **0** )), **0** ) AS DRUG\_MBRS, NVL(SUM(pd\_amt), **0** ) AS CLAIMS\_PAID FROM dwa\_vw.ices\_denorm\_vw **{{Quote(:DATABASE)}}** AND TRNSL\_GL\_CO\_CHK\_STOCK\_ID\_CD IN (‘ID01’,‘ID99’) dwa\_proc\_earnd\_mo\_key, trnsl\_gl\_co\_chk\_stock\_id\_cd, primy\_grp\_id\_cd, origtg\_primy\_grp\_id\_cd, grp\_origtg\_src\_cd, grp\_sell\_ovr\_dT, grp\_name) WHERE ORIG\_GRP\_SRC IN (‘TRG’); Here is how you would write this in TDA. I’m doing this right into the editor so I might make a mistake, but hopefully you will get the gist. \*\*\*\*DROP TABLE CPSS\_ID;(SELECT dwa\_proc\_earnd\_mo\_key AS EARNED\_MONTH, AS GROUP\_ID,AS ORIG\_GRP\_SRC, AS GROUP\_NAME, NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘01’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS MEDICAL\_MBRS,NVL(SUM(DECODE (EXPRC\_CAT\_CD,‘06’, WHOLE\_MBR\_CNT, **0** )), **0** ) AS CAP\_DENTAL\_MBRS,NVL(SUM(incm\_amt), **0** ) AS PREMIUM, WHERE dwa\_proc\_earnd\_mo\_key = **:EARNED** GROUP BY trnsl\_cstmr\_typ\_cd, ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [October 8, 2009, 10:27am UTC](https://forums.toadworld.com/t/variable-trouble/6054/9 "2009-10-08T10:27:20Z")
P.S. I just added support for the & and && today. It will be in our next 2.6 Beta. Debbie