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 :



0 comments:

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More