SELECT SQL

Document Sample
SELECT SQL Powered By Docstoc
					SELECT - SQL Command

Retrieves data from one or more tables. The SELECT SQL command is built into Visual FoxPro like any other Visual
FoxPro command. When you use SELECT to pose a query, Visual FoxPro interprets the query and retrieves the
specified data from the tables. You can create a SELECT query from within the following:

         Command window
         Visual FoxPro program as with any other Visual FoxPro command
         Query Designer

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] [Alias.] Select_Item
 [[AS] Column_Name] [, [Alias.] Select_Item [[AS] Column_Name] ...]
 FROM [FORCE] [DatabaseName!] Table [[AS] Local_Alias]
 [ [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN DatabaseName!]
    Table [[AS] Local_Alias] [ON JoinCondition ...]
    [[INTO Destination] | [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]]
    [PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]
    [WHERE JoinCondition [AND JoinCondition ...] [AND | OR FilterCondition [AND | OR FilterCondition ...]]]
 [GROUP BY GroupColumn [, GroupColumn ...]] [HAVING FilterCondition] [UNION [ALL] SELECTCommand]
 [ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

Parameters

SELECT
          Specifies the fields, constants, and expressions displayed in the query results.
ALL
        Displays all the rows in the query results by default.
DISTINCT
        Excludes duplicates of any rows from the query results. You can use DISTINCT only once per SELECT
        clause.

          Visual FoxPro does not support using the DISTINCT clause in a SQL SELECT statement that contains memo
          or general fields. Instead, you can wrap a Memo field expression inside a function such as PADR( ) or
          ALLTRIM( ). For more information, see PADL( ) | PADR( ) | PADC( ) Functions and ALLTRIM( ) Function.

TOP nExpr [PERCENT]
        Specifies the query result contain a specific number of rows or a percentage of rows of the query result.
        You can specify from 1 to 32,767 rows. If you include the PERCENT option, permissible values for nExpr
        are 0.01 to 99.99. When you include the PERCENT option, the number of rows returned in the result is
        rounded up to the next highest integer.

          Rows with identical values for the columns specified in the ORDER BY clause are included in the query
          result. Therefore, if you specify 10 for nExpr, the query result can contain more than 10 rows if there are
          more than 10 rows with identical values for the columns specified in the ORDER BY clause.

          When you include the TOP clause, you must include an ORDER BY clause. The ORDER BY clause specifies
          the columns on which the TOP clause determines the number of rows to include in the query result.

[Alias.] Select_Item
          Qualifies matching item names. Select_Item specifies an item to be included in the query results. An item
          can be one of the following:

                                                           1
                The name of a field from a table in the FROM clause.
                A constant specifying that the same constant value appears in every row of the query results.
                An expression that can be the name of a user-defined function.

        For more information about using user-defined functions, see User-Defined Functions with SELECT in the
        Remarks section.

        Each item you specify with Select_Item generates one column of the query results.

        If two or more items have the same name, include the table alias and a period before the item name to
        prevent columns from being duplicated.

[AS] Column_Name
         Specifies the heading for a column in the query output. Column_Name can be an expression but cannot
         contain characters that are not permitted, for example, spaces, in table field names.

        This option is useful when Select_Item is an expression or contains a field function and you want to give
        the column a meaningful name.

FROM [FORCE] DatabaseName!
       Lists the tables containing the data that the query retrieves.

        FORCE specifies that tables are joined in the order in which they appear in the FROM clause. If FORCE is
        omitted, Visual FoxPro attempts to optimize the query. However, the query might be executed faster by
        including the FORCE keyword to disable the Visual FoxPro query optimization.

        DatabaseName! specifies the name of a non-current database containing the table. You must include the
        name of database containing the table if the database is not the current database. Include the
        exclamation point (!) delimiter after the database name and before the table name.

[[AS] Local_Alias]
         Specifies a temporary name for the table named in Table. If you specify a local alias, you must use the
         local alias in place of the table name throughout the SELECT statement.

        INNER JOIN specifies that the query result contain only rows from a table that match one or more rows in
        another table.

        LEFT [OUTER] JOIN specifies that the query result contains all rows from the table to the left of the JOIN
        keyword and only matching rows from the table to the right of the JOIN keyword. The OUTER keyword is
        optional; you can include it to emphasize that an outer join is created.

        RIGHT [OUTER] JOIN specifies that the query result contain all rows from the table to the right of the JOIN
        keyword and only matching rows from the table to the left of the JOIN keyword. The OUTER keyword is
        optional; it can be included to emphasize that an outer join is created.

        FULL [OUTER] JOIN specifies that the query result contain all matching and nonmatching rows from both
        tables. The OUTER keyword is optional; you can include it to emphasize that an outer join is created.

        For more information about joins, see Joins in the Remarks section.



                                                         2
        ON JoinCondition specifies the conditions for which the tables are joined.

INTO Destination
        Specifies where to store the query results. Destination can be one of the following clauses:

                ARRAY ArrayName stores query results in a memory variable array.

                 The array is not created if the query selects 0 records.

                CURSOR CursorName [NOFILTER | READWRITE] stores query results in a cursor.

                 To create a cursor that can be used in subsequent queries, use NOFILTER. For more information
                 about NOFILTER, see the Remarks section.

                 To specify that the cursor is temporary and modifiable, use READWRITE. If the source table or
                 tables use autoincrementing, the settings are not inherited by the READWRITE cursor.

                DBF | TABLE TableName [DATABASE DatabaseName [NAME LongTableName]] stores query
                 results in a table.

                 To specify a database to which the table is added, include DATABASE DatabaseName.

                 To specify a long name for the table, include NAME LongTableName. Long names can contain up
                 to 128 characters and can be used in place of short file names in the database.

        If you do not include the INTO clause, query results are displayed in a Browse window. You can also use
        the TO FILE clause to direct the query results to the printer or a file.

TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN
         Directs the query results to the printer or a file.

        ADDITIVE appends query output to the existing contents of the text file specified in TO FILE FileName.

        TO PRINTER directs query output to a printer. To display a dialog box before printing starts, use the
        PROMPT clause immediately after TO PRINTER. You can adjust printer settings in the dialog box. The
        printer settings that you can adjust depend on the currently installed printer driver.

        TO SCREEN directs query output to the main Visual FoxPro window or to an active user-defined window.

PREFERENCE PreferenceName
       Saves the attributes and options of the Browse window for later use, if query results are sent to a Browse
       window. For more information about how PREFERENCE functions, see the Remarks section.
NOCONSOLE
       Prevents display of query results sent to a file, the printer, or the main Visual FoxPro window.
PLAIN
       Prevents column headings from appearing in the query output that is displayed. You can use PLAIN
       whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored.
NOWAIT
       Continues program execution after the Browse window is opened and query results are directed to it. The
       program does not wait for the Browse window to close but continues execution on the program line


                                                         3
       immediately following the SELECT statement. For an explanation of how you can use NOWAIT, see the
       Remarks section.
WHERE JoinCondition
       Specifies that Visual FoxPro include only records in the query results that meet specified criteria.
       JoinCondition specifies fields that link the tables in the FROM clause. For more information about
       specifying join conditions, see the Remarks section.

        WHERE supports the ESCAPE operator for JoinCondition, making it possible for you to perform meaningful
        queries on data containing the SELECT SQL command percent (%) and underscore (_) wildcard characters.
        ESCAPE allows you to specify that a SELECT SQL command wildcard character be treated as a literal
        character. In the ESCAPE clause, you specify a character which, when placed immediately before the
        wildcard character, indicates that the wildcard character be treated as a literal character.

FilterCondition
         Specifies the criteria that records must meet to be included in the query results. You can include as many
         filter conditions as you like in a query and connect them with the AND or OR operator. To reverse the
         value of a logical expression, use the NOT operator. To check for an empty field, use the EMPTY( )
         function.

        The SELECT SQL command supports "<field> IS / IS NOT NULL" in the filter condition. To learn how to use
        the FilterCondition, see the Examples section.

GROUP BY GroupColumn [, GroupColumn ...]
       Groups rows in the query based on values in one or more columns. GroupColumn can be the name of a
       regular table field, a field that includes an SQL field function, or a numeric expression indicating the
       location of the column in the result table. The leftmost column number is 1.
HAVING FilterCondition
       Specifies a filter condition that groups must meet to be included in the query results. The HAVING clause
       can include as many filter conditions as you like, connected with the AND or OR operators. To reverse the
       value of a logical expression, use NOT. You can use local aliases and field functions in the HAVING clause.
       For more information on the field functions you can use, see the Remarks section. FilterCondition cannot
       contain a subquery.

        You can use a SELECT statement containing a HAVING clause without including a GROUP BY clause if
        SELECT does not contain aggregate functions. The HAVING clause without a GROUP BY clause acts like the
        WHERE clause. If the HAVING clause contains no field functions, use the WHERE clause for faster
        performance.

        The HAVING clause should appear before an INTO clause or a syntax error occurs.

[UNION [ALL] SELECTCommand]
        Combines the final result for one SELECT statement with the final result of another SELECT statement.
        UNION checks the combined results and eliminates duplicate rows by default. To combine multiple
        UNION clauses, use parentheses. You can use the UNION clause to simulate an outer join.

        ALL prevents UNION from eliminating duplicate rows from the combined results.

        When one of the columns has Memo or General type, unions of differing column types should not be
        allowed.




                                                         4
        In versions earlier than Visual FoxPro 8.0, you needed to perform explicit conversion when performing
        UNION operations between two fields of different types. Visual FoxPro now supports implicit data type
        conversion for data types that support it. For more details about implicit data type conversion and data
        type precedence, rules that UNION clauses follow, and other information, see Data Type Conversion and
        Precedence in the Remarks section.

ORDER BY Order_Item [ASC | DESC]
       Sorts the query results based on the data in one or more columns. Each Order_Item must correspond to a
       column in the query results and can be one of the following:

               A field in a FROM table that is also a Select_Item in the main SELECT clause (not a subquery).
               A numeric expression indicating the location of the column in the result table. The leftmost
                column is number 1.

        ASC specifies an ascending order for query results according to the order item or items and acts as the
        default for ORDER BY.

        DESC specifies a descending order for query results.




                                                        5

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:4/25/2012
language:English
pages:5