Hi, Try the following

SELECT ItemCode AS "Stuff", QuantityOnHand AS "Quantity of Stuff"
FROM IM_ItemWarehouse
WHERE WarehouseCode='100'

See the ODBC driver guide 

CASE statements are not supported in the Providex ODBC driver from Sage.

They are supported in the version of the driver from PVX PLUS.

John Nichols

Sage. 

John,

Does the PVX PLUS ODBC driver support the Sage company and login feature?

Thanks Jcnichols. I'm not really interested in the "Stuff" part of the issue, that's just to keep the example simple.

Here's what I want:

SELECT *

FROM (SELECT ItemCode, SUM(QuantityOnHand WHERE WarehouseCode='100') AS OnHand GROUP BY ItemCode) as INV

For my own reasons.

I know I can do this independently, without nesting it, but I should be able to run this script.

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.

Sage 100 Advanced does not have a SQL server back end (it uses a file based data storage format called Providex), so the full SQL language is not implemented.  You get what the Providex ODBC driver supports... and it does what it does.

If there is something you can't figure out a work-around for, ask.  There are techniques commonly used to get around the driver's limitations.

Thanks Kevin! I'll be sure to pick your brain in the future! 

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.