Home
Main Introduction Features Constants Statements Database Objects Parser Serial-Communication Samples Misc
Database>Select statement
Caravan Business Server>Help>Database>Select statement
Syntax <CARAVAN>
SELECT [*]
FROM <connection object>
[WHERE <search_condition>]
[ORDER BY <order_expression> [ASC | DESC] ]
</CARAVAN>
Text The Select statement is used to retrieve rows from the database based on a query. You can select one or more rows or columns from one or more tables. The Select statement is powerful and can be as complex asyou require it. Typically, a Select statement syntax is as follows.

All clauses within box brackets [..] are optional.

Connection object specifies the connection instance to a table source from which rows are to be retrieved.

search condition specifies the search condition to restrict the rows returned. Search condition is a combination of one or more expressions using logical operators such as AND and OR that returns TRUE, FALSE, or UNKNOWN. There is no limit to the number of expressions that can be included in a search condition. An expression is a column name, a constant, a function, a variable, or any combination of column names, constants, and functions connected by one or more operators.

Order expression specifies the column on which to sort the result set. ASC specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value. DESC specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value
Logical operators
AND
Combines two conditions and evaluates to TRUE when both of the conditions are TRUE.

OR
Combines two conditions and evaluates to TRUE when either condition is TRUE.

Relational Operators
= tests the equality between two expressions.
<> tests the condition of two expressions not being equal to each other.
!= tests the condition of two expressions being equal to each other.
> tests the condition of one expression being greater than the other.
>= tests the condition of one expression being greater than or equal
to the other expression
< tests the condition of one expression being less than the other.
<= tests the condition of one expression being less than or equal to
the other expression.
like tests the condition of one expression being like the other expression.
string_expression is a string of characters and wildcard characters.
Sample Select all rows from a table.

<CARAVAN>
table company = contacts.company
select from company
</CARAVAN>

Select all rows based on a search condition.

<CARAVAN>
table contacts = contacts.contacts
select from contacts where firstname="John"
</CARAVAN>

Select columns and rows based on a search condition.
<CARAVAN>
table contacts = contacts.contacts
select firstname, lastname from contacts where firstname="John"
</CARAVAN>

Select rows based on multiple search condition.
<CARAVAN>
table contacts = contacts.contacts
select firstname, lastname from contacts where firstname="John" and workphone like "6550291"
</CARAVAN>

Select rows based on multiple search condition and using both logical operators AND and OR.

<CARAVAN>
table contacts = contacts.contacts
// The first selection returns a record set containing
select firstname, lastname from contactswhere firstname="John" or lastname= "Chris"
select firstname, lastname from contacts where Title="Manager"
</CARAVAN>

Select rows based on search condition and sorted order.

Ascending sort order.
<CARAVAN>
table contacts = contacts.contacts
select from contacts where firstname="John" order by firstname asc
</CARAVAN>

Descending sort order.
<CARAVAN>
table contacts = contacts.contactsselect from contacts where firstname="John" order by firstname desc
</CARAVAN>
<CARAVAN>
table contacts = contacts.contacts
select from contacts where firstname="John"

// returns records where firstname is John
//now if your run another statement like

select from contacts where firstname="Chris"

//You will not get any records as the selection is done on the previous set of records.
//the correct methodwill be

table contacts = contacts.contacts
select from contacts where firstname="John"

// returns records where firstname is John
// now to run another select statement which returns the firstname OR any other different selection
// you must follow this method.
delete contacts
table contacts = contacts.contacts
select from contacts where firstname="Chris"
</CARAVAN>
Properties Wild Card Search
Using Form Fields, Variables and Constants
Back