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 :



2 comments:

ERP stands for enterprise resource planning. An ERP Software is specially developed for service providers. Open Source ERP Management tool is a software by which a company manages all business services. which makes the services accessible and predictable. ERP software handles all kind of parts such as purchasing, planning,  sales, human resources etc. KDSS Technologies is one of the estimated IT services providers in India and it provides all kind of solutions that are related to technologies.

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More