Structured Query Language(SQL) in BAAN ERP

Introduction

BAAN IV provides much functionality within its SQL such as:

• Embedded SQL
• SQL Queries
• Text Manager
• Easy SQL
• QBF - Query By Form

This functionality provides ease of application and database query execution. This chapter introduces basic SQL syntax both using the Text Manager and Easy SQL.


SQL Queries Overview

A query can be created in two ways:

1 Easy SQL

2 Text Manager

Easy SQL is a simple way to create a query. It is primarily menu-driven. Text Manager queries are more complex to create, and require the user to have knowledge of the syntax and data models.

The output of a query can be sent in three different formats:

1 Report Label: The data will be printed in a normal report format

2 Report Label: The output can be set in a specific size and format

3 Chart: The output is printed in a graphical presentation format

Also, query can be executed on a menu.

SQL Query: Syntax overview

General structure of a query is as follows:

SELECT datafield name(s)

FROM  table name(s)

WHERE conditions(s)

SELECT Statement

The SELECT statement is a mandatory part of each query. 

• It is used to retrieve data from one or more tables to be used in the output of the query.
• Select *: means all columns from all tables listed in the FROM clause will be selected.
• To select more than one table field but not all, use a comma separating each field name.

Examples:

SELECT dmoes001.item

SELECT dmoes001.item,dmoes001.ppri

FROM Statement

The FROM is a mandatory part of each query.

Used to specify the table(s) used in the query. If more than one table is used, the table names must be separated by commas.

Example: SELECT dmoes001.item, dmoes001.dscr, dmoes001.stck FROM dmoes001 

WHERE Statement

• The WHERE is an optional part of a query.
• Used to specify conditions for the selected table fields.These conditions determine which records will be selected from the database.
• WHERE<Operand>
• Numeric Constants…….10, 100, 4.50
• Strings…………………..”Sub”, “[Jj]hon”
• Table Fields…………….dmoes001.item
• Compare with Enumerated Constants, use domain and constant……………… …tckitm.purchase
• Dates……………………date(1993,01,01)

Examples: SELECT dmoes001.item, dmoes001.dscr, dmoes001.stck 
FROM dmoes001 
WHERE dmoes.stck > 100   

Optional Query Option
JOIN

• The JOIN is an optional part of a query 
• Used to combine rows from two or more tables

Example:
SELECT  dmoes001.item,
dmoes001.dscr,
dmoes010.dscr
FROM  dmoes001,
dmoes010
WHERE  dmoes001.igpc = dmoes010.igpc


ORDER BY Statement

• The ORDER BY is an optional part of a query
• Used to order the rows to sort (Ascending or Descending)
• Default is Ascending

Example: Sort by item description
SELECT  dmoes001.item,
dmoes001.dscr,
dmoes001.stck
FROM  dmoes001
ORDER BY dmoes001.dscr

GROUP BY Statement

• The GROUP BY is an optional part of a query
• Used to group selected rows and return a single row of summary information

Example:

SELECT  tccom010.creg, count(*)

FROM  tccom010

GROUP BY  tccom010.creg

Note: Try the example without GROUP BY to see the difference.

HAVING Statement

• The HAVING statement is an optional part of a query
• Use this clause to select within a group

Example:

SELECT  tdsls041.cuno, sum(tdsls041.item)

FROM  tdsls041

GROUP BY  tdsls041.cuno

HAVING  sum(tdsls041.item) < 100

DATE

• The DATE is an optional part of a query
• Used to select specific date equivalents

Example:

SELECT  tdsls041.odat

FROM  tdsls041

WHERE  tdsls041.odat > date(1993,01,01)

People who read this post also read :

Posted in: BAAN ERP,Enterprise tools,SQL in BAAN ERP