Select

Kalipso - Form - Actions - Actions Description - Group Database - Select

Selects one or more records from the specified Database Table.

Action available for the following operating systems:

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.

Usage

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

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

Example 2

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

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

Example 4

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

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

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

Example 6

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.

PreviousUpdateNextDelete

Last updated