Hi
I recently inherited a set of queries and found that some of them have substitute variables such as: ‘&&Start_Dt_in_dd_mon_yy_format’ AND ‘&&End_Dt_in_dd_mon_yy_format’
I try to run these queries in Toad and I get an error message saying: a non-numeric character was found where a numeric was expected. I can run the query in other applications and it executes normally. I was wondering if there is a work around in Toad where it will recognize the &&?
I am using Toad Data Point 3.3
This type of query works find for me. I tested in current version and TDP 3.3. The error you are getting sounds like it is coming from the database and is complaining about the format of the string. Are you using the Oracle native connector? Not ODBC or Direct Connect? Can you post a complete sample query including screenshot of what you are entering in the variable window?
Actually the query doesn’t get to the input stage. it errors out as it’s executes. here is the query i am trying to run:
SET VERFIY OFF;
SELECT
ORG_ACCT_NM AS “customer”
, atl.acct_typ_desc AS “customer_type”
, ORG_ST_CD AS “state”
, INITCAP(ORG_CTY_ADDR) AS “city”
, INITCAP(TRIM(uoc.frst_nm)) || ’ ’ || INITCAP(TRIM(uoc.lst_nm)) AS “user_name”
, uoc.email
, CASE WHEN SUBSTR(TRIM(uoc.usr_titl),1,1) = LOWER(SUBSTR(TRIM(uoc.usr_titl),1,1))
THEN INITCAP(uoc.usr_titl)
ELSE NVL(uoc.usr_titl,’ ')
END AS “title”
, kntc_add_dt AS “Register Date”
, to_char(kntc_lst_lgn_dt,‘MM/DD/YYYY’) AS “Last Login Date”
FROM CMCADM.USR_ORG_CUST_V1 uoc
INNER JOIN CMCADM.CMC_ORG cmo ON uoc.cmc_org_id = cmo.cmc_org_id
LEFT OUTER JOIN CMCADM.CDF_ORG cdo ON cmo.cid = cdo.cid
INNER JOIN CMCADM.ACCT_TYP_LKP atl ON NVL(cdo.acct_typ_cd,cmo.acct_typ_cd) = atl.acct_typ_cd
WHERE
TRUNC(kntc_lst_lgn_dt) BETWEEN ‘&& Start_Dt_in_dd_mon_yy_format’ AND ‘&& End_Dt_in_dd_mon_yy_format’
ORDER BY org_acct_nm, atl.acct_typ_desc, org_cty_addr;
as far as how i am connecting, it looks like i am using oracle client to connect. the current home it Orahome10201
Doc3.docx (51.8 KB)
Try putting your &&Start_Dt_in_dd_mon_yy_format variable inside of a To_Date function.
To_Date(&&Start_Dt_in_dd_mon_yy_format, ‘dd mon yy’)
Assuming ‘dd mon yy’ is how the date is actually formated. I think your variables are string trying to do a date comparision, some things will implicity convert for you, some only if it is in the one text format that the database is looking for. To_Date will force it into a numeric date value which will make a vaild comparison.
I also tried using [tag:+variable]# and that actually brought up the enter date box and the query ran normally. One extra thing…does Toad support SET VERIFY OFF? that is the other thing that i get an error message on.
The only SQL Plus feature we support is the & and &&. We do not support Set Verify Off. If you have more scripts that need SQL Plus support you can use the “Execute script in external Application” tool bar button. For an oracle connection it is mapped to Script Runner which does support SQL Plus.
ok thank you for the help.