Added the ability to use INNER JOIN in normal syntax (non {oj} syntax) over multiple tables as used by default by Crystal Reports XI Professional and Developer Editions. The following SQL statement from a Crystal Report example built using the Visual DataFlex 10.1 Ordering sample, now works correctly:-
SELECT "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER", "CUSTOMER"."NAME", "ORDERHEA"."ORDER_DATE", "ORDERDTL"."ITEM_ID", "ORDERDTL"."EXTENDED_PRICE" FROM ("CUSTOMER" "CUSTOMER" INNER JOIN "ORDERHEA" "ORDERHEA" ON "CUSTOMER"."CUSTOMER_NUMBER"="ORDERHEA"."CUSTOMER_NUMBER") INNER JOIN "ORDERDTL" "ORDERDTL" ON "ORDERHEA"."ORDER_NUMBER"="ORDERDTL"."ORDER_NUMBER" ORDER BY "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER"
The current FlexODBC style of using LEFT OUTER JOINs on more than two tables is currently to do all the join statements in a row then the ON afterwards in reverse order like this:-
SELECT "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER", "CUSTOMER"."NAME", "ORDERHEA"."ORDER_DATE", "ORDERDTL"."ITEM_ID", "ORDERDTL"."EXTENDED_PRICE" FROM "CUSTOMER" "CUSTOMER" LEFT OUTER JOIN "ORDERHEA" "ORDERHEA" LEFT OUTER JOIN "ORDERDTL" "ORDERDTL"
ON "ORDERHEA"."ORDER_NUMBER"="ORDERDTL"."ORDER_NUMBER"
ON "CUSTOMER"."CUSTOMER_NUMBER"="ORDERHEA"."CUSTOMER_NUMBER"
ORDER BY "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER"
and can be implemented using the "Add Command" in Crystal Reports XI. Crystal Reports however does LEFT OUTER JOINs using the same style as INNER JOINs:
SELECT "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER", "CUSTOMER"."NAME", "ORDERHEA"."ORDER_DATE", "ORDERDTL"."ITEM_ID", "ORDERDTL"."EXTENDED_PRICE" FROM {oj ("CUSTOMER" "CUSTOMER" LEFT OUTER JOIN "ORDERHEA" "ORDERHEA" ON "CUSTOMER"."CUSTOMER_NUMBER"="ORDERHEA"."CUSTOMER_NUMBER") LEFT OUTER JOIN "ORDERDTL" "ORDERDTL" ON "ORDERHEA"."ORDER_NUMBER"="ORDERDTL"."ORDER_NUMBER"} ORDER BY "CUSTOMER"."CUSTOMER_NUMBER", "ORDERHEA"."ORDER_NUMBER", "ORDERDTL"."DETAIL_NUMBER"
A future FlexODBC update will also support this style of LEFT OUTER JOIN in normal syntax ({oj} syntax) over multiple tables.
Fixed an issue with SQLStatitics not returning RECORD_NUMBER index information to Microsoft Access correctly when opening DataFlex System Files or DataFlex tables that contain no indexes (.K*s).
These tables connections appeared as read-only tables when using DAO and LinkODBC VBA code in Access. External Linked Tables also required a Unique Record Identifier to be selected by the end user prior to v4.0.19 |