SQL Query

Introduction

The Calculator Area in Tally.ERP 9 can be used for processing SQL Queries.

Tally.ERP 9 has an in-built SQL processor that processes SQL Select statements on collections. The collections which are exposed to ODBC are available as tables to the external applications. By default, the methods at the first level of the collection are available for selection.

The general syntax to write an SQL Query in Tally.ERP 9 is:

Note: SQL is not case sensitive. SELECT is the same as select.

For Example let’s write a simple SQL Query to retrieve the names of all the Ledgers:

Here, $Name is the Method Name and Ledger is the Collection.

A Collection is where all the stored information is available. The collection Ledger will have all the information about all the ledgers in the company.

A method is where the particular information of a collection is stored. The method $Name is where the names of all the Ledgers are stored.

Note: The method names will always starts with a “$”.

The result of the above SQL Query is the list if all the ledgers in the company and will be listed as shown:

Note: In a SQL Query the WHERE clause and ORDER BY keyword are optional. The WHERE clause is used to filter the search and the ORDER BY keyword is used to view the results in the desired order.

Examples of SQL Queries

SELECT Statement

1. To retrieve list of all the Collections which are exposed to ODBC

2. To retrieve one method from a collection; say $Name method from Ledger collection

3. To retrieve more than one method from a collection, separate the method names with a comma. For example, to retrieve $Name and $ClosingBalance of Ledger collection

4. To display all methods of a collection; for example, retrieve all the methods of Ledger collection. This SQL Query can be used to discover all the Methods available for a Collection.

Note: “*” will retrieve all the methods of a collection

WHERE Clause

The WHERE clause is used to filter queries based on conditions using functions, mathematical/logical expressions, and patterns/strings.

1. Retrieve the names of the ledgers with only debit closing balance using Functions

Note: to get Credit Closing Balance use the function “NOT $$IsDr”.

2. Retrieve the names of the ledgers whose closing balance is between 10000 and 11000

3. Retrieve the names of the ledgers which have the word “sales” in their name

Note: The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

With the WHERE clause, the following Mathematical operators can also be used:

ORDER BY Keyword

To sort the result of the query the ORDER BY keyword is used. The ORDER BY keyword sorts the result of the query in ascending order by default. To sort the results in descending order the DESC keyword is used

1. For example, to display the names and debit closing balances of all Ledger objects sorted in descending order of closing balances