How to Create a Sage X3 Stock Quantities SQL Query

Sage X3 offers robust tools for managing inventory, but sometimes, users need more granular or customized stock reporting than what’s available in the standard interface. Whether you’re building a custom dashboard, reconciling stock levels, or integrating with external systems, a detailed SQL query that pulls accurate stock quantities is essential.

In this article, we’ll walk through how to create a comprehensive stock quantities SQL SELECT statement using Sage X3’s database structure, including key tables, filters, and joins. You can use a Sage X3 stock quantities SQL query to do a granular analysis of your own data.

How to a Create a Detailed Stock Quantities Query from an SQL Select Statement in Sage X3

While Sage X3 provides a number of out-of-the-box reports and queries, these reports often don’t return the desired data. Fortunately, Sage X3 includes multiple tools you can use to create custom queries. Some of these tools are meant for professionals who know SQL well but don’t have the skills to build queries using SQL tools.

The key advantage of this custom query is that it delivers inventory data for all products, broken down to the precise location (bin) level.

While Sage X3’s native stock query shows inventory across the system, it lacks detailed location-level granularity. Conversely, the built-in stock inquiry does provide location-level details, but only for a single product at a time.

This custom SQL query combines the best of both: a complete inventory view with full location-level detail across all products.

This is an example of a real-life request from a customer using Sage X3 for a global list of all products in stock by locations (bins). We built a Sage X3 stock report query to fulfill their request.

Create Stock by Location Query

First, follow this path to access a built-in utility that allows to you to create, save, and execute custom SQL queries within Sage X3.

Setup > Usage > Reports > SQL Query tool

Once you’ve followed this navigation path, click “New” to create a new query. Then, enter the Query code and description (e.g., ZLOCSTK, “Stock by Location”).

Next, paste the SQL SELECT query code into the SQL query fields in the Setup section:

SELECT

    S.ITMREF_0 AS product_code,

    I.ITMDES1_0 AS product_description,

    L.STOFCY_0 AS Site,

    L.LOCTYP_0 AS location_type,

    S.LOC_0 AS location,

    S.QTYSTU_0 AS stock_quantity,

    S.STA_0 AS status

FROM

    STOCK S

    INNER JOIN ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0

    INNER JOIN STOLOC L ON S.LOC_0 = L.LOC_0 AND S.STOFCY_0 = L.STOFCY_0     LEFT JOIN ITMFACILIT F ON S.ITMREF_0 = F.ITMREF_0 AND S.STOFCY_0 = F.STOFCY_0

In the Columns section, define column descriptions and types for all the fields returned by the SQL SELECT statement, as shown in the screenshot below.

Check the Activate checkbox and then save the query. After it’s saved, click the Validate button. After validation is complete, the query is ready to use. It can be tested using the Run button.

You should see the results of the query execution.

The query is accessible via the following path:

Reports > Reports > View queries Sage X3 menu

The query can be exported into MS Excel format by selecting “Export” from the ellipses button. The option to Export to Excel is only available on computers with a Windows operating system. In addition, you must have the Sage X3 Office Add-on installed. For other machines, you can use CSV format instead.

What You Can Do With It

Using this utility allows you to create, save, and execute custom SQL queries within Sage X3, without the need for external database tools like SQL Server Management Studio.

You can perform multiple functions with this tool, including:

• Building custom inventory, sales, or purchasing reports
• Joining multiple tables (e.g., STOCK, ITMMASTER, BPCUSTOMER)
• Filtering by site, product, status, dates, etc.
• Testing SQL queries before integrating them into Crystal Reports or dashboards

Ready to Get More from Your Sage X3 Data?

If you’re looking to build powerful inventory reports, automate stock tracking, or get deeper insights from Sage X3, we can help. Our team specializes in all things related to Sage X3, including full Sage X3 implementations, Sage X3 Magento integrations, custom Sage X3 enhancements, and creating custom SQL queries in Sage.

Contact us today to see what we can do for you business.

Sage X3 Inventory SQL Query FAQ

Can I use SQL queries directly inside Sage X3 without external tools?

Sage X3’s built-in SQL query tool, which can be found under the pathway listed below, allows users to create, save, and execute custom SQL queries directly within Sage X3 without the need for external databases or tools.

Setup > Usage > Reports > SQL Query Tool

Follow this path to run a custom stock report Sage X3.

What tables should I use to report stock quantities in Sage X3?

The most commonly used tables for stock reporting include:

• STOCK (stock quantities)
• ITMMASTER (item master data)
• STOLOC (location details)
• ITMFACILIT (item-site configuration)

Why isn’t the Export to Excel option available in my query results?

The Export to Excel option is only available on Windows machines with the Sage X3 Office Add-on installed. On other systems, you can export results as a CSV file.

Can I filter my stock query by site or location?

You can filter your Sage X3 stock query by site or location using the WHERE clause to filter by specific fields such as STOFCY_0 (site), LOC_0 (location), or STA_0 (status).

Can I schedule SQL queries to run automatically in Sage X3?

While you can’t schedule queries directly using the SQL Query Tool, you can use Sage X3’s batch server functionality or export the query to a Crystal Report or BI tool that supports scheduling.

Do I need SQL expertise to use the SQL Query Tool in Sage X3?

While some SQL knowledge is helpful, even users with basic experience can build simple queries in Sage X3. More complex queries (joins, filters, aggregations) may require intermediate SQL skills.

What is SQL Select Statement in Sage X3?

In Sage X3, you can use the SQL Select Statement function to retrieve data from a database table, just like in standard SQL. However, Sage X3 has its own layer of abstraction and tools that sit on top of the SQL engine to provide additional functionality, like Query Tools, Reports, and Crystal Reports.

Sage X3 Tips and Tricks

This is part of our ongoing series of Sage X3 user guides. We’re covered other topics including: