Contents
Display Legacy Contents

 Print
    SELECT  
Selects one or more records from the specified Database Table.

Parameters


SELECT
  From
 
Database Table where to retrieve the records from.
 
 
  Function
 
Function applied to the specified column. For further information about this parameter, please consult the documentation of your Database or SQLite if you're working off-line.

  AVG
 
  Returns the average value of the specified Column.
  COUNT
 
  Returns the number of records.
  MAX
 
  Returns the maximum value of the specified Column.
  MIN
 
  Returns the minimum value of the specified Column.
  SUM
 
  Returns the sum of the values of the specified Column.
 
 
  Column
 
Column to be retrieved.
 
 
  Into <target>
 
Control or Variable where to store the retrieved value.
 
 
  (...)
 
 
  Nth From
 
Table where to retrieve the records from.
 
 
  Nth Function <unquoted string>
 
Function applied to the specified column. For further information about this parameter, please consult the documentation of your Database or SQLite if you're working off-line.
 
 
  Nth Column <unquoted string>
 
Column to be retrieved.
 
 
  Nth Into <unquoted string>
 
Control or Variable where to store the retrieved value.
 
 
Ignore Database Masks
  If you've defined a Mask for the specified Column in the Table properties and:

This option is checked
 - 
The value is return the Kalipso format.
This option is unchecked
 - 
The value is returned with the Mask format.
     
Clear Data When No Record is Returned
  If this option is checked and no record is returned, all the targets (Into parameter) are cleared.
     
Where <Kalipso Filter> or <string>
 
Condition to specify which records should be retrieved. Check Expressions chapter for more information about Kalipso Filter and String expressions.

     
This function generates a Select operation that exists in every SQL database, and is used to query the database and retrieve data according to one ore more conditions.
 
Consider the following Database Table, and the following Variables:
 
 Products
Code  Name  Stock
1 Coca Cola 100
2 Pepsi Cola 200
3 Pork Chops 300
   
Code Name Stock
VAR(0) String Kalipso
VAR(1) Numeric 0
VAR(2) String Kalipso1
 
Consider that column Code is Numeric, column Name is String and column Stock is Numeric.
Note that the table showing variables isn't a database table, its purpose is to show only the values and type of each variable.

Example 1
  Example 2
From Function Column Into
Products <none> Code VAR(0)
Products <none> Name VAR(1)
Products <none> Stock VAR(2)
 
Where FIELD(Products,Code)=001
Clear Data Yes

Result:

Variable Type Value
VAR(0) String 1
VAR(1) Numeric Coca Cola
VAR(2) String 100
 
From Function Column Into
Products AVG Code VAR(0)
Products <none> Name VAR(1)
Products SUM Stock VAR(2)
 
Where FIELD(Products,Code)=004
Clear Data Yes

Result:

Variable Type Value
VAR(0) Numeric  
VAR(1) String  
VAR(2) Numeric  

Example 3
  Example 4
From Function Column Into
Products <none> Code VAR(0)
Products <none> Name VAR(1)
Products <none> Stock VAR(2)
 
Where FIELD(Products,Code)=004
Clear Data No

Result:

Variable Type Value
VAR(0) String kalipso
VAR(1) Numeric 0
VAR(2) String kalipso1
 
From Function Column Into
Products AVG  Code VAR(0)
Products <none> Name VAR(1)
Products SUM Stock VAR(2)
 
Where FIELD(Products,Code)=004
Clear Data No

Result:

Variable Type Value
VAR(0) Numeric  
VAR(1) String  
VAR(2) Numeric  

Notes: In this example, the option Clear Data is disabled but are used functions AVG and SUM , and because of that, this operation return on record, therefore types are enforced.

Example 5
  Example 6
From Function Column Into
Products SUM  Stock VAR(2)
 
Where FIELD(Products,Code)<>0
Clear Data Yes




Result:

Variable Type Value
VAR(0) String kalipso
VAR(1) Numeric 0
VAR(2) Numeric 600
 
From Function Column Into
Products MIN Code VAR(0)
Products COUNT Name VAR(1)
Products SUM Stock VAR(2)
 
Where FIELD(Products,Code)<> ""
Clear Data No

Result:

Variable Type Value
VAR(0) Numeric 1
VAR(1) String 3
VAR(2) Numeric 600

Notice that, as you can see in the examples above, Variable types are enforced, except if there is no result of the select operation and parameter Clear Data isn't checked.


     
  • When you specify more than one condition in Where parameter, the operator used between the conditions is "AND" or "OR".
  • You can use the RECORDCOUNT Keyword to know how many records where returned.

See also
Samples

 
 
Updated: 3/21/2018 5:41 PM