Database>Select statement
Introduction Installation Beginning Admin Quick Ref FTP Server SMTP Server Database Security
Statements Objects String Parsing Events Queues Samples Special names Error Handling Accessories   Back
Caravan Business Server>Help>Database>Select statement
Syntax
<CARAVAN>  
SELECT [*]
FROM [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 as you require it. Typically, a Select statement syntax is
as follows.
      
All clauses within box brackets [..] are optional.       
            
Table Instance object
specifies the Table 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             uses  search engine to test the ocurrence of the expression in the field
      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 con = contacts.contacts
      select from  con where firstname="John"
       </CARAVAN>

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

      //Select rows based on multiple search condition and using both logical operators AND or OR
      <CARAVAN>
      table contacts = contacts.contacts
      // The first selection  returns a record set containing
      select from  contacts where firstname="John" or lastname= "Chris"
      // The following statement will search within the above selected records. (Search within a search)
      // Hence it acts as an AND search. (to reset all selection, give con(selectall)
      select 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.contacts
           select 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 method will 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 one of the following method:
        // Method 1
             delete contacts
             table contacts = contacts.contacts
        // Method 2
             contacts(selectall)  ; // reset all previous selections
      
        // Now the following statement will give you the required result.
             select from  contacts where firstname="Chris"
       </CARAVAN>

        
Quick Reference
Properties Wild Card Search
Using Form Fields, Variables and Constants
Home       Back