The ProvideX ODBC driver doesn't use the standard syntax for table joins.

Refer to the attached document for more info on its usage.

6886.PVX-ODBC4x.pdf

You did mention linked server so i don't recall specifically whether SSMS expects its T-SQL syntax or if you have to use the PVX SQL syntax which uses some variant of the SQL-92 standard if i recall correctly.

Moving along, in order to join tables, you have to use a {oj keyword in the join and you can't join more than one table per {oj keyword, you have to duplicate common joins and then use the WHERE clause to join the common tables together. In most cases, if you have  abunch of tables using inner joins, you can get away with not including the {oj keyword in the FROM clause and just list each table followed by its alias with commas separating each table and then use the WHERE clause to join them all to together.

If you must include any kind of outer join, you must include the {oj keyword and the ON keyword in the join. Here is a basic example.

SELECT * 
FROM 
{oj 
CI_Item CII 
Left Outer Join 
SO_SalesOrderDetail SOD 
On 
( 
CII.ItemCode = SOD.ItemCode
)
}

Keep in mind that any fields from a table joined as the outer join included in the WHERE clause will produce Inner Join results.

If you have more than two tables involved and need an outer join between two of them, you can use the WHERE clause to handle the common joins to produce the inner joins and use the {oj join for the two tables that are needed in the outer join. Here is another basic example of this.

SELECT * 
FROM 
SO_SalesOrderHeader SOH, 
{oj 
SO_SalesOrderDetail SOD 
Left Outer Join 
PO_PurchaseOrderHeader POH 
ON 
( 
SOD.PurchaseOrderNo = POH.PurchaseOrderNo 
) 
} 
WHERE 
SOH.SalesOrderNo = SOD.SalesOrderNo 
 

The ProvideX ODBC driver doesn't use the standard syntax for table joins.

Refer to the attached document for more info on its usage.

6886.PVX-ODBC4x.pdf

You did mention linked server so i don't recall specifically whether SSMS expects its T-SQL syntax or if you have to use the PVX SQL syntax which uses some variant of the SQL-92 standard if i recall correctly.

Moving along, in order to join tables, you have to use a {oj keyword in the join and you can't join more than one table per {oj keyword, you have to duplicate common joins and then use the WHERE clause to join the common tables together. In most cases, if you have  abunch of tables using inner joins, you can get away with not including the {oj keyword in the FROM clause and just list each table followed by its alias with commas separating each table and then use the WHERE clause to join them all to together.

If you must include any kind of outer join, you must include the {oj keyword and the ON keyword in the join. Here is a basic example.

SELECT * 
FROM 
{oj 
CI_Item CII 
Left Outer Join 
SO_SalesOrderDetail SOD 
On 
( 
CII.ItemCode = SOD.ItemCode
)
}

Keep in mind that any fields from a table joined as the outer join included in the WHERE clause will produce Inner Join results.

If you have more than two tables involved and need an outer join between two of them, you can use the WHERE clause to handle the common joins to produce the inner joins and use the {oj join for the two tables that are needed in the outer join. Here is another basic example of this.

SELECT * 
FROM 
SO_SalesOrderHeader SOH, 
{oj 
SO_SalesOrderDetail SOD 
Left Outer Join 
PO_PurchaseOrderHeader POH 
ON 
( 
SOD.PurchaseOrderNo = POH.PurchaseOrderNo 
) 
} 
WHERE 
SOH.SalesOrderNo = SOD.SalesOrderNo 
 

I'd do "select * ..." from queries as SQL Views, then do another query with the joins inside SQL on those Views (avoiding the complex Providex join syntax). 

Unless you have a massive performance hit from pulling in unfiltered table data, that approach is much easier to deal with.

That is typically the best way to go if you have SSMS available, if not and you're dealing with the Standard or Advanced platforms and trying to add complicated joins to a crystal report that can't be handled with sub-reports, you don't have much choice unless you want to involve MS Access for passthrough queries or SQL Server Express if possible.

I use SQL Express often, when dealing with complex joins in reports (and Providex data).

I'm using SQL Server 2014

Have you tried using unfiltered SQL Views (one table at a time), then doing the joins in SQL (based on the views) as I suggested?

More common for me is to just use queries to refresh mirror tables (stored in SQL), but I don't see why linked server queries wouldn't work too.

I'll try this way. Thanks for your replay.