SELECT - SQL Command - WITH Clause

The WITH clause allows you to apply a single command to existing SELECT statements without modifying those statements.

For the complete syntax, see SELECT - SQL Command.

[WITH (BUFFERING = lExpr) ]

Parameters

• lExpr
  Logical data type. The following table lists the values for lExpr.

  lExpr	Setting
  True (.T.)	Data in the SQL-SELECT statement is based on buffered data, if available.
  False (.F.)	(Default) Data in the SQL-SELECT statement is based on data written to disk only.

Remarks

The following code shows a summary of the main clauses of the SELECT - SQL Command:

SELECT Select_List
   FROM Table_List
...[WITH (BUFFERING = lExpr)]
   [WHERE Conditions]
   [GROUP BY Column_List]
   [UNION Clause]
   [HAVING Conditions]
   [ORDER BY Column_List]
   [INTO Clause | TO Clause ] 
   [Additional_Display_Options]

For more information about a particular clause of the SQL SELECT command, see the following topics:

• SELECT Clause

• FROM Clause

• WHERE Clause

• GROUP BY Clause

• HAVING Clause

• UNION Clause

• ORDER BY Clause

• INTO or TO Clause

• Additional Display Options

If you set BUFFERING to True (.T.), you can query data from a local buffered cursor, which may include records that have been updated but not committed. Otherwise, your results include only records committed to disk.

If the cursor uses row buffering, then current record is committed before the statement is executed.

If the cursor uses table buffering, then the modified current record is saved into the buffer.

The WITH clause to specify BUFFERING is supported on local Visual FoxPro data only and not on data from back-end databases.

If you set BUFFERING to False (.F.), then your SELECT statement pulls data from cursor on disk (which may be different to what is in buffer). If you do not include the BUFFERING statement, then your results are based on the setting for SET SQLBUFFERING Command. By default, your queries pull data from disk. A SELECT statement WITH BUFFERING set to True (.T.) overrides the SET SQLBUFFERING setting.

The WITH BUFFERING option is effective in the following cases:

• The work area does not have an alias specified to support the cursor referenced in the statement.

• The cursor is not buffered.

• The cursor acts as the target for update or delete operations.

Example 1

The following example shows a simple statement referencing a single table. You need to have a work area that has the alias "Customers" in order for the WITH clause to be effective.

SELECT * FROM customers WITH (BUFFERING=.T.)

Example 2

The following example shows that each table referenced in the FROM clause needs a WITH BUFFERING clause.

SET MULTILOCKS ON
CREATE TABLE Table1 (Field1 N(10), Field2 N(10))
CREATE TABLE Table2 (Field3 N(10), Field4 N(10))

CURSORSETPROP("Buffering", 5, "Table1")
CURSORSETPROP("Buffering", 5, "Table2")

INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table2 VALUES (0, 100)

SELECT Table1.*, Table2.* ;
   FROM FORCE Table1 ;
      JOIN Table2 ;
      ON Field1>=Field3 AND Field2>0 AND Field4<100

REPLACE Field4 WITH 99 IN Table2

SELECT Table1.*, Table2.* ;
   FROM FORCE Table1 ;
      JOIN Table2 ;
      ON Field1>=Field3 AND Field2>0 AND Field4<100

SELECT Table1.*, Table2.* ;
   FROM FORCE Table1 WITH (BUFFERING=.T.) ;
      JOIN Table2 WITH (BUFFERING=.T.) ;
      ON Field1>=Field3 AND Field2>0 AND Field4<100

See Also

Reference

CREATE QUERY Command
MODIFY QUERY Command

Concepts

Buffering Data

Other Resources

Working with Queries
Working with Views (Visual FoxPro)
Query and View Designers