Toad World® Forums

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

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?