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 :



0 comments:

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More