<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Definition Language > Local SQL VIEWs

Description

Local SQL does not support VIEWs is the traditional sense. It includes no CREATE VIEW statement. However, local SQL does provide an emulation that includes a lot of the functionality found in the traditional VIEW. This is the use of SQL (and QBE) queries that have been saved to a file.

An SQL statement can be saved to a text file and the file referenced in a local SQL statement. This process does not use an already existing result set, but executes the saved SQL statement to produce a result set on-the-fly that is used in the local SQL statement. All local SQL syntax rules apply to saved SQL statements.

To use an SQL statement as a VIEW, save it to a simple text file (with a .SQL extension), such as by using the Windows applet NotePad. Then, in a local SQL statement, mention the file by name where you would otherwise have named a base table. Enclose the file name in quotation marks. For example, assume the SQL statement below saved to the text file CA_CUST.SQL:

SELECT CustNo, Company

FROM "Customer.db"

WHERE (State = "CA")

Another local SQL statement can use the result set produced by this saved query by naming the .SQL file in the FROM clause of a SELECT statement:

SELECT *

FROM "CA_Cust.sql"

WHERE (SUBSTRING(Company FROM 1 FOR 1) <= "M")

Use of saved SQL queries is not limited to somple SELECT statements. They can also be used in place of tables in joins and can even be joined to the result sets of other saved queries. Using the previously mentioned CA_CUST.SQL file:

SELECT C.*, O.*

FROM "CA_Cust.sql" C

 JOIN "Orders.db" O

   ON (C.CustNo = O.CustNo)

Similarly, Paradox-style QBE (Query By Example) queries saved to .QBE files can be used in lieu of a base table in a local SQL statement. The QBE query below performs the same data retrieval as the earlier CA_CUST.SQL saved SQL file. It is saved with the name CA_CUST.QBE.

Query

ANSWER: :PRIV:ANSWER.DB

 

customer.db | CustNo | Company | State |

           | Check  | Check   | CA    |

 

EndQuery

As was the case with the saved SQL query, this saved QBE query is used in a local SQL statement in the exact same way.

SELECT C.*, O.*

FROM "CA_Cust.qbe" C

 JOIN "Orders.db" O

   ON (C.CustNo = O.CustNo)

Using saved QBE queries in local SQL statements affords you the ability to use functionality available just in QBE – but not local SQL – in local SQL statements. One example of such functionality is case-insensitive filtering on memo column contents.

Create QBE query files using Paradox, the Database Desktop utility, or a simple text editor.