Issue with math formulas on Two Fields in a Cross Connection Query

Toad Data Point & Toad Intelligence Central
1

This is really frustrating, cause I have searched and searched for some guidance and cannot find any documentation.

I have this CASE statement in my Cross Connection Query that before it was a Cross Connection worked fine and is completely OK in SQL.

CASE
WHEN SUM(sp.ServiceTypeId)/Count( Coalesce(x.SiteId, x.AccountId)) = 3 THEN 'Electic & Gas'
WHEN SUM(sp.ServiceTypeId)/Count( Coalesce(x.SiteId, x.AccountId)) = 1 THEN 'Electric'
WHEN SUM(sp.ServiceTypeId)/Count( Coalesce(x.SiteId, x.AccountId))= 2 THEN 'GAS'
Else 'Electric'
END as 'Fuel Type'

As soon as I join a table for another DB It get an error.

'Cross-Query Database Error: Unknown column 'sp.ServiceTypeId' in 'field list'. Make sure that the table is fully qualified, and that the defined table alias is also included in the column name. Check that col'

Has anyone else encountered this and what is the proper syntax for Cross DB Queries?

2

This post is old but I am getting the exact error with a CASE statement on a cross connection query. Works before the cross connection. I also simplified the CASE to one WHEN clause and it worked in my cross connection query. When I add the second WHEN it fails. Same error. Any ideas?