Generic SQL Statements for EAP/Oracle/SQLServer

[crd]

Just wondering if someone can provide direction.  I have written several Model Searches and Word macros using the EA API.  I am running into implementation issues when moving to different database platforms.  What runs beautifully against an EAP file causes Oracle errors.  I would appreciate any guidelines or recommended resources for writing cross-platform SQL statements.  It appears that Oracle is choking on the more complex statements.

Oracle is new turf for me.  Here is a sample of the SQL Statement I am running:

“SELECT o1.Object_ID FROM t_object AS o INNER JOIN (t_connector AS oc INNER JOIN t_object AS o1 ON oc.End_Object_ID = o1.Object_ID) ON o.Object_ID = oc.Start_Object_ID
WHERE o.Object_ID = " & e.elementid & " And oc.Connector_Type = 'Realisation'
ORDER BY o1.Name"

This statement give me a "ORA-000933 error SQL Command Not Properly Ended" error.

Thanks in advance for your help.
Carol

[Eve]

Your problem is your table alias.

From my experience for safety across the different databases EA supports: When using aliases in a query you need to use 'AS' for column aliases, and you can't use 'AS' for table aliases.

[Geert Bellekens]

In theory: refer to the ANSI SQL (92?) standard. That should be supported by all the major databases.
In practice: trial and error. Especially MS-Access (standard eap files) is very particular with regards to the SQL syntax.

Geert