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.

People who read this post also read :

Posted in: Enterprise tools,SQL in BAAN ERP