When using the ProvideX ODBC driver with Sage 100 Standard & Advanced, in order to use scaler functions, you have to start it with "{fn" and end it with "}".

So to use left, you would use it like this.

Select 
{fn Left(CI_Item.ItemCode), 1} As "Stuff"
From
CI_Item CI_Item

It does not support CASE or IIF.

It does not support nested queries, if you need nested queries, you either need to use SQL Server (Express works) with a linked server or an Access database with linked tables or passthrough queries.

To get what you want in your most recent post, you need to use the GROUP BY clause.

Select 
IM_ItemWarehouse.ItemCode, 
Sum(IM_ItemWarehouse.QuantityOnHand) AS "Inv" 
From 
IM_ItemWarehouse IM_ItemWarehouse 
Where 
IM_ItemWarehouse.WarehouseCode = '100' 
Group By 
IM_ItemWarehouse.ItemCode 

EDIT: Forgot the second argument to the Left function in my original post so it has been corrected.

When using the ProvideX ODBC driver with Sage 100 Standard & Advanced, in order to use scaler functions, you have to start it with "{fn" and end it with "}".

So to use left, you would use it like this.

Select 
{fn Left(CI_Item.ItemCode), 1} As "Stuff"
From
CI_Item CI_Item

It does not support CASE or IIF.

It does not support nested queries, if you need nested queries, you either need to use SQL Server (Express works) with a linked server or an Access database with linked tables or passthrough queries.

To get what you want in your most recent post, you need to use the GROUP BY clause.

Select 
IM_ItemWarehouse.ItemCode, 
Sum(IM_ItemWarehouse.QuantityOnHand) AS "Inv" 
From 
IM_ItemWarehouse IM_ItemWarehouse 
Where 
IM_ItemWarehouse.WarehouseCode = '100' 
Group By 
IM_ItemWarehouse.ItemCode 

EDIT: Forgot the second argument to the Left function in my original post so it has been corrected.

Thanks David. This helps. I'm going to miss nested queries, but at least I can work with the function codes.

If you want nested queries, go through a Linked Server with SQL Server (Express works too), Linked Tables or Passthrough queries through MS Access, or use a modern version of Excel with PowerQuery (can do all sorts of queries, joins, multiple steps and stuff with PowerQuery, just query each table individually, then merge as needed, apply filters where appropriate, best part is you don't have to write SQL statements by hand).

Hi David!  

I am very interested in getting to Sage Data from external applications. You mention using a linked server, is this for the File system based ProvideX version of Sage and its ODBC driver or are you referring to the SQL version of Sage that uses an SQL database?

A Linked Server set up in MS SQL Server instance can be used for any edition of Sage 100 (as well as any database that can be queried through ODBC drivers and/or OLE providers) in order to leverage the T-SQL syntax and features.