The Unquote function as shown below no longer works in the SQL of TDP 3.6. When I try it, it does let me enter the variable prompt, but then I get the error: Token : was not valid. Valid tokens: ( NEW FINAL TABLE UNNEST LATERAL XMLABLE . "
This is running an ODBC connection to a DB2 query. I still have a copy of TDP 3.4 and I run this same exact query using this unquote function in it, and it works perfectly fine. I looked at new documentation and I have everything exactly the same and I’m wondering if others are getting the same issue when using variables in this unquote function in TDP 3.6?
{{Unquote(:dbname)}}
Chad King
Hi, just checking, has anyone had the opportunity to test this recently in their TDP 3.6 and see if they are having the same issue with the unquote function?
Chad
I did quick test of
select {{Unquote(:dbname)}} from address
on Oracle and native Db2 LUW. These worked fine for me.
The one thing that is different with TDP 3.6 is that we no longer bundle an IBM client for LUW and Z/OS. Why type of DB2 instance are you connected to? And what is the query you are using this Toad function in?
Debbie, I am not really sure on the DB2 instance. I will say that we connect through an ODBC connection, which is from a software we have called IBM I-Series for Windows. It has drivers that allow us to connect to our AS400 for DB2 queries.
Regarding the query, I think I found why maybe you are not seeing it. It works just fine when I only reference one table, but when I reference a second table it then gets the error:
Token : was not valid. Valid tokens: (NEW FINAL TABLE UNNEST LATERAL XMLTABLE . Also after I hit OK, it fires off a message from TDP saying Missing a parameter to query.
Below is a single table query that works:
select distinct CUNROV, CUCNO
FROM {{Unquote(:NAME)}}LIVFILE.CUMSTPF
Where CUSTA = ‘A’
and CUTYP = ‘V’
Below is a query with two tables referenced and join that gets the error:
select distinct CUNROV, CUCNO
FROM {{Unquote(:NAME)}}LIVFILE.CUMSTPF, {{Unquote(:NAME)}}LIVFILE.HOSTPF
Where CUNROV = HONROV
and CUHO = HONUM
and CUSTA = ‘A’
and CUTYP = ‘V’
Maybe this will shed some more light to see if you are getting the same issue or not??
Thanks for the help.
Chad
I tried this SQL on Oracle and no issues.
SELECT {{Unquote(:dbname)}}
FROM {{Unquote(:dbname)}}, {{Unquote(:dbname1)}}
WHERE {{Unquote(:dbname)}}.REGION_ID = {{Unquote(:dbname1)}}.REGION_ID
and {{Unquote(:dbname)}}.REGION_ID = 2
What is {{Unquote(:NAME)}} supposed to translate to? As well as {{Unquote(:NAME)}}LIVFILE.CUMSTPF
I don’t see any issues in any of your syntax.
can you open up a support case and we can get someone to test this on iSeries. For iSeries we have to set up an appointment with IBM to gain access. It takes a couple of days but this way we can see how the platform type affects it.
P.S. I forget why i had you use this solution. It was a big obscure. Do you remember the circumstances? Perhaps this work around isn’t needed anymore…
Debbie,
It’s supposed to translate to a Database name since we have 10 different ones. Therefore I’d let the prompt come up and enter something like ETX or ARK in that and it would recognize the DB name and know what library to go to.
I’ll work on a support case regarding this.
Lastly, we put this in place because we are a company that has so many different libraries that its helpful when running QC type queries to prompt for this DB name without having to replace each time. It was very cool utilizing in 3.4 and we just now got to a situation to try it in 3.6 and noticed it doesn’t work anymore.
Thanks again. Hopefully getting access to the I-Series connection will aid in ya’lls troubleshooting as well.
Thanks
Chad
FYI, I got a response on Tuesday 12/30 from a Dell Support Engineer regarding this unquote issue in TDP 3.6 and they have responded “The Toad Data Point problem you reported, with the unquote function, has been verified as a defect and logged as defect ID ‘QAT-4575’, which will be evaluated for a future release.” I just wanted to give everyone a heads up on this and hopefully its something we can keep a lookout for in the upcoming releases for a hopeful future fix. Thanks Chad King
I am working on getting a conneciton to iSeries now so I can look at this issue. The fact that the first level of support was able to replicate the issue is very good news and helps tremendously. I’ll try and keep you updated.
Hi Debbie, I was wondering if this issue is resolved in the upcoming 3.7 release? Dell has said they had QAT-4575 set up, plus when I responded to Dell, it says the ticket on their end is closed. Can you please check for me? This is the biggest fix we are hoping to have when getting 3.7. Thanks!
QAT-4575 is marked fixed and tested. QA tested it in Feb 2015. I’d check it real quick but I don’t have connection to iSeries. TDP 3.7 is GA today so download and let us know:)
Debbie, FYI. This does appear to be resolved in TDP 3.7. I can run a single variable to multiple table references. Wishfully thinking, I was hoping it could do more than one variable prompt, for example {{Unquote(:NAME)}} and {{Unquote(:NAME2)}} representing two different variables entered, etc., but it only seems to allow a single one. It only still prompts for 1 variable, which then causes issues for the 2nd one and errors the query out. Not sure if that’s something ya’ll have tried before and maybe i’m just doing it incorrectly. Thanks a bunch! Chad
I don’t think we tried that but I also can’t think of a reason that wouldn’t work. can you post your example?
Kindof as simple as I had listed. Something like select Account from LIVFILE.{{Unquote(:TBL1)}}, LIVFILE.{{Unquote(:TBL2)}} Where Account = Account. Basically if you reference two different unquote variables, it will only prompt for one and not even think to ask for the second, then of course the query errors out, cause the 2nd isn’t recognized. I’m hoping I’m just not doing it right :). Thanks!
I don't get this issue. I get prompted for both. What connection type are you using?
Wow, yeah, mine doesn't look like that. It only gives one variable. I'm using an ODBC generic connection which ties to an IBM I-Series Access ODBC connection that goes to an AS400 system using DB2. It just shows one name, which actually isn't even the same. It says Parameter 1, not Tbl1 like yours and doesn't show anything other than that.
I suspect this has something to do with ODBC only supporting positional params. I entered QAT-5742 to look into this. We are also adding named param support so I will wait until that codeing is done before checking this out. I will let you know
Hi Debbie, I wanted to see if there was any updates regarding QAT-5742 that you entered? Thanks!
This will be in the next Beta, on June 29th.
I apologize for the delay in testing this. Is the QAT-5742 fix on a 3.8 beta version? I still haven’t tested, so I need to figure out which beta is best to test this in. Thanks!!!
That issue says it was fixed back and June. I personally have not tested it. TDP 3.8 will be out Dec 2 or you can try the Beta in this link. www.toadworld.com/…/
