Toad World® Forums

Concatenating varchars in Cross-Connect Query not working


#1

I am running a simple cross connection query against 2 SQL Server 2016 DBs in TDP 4.3.0.718. The quest simply concatenates two varchar fields but it is returning 0.00.

If I try the concatenation in a standard query, it works fine, which leads me to believe this is an underlying issue with the cross-connection query engine.


#2

Toad Data Point is using MySQL as the Cross-connection query engine, means the data from different DBs would fetch to MySQL first and then run the query in MySQL

MySQL is different from most DBMSs use of + for string concatenation. It uses the CONCAT function:
SELECT CONCAT(a.FirstName, ’ ', a.LastName) AS Full_Name FROM …

BTW, In cross query, you can ONLY use the target DB’s sql syntax in sub-query(like the specific keyword of functions in SqlServer or Oracle )
Some ref for sub-query in Toad Data Point cross-connection query
http://dev.toadfordataanalyst.com/webhelp/Content/Query_Builder/Optimize_CrossConnection_Query.htm