Introduction
BAAN IV provides much functionality within its SQL such as:
SQL Queries Overview
FROM Statement
FROM dmoes001
WHERE dmoes.stck > 100
BAAN IV provides much functionality within its SQL such as:
- Embedded SQL
- SQL Queries
- Text Manager
- Easy SQL
- QBF - Query By Form
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
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)
FROM dmoes001
WHERE dmoes.stck > 100
Optional Query Option
JOIN
SELECT dmoes001.item,
dmoes001.dscr,
dmoes010.dscr
FROM dmoes001,
dmoes010
WHERE dmoes001.igpc = dmoes010.igpc
ORDER BY Statement
SELECT dmoes001.item,
dmoes001.dscr,
dmoes001.stck
FROM dmoes001
ORDER BY dmoes001.dscr
JOIN
- The JOIN is an optional part of a query
- Used to combine rows from two or more tables
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
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)
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.
Webcrs travel is a web-based Travel and ERP for tour operators that helps travel agencies maximize their online bookings while reducing their administrative costs. You can sell your travel products via multiple channels(B2C, B2B and B2B2C) and efficiently manage your operations through our advanced back-office automation module.
Post a Comment