SQL - Nested Queries?

SUGGESTED

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,

Parents
  • 0
    SUGGESTED

    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.

  • 0 in reply to David Speck

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

  • 0 in reply to Justin Langdon

    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).

  • 0 in reply to David Speck

    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?

  • 0 in reply to yourguide

    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.

Reply Children
No Data