1 General Rules for Writing Queries
2 Queries Used in Forecast Calculations
- 2.1 New SKUs/Groups Form
- 2.2 Master Forecast Form
- 2.3 Additional Load Form (Experimental)
- 2.4 Safety Stock Adjustment
- 2.5 Forecast Adjustment Form
- 2.6 Forecast View Form
3 Promo Queries
- 3.1 Promo Forms
- 3.2 Promo Processor
4 Queries Used When Calculating Orders in the Replenishment Module
5 Queries Used When Working with Replenishment Module Functionality
- 5.1 Diagrams Form
- 5.2 Calendars Form
  - 5.2.1 The Place of Calendars in the MySales System
  - 5.2.2 FrontEnd Queries
- 5.3 New Stores Form
- 5.4 Specifications Form (Experimental)
- 5.5 Tender Items Form
- 5.6 Orders Form
- 5.7 Item Parameters Form
- 5.8 Additional Load Form
- 5.9 Push Distribution Form
- 5.10 Additional Locations Form
- 5.11 Transfers Form
6 Batches
- 6.1 Order Calculation Batch
  - 6.1.1 Preliminary Operations with Tables Before Order Calculation Begins

General Rules for Writing Queries

Queries in MySales are stored in hierarchically linked .ini files. Each query has its own unique identifier specified in square brackets on the line before the query. For example, [all_stores].

The hierarchy of query files serves not only to improve code readability and organisation, but also to make query customisation possible. To customise a query, it must be redefined under the same identifier. As a result, in the query library, the query text will be the one defined in the last occurrence of the identifier in the chain of .ini files.

Queries are divided into groups according to the types of databases in which they are used. Supported database types: MsSQL, MySQL, Postgres, Oracle. A client can use only one preselected database type.

Parameters may be used in queries; however, their use differs from SQL standards. Thus, the following parameters may occur in the query code:

:param (single numeric parameter)
':param' (single string parameter)
[param] (numeric array)
'[param]' (string array)

Before being sent to the database, the query code is compiled by replacing the parameter text with its value. Thus, the text of the following query:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT 
  from RPM_NEW_STORES n 
  where n.NEW_STORE_ID = :store_id

with the parameter value :store_id = 42 will become:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where n.NEW_STORE_ID = 42

and it is in this exact form that it will be sent to the database driver.

Accordingly, string parameters will be replaced with string literals. This must be kept in mind when working with date parameters: they are always strings and must be enclosed in ampersands. For example:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where n.START_DATE >= ':start_date'

And if, for example, :start_date equals 2026-01-01, the resulting code will be:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where n.START_DATE >= '2026-01-01'

Array parameters are converted into expressions of the form in (v1, v2 …, vN) for numeric arrays and in ('v1', 'v2' …, 'vN') for string arrays. Thus, for example, if we want to specify an array of dates, we must use the parameter as follows:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where n.START_DATE '[start_date]'

which, with the values 2026-01-01 and 2026-02-02, for example, is converted into:

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where n.START_DATE IN ('2026-01-01', '2026-02-02')

ATTENTION! Note that there is no IN in the source code; this operator is added automatically.

ATTENTION! Also, due to this conversion of parameters, it must be understood that the size of the query sent to the database directly depends on the parameter values. This is especially important to consider for arrays. Databases have limitations when parsing queries, and if a query is too long, it may be rejected. Try to ensure that the number of values passed in a numeric array does not exceed 1,000.

Queries Used in Forecast Calculations

check_semaphore_ready

Used in the “Send SMS when there is no semaphore” batch. In the rows, it returns the messages that the system sends by SMS to the corresponding numbers.

all_stores

Used in the classes/loaders/storeDim module. Loads the list of stores into memory so that it does not have to be requested from the database each time. It is periodically reloaded by the loaders/masterData module, depending on the options.loader.reloadMasterDataHours parameter, which is equal to 4 hours by default. It also loads the mappings of stores to regions, weather codes, and price lines (if they are used in the prices_by_sku_store query).

select s.STORE_ID NUM, s.STORE_NAME NAME, s.REGION_ID REGION, w.WEATHER_CODE WEATHER, WHS
    from STORES s
	left join STORE_WEATHER w on s.STORE_ID=w.STORE_ID
    where s.STORE_ID in (select distinct store_id 
    from sku_range 
    where end_date is null or end_date > dateadd(d, -90, getdate()))
    order by s.REGION_ID, s.STORE_ID

STORE_WEATHER is the mapping of a store and a region code used to obtain weather data.

It must consist of the values defined in the weather.locations option.

new_stores

Used in the classes/loaders/storeDim module to load new stores. Like the all_stores query, it is periodically reloaded into memory.

select n.NEW_STORE_ID, n.OLD_STORE_ID, n.COEFFICIENT
    from RPM_NEW_STORES n
    where now() between n.START_DATE and n.END_DATE
        and n.IS_ACTIVE = 1

all_regions

Used in the classes/loaders/storeDim module to load the list of regions. Like the all_stores query, it is periodically reloaded into memory.

select '0' NUM, 'All' NAME, null WEATHER
union all
select r.REGION_ID NUM, r.REGION_NAME NAME, w.WEATHER_CODE WEATHER 
from REGIONS r 
left join REGION_WEATHER w on r.REGION_ID=w.REGION_ID

all_groups

Used in the classes/loaders/ProductDim module to load the complete product group directory. It is periodically reloaded into memory by the loaders/masterData module, depending on the options.loader.reloadMasterDataHours parameter, which is equal to 4 hours by default.

SELECT GROUP_ID as GRP, GROUP_NAME as NAME
    FROM GROUPS
    WHERE GROUP_ID in (
        SELECT distinct GROUP_ID
    FROM PRODUCTS P
    WHERE (P.SKU_ID in (select distinct SKU_ID from SKU_RANGE where end_date >= dateadd(d, -365, getdate())
           or end_date is null)
           or (select min(start_date) from PRICES PR where PR.sku_id=P.sku_id) >= dateadd(d, -90, getdate()) )
    )
    order by GROUP_ID

sku_group

Used in the classes/loaders/ProductDim module to load the complete product directory, usually either limited by the presence of an assortment matrix or, if it is a new product, identifying it by the presence of its first price within the last 30 days. It is also periodically reloaded into memory by the loaders/masterData module, depending on the options.loader.reloadMasterDataHours parameter, which is equal to 4 hours by default.

MySales SQL Queries

General Rules for Writing Queries

Queries Used in Forecast Calculations