Cross Query Error

Toad Data Point
1

When writing a cross query between Oracle v12, direct connect, and MS SQL 2008 R2, ODBC as -

SELECT oc.short_name AS service_provider_id,

ahpt.attribute_1 AS provider_type,

oc.to_date_id AS exit_date

FROM CDRSIT (OMS), OMS.OMS.O_CODE oc,

CDRSIT (OMS), OMS.OMS.O_CODE ahpt,

CDRSIT (OMS), OMS.OMS.O_CODE_TYPE oct

INNER JOIN GTVSQL3 (dsuser).SRWSProviders.dbo.Providers prov

ON oc.SHORT_NAME = prov.Workcover_registration_no

AND ( CAST(prov.Chiropractor AS INT)

  • CAST(prov.Osteopath AS INT)

  • CAST(prov.Physiotherapist AS INT)

  • CAST(prov.ExercisePhysiologists AS INT)

  • CAST(prov.Psychologists AS INT)

  • CAST(prov.Counsellors AS INT)

  • CAST(prov.RMT AS INT) > 1)

WHERE oct.code_name = ‘AHP’

AND oc.parent_code_id = ahpt.oms_code_id

AND oc.code_type_id = oct.code_type_id;

I get the following error -

Category Timestamp Duration Message Line Position
Error 26/08/2016 5:36:44 PM 0:00:09.207 Cross Query Database Error: You have an error in your SQL syntax; check the manual that corresponds to your Cross Query server version for the right syntax to use near ‘INT)
+ CAST(prov.Osteopath AS INT)
+ CAST(prov’ at line 11 1 0

Any assistance appreciated

Cheers

Chris

2

First of all, we don’t support Direct Connect in Cross-Query. Change to Oracle OCI client that has ODBC installed.

Then if you still get the error generate a support bundle from the Help menu. make sure you have the cross-query in a QB or SQL Editor and that is the focused window.

Attached the bundle to this post or email to me directly.