Variable trouble

Toad Data Point & Toad Intelligence Central
1

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,

2

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

3

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,

4

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

5

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

6

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
QuestScriptRunner.png794×499 21.7 KB

7

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
QuestScriptRunner1.png779×626 39.2 KB

8

–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 = :EARNEDGROUP BY trnsl_cstmr_typ_cd,

9

P.S. I just added support for the & and && today. It will be in our next 2.6 Beta.

Debbie