SQL - Nested Queries?

Question

I'm new to Sage, using 100 Advanced. 
 For SQL, why doesn't this script work: 
 
 SELECT * 
 FROM (SELECT ItemCode, QuantityOnHand WHERE Warehouse="100" FROM ItemWarehouse) AS STUFF 
 
 Please disregard spelling errors (if any). I've used this style of syntax in the past and it worked fine. But in Sage I keep getting an error. 
 
 I have the same issue with CASE statements. Even basic ones don't seem to work. 
 
 I also cannot do this: 
 SELECT LEFT(ItemCode,1) AS STUFF 
 FROM CI_Item 
 
 I could use some help, if anyone could steer me in the right direction. 
 Thanks,

David Speck · Answer

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

SQL - Nested Queries?

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING