WHERE Conditions and Functions

Within the WHERE clause, the operand1 can be one of the following:

• Numeric constant
• String constant
• Table Field
• Numeric Expression
• String Expression
• Special Field (enum, date)
• Combined Field

Operator Syntex Figure

The operator is used to compare or set conditions between operand1 and operand2.

Operand Syntax:

Overall syntax for WHERE statement:

Arithmetical Operators for WHERE Conditions:

• [+] [-]: Positive, Negative (value indicators)

Example: Show negative stocks.

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck

FROM dmoes001

WHERE dmoes001.stck=-10

• [+] [-]: Adds, Subtracts (binary)

Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck, 

dmoes001.ostk

FROM dmoes001

WHERE (dmoes001.stck-dmoes001.ostk)>50

• [*]: Multiplies
• [/]: Divides

Example: Check 30% profit or more.

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.ppri,

dmoes001.spri

FROM dmoes001

WHERE dmoes001.spri>dmoes001.ppri*.3

Relational Operators for WHERE Conditions:

• [=]: Equality test

        Example:

SELECT dmoes001.item,

  dmoes001.dscr,

dmoes001.stck

FROM dmoes001

WHERE dmoes001.item = 10

• [!=] [^=] [<>]: Inequality test.

   Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE dmoes001.ostk <> 10

• [>]: Greater than
• [<]: Less than

Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE dmoes001.ostk < 10

• [>=]: Greater than or equal to.
• [<=]: Less than or equal to.

Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE dmoes001.ostk <= 10

Logical Operators for WHERE Statements:

• AND:

Returns TRUE if both component conditions are TRUE; otherwise returns FALSE.

Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE dmoes001.ostk > 10 and

dmoes001.stck > 10

• OR:

Returns TRUE if either component conditions are either TRUE or unknown; otherwise     returns FALSE.

Example:

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE dmoes001.ostk > 10 or

dmoes001.stck > 10

Example: OR clause using parentheses

SELECT dmoes001.item,

dmoes001.dscr,

dmoes001.stck,

dmoes001.ostk

FROM dmoes001

WHERE (dmoes001.igpc = “010” and dmoes001.spri > 50) or

(dmoes001.igpc = “020” and dmoes001.spri > 100)

• NOT:

Returns TRUE if the following condition is FALSE; otherwise returns FALSE.

  The NOT condition is used with these conditions:

Between

In

Like

The NOT is placed in front of these conditions.

Read More

Select Internal Functions in SQL

Key Words

• min
• max
• avg
• sum
• count

Select Statement Optional Functions

In the SELECT statement you can use the following built-in functions:

• COUNT number of records

Example:
SELECT COUNT(dmoes001.item)
FROM dmoes001

• AVG average value in column

Example:
SELECT AVG(dmoes001.spri)
FROM dmoes001

• SUM sum of the values in column

Example:
SELECT SUM(dmoes001.spri)
FROM dmoes001

• MAX maximum value in a column

Example:
SELECT MAX(dmoes001.spri)
FROM dmoes001

• MIN minimum value in a column

Example:
SELECT MIN(dmoes001.item)
FROM dmoes001

Read More

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)

Read More