The hidden face of SELECT SQL

Christian Desbourse, January 1, 2001

One of the most powerful command available with FoxPro’s language, SELECT-SQL allows you to extract from your databases the right data you need. For the beginner, FoxPro provides a built-in RBQE dialog which builds the SELECT-SQL statement and stores these in QPR files, which can be executed from w...

Most of these examples are applied to Testdata.dbc from Visual FoxPro Samples directory.

Array or Cursor or Table output?

Output to array for maximum speed when you don’t need permanent data for further processing and you are sure the size of the array will not use up all available memory. Output to table or cursor is limited only by the space available on disk. A cursor is always read only. You will need a table to edit the data resulting from the SELECT SQL statement after quitting the application or the work session.

How to get the top ten records?

This is an example where the SELECT-SQL is best divided in two statements, with an intermediate table. Applying the WHERE clause to the first statement would give you the first ten records of the Customer file, regardless of the unit_price value.

SELECT prod_name,unit_price FROM products ORDER BY 2 DESCENDING INTO TABLE tmp
SELECT * FROM tmp WHERE RECNO()<10

USE IN tmp
DELETE FILE tmp.dbf

RECCOUNT() does not match number of records

Even if you require output to a cursor, FoxPro may elect to use the original data table in another work area, under a different alias, as you would obtain with a statement like :

USE MyFile AGAIN IN 0 ALIAS MyAlias

SELECT * FROM products WHERE product_id=‘    14’ INTO CURSOR tmp
SELECT tmp
? _tally     &&  =1
? RECCOUNT() &&  =77
SELECT * FROM products WHERE prod_name = "Tofu" INTO CURSOR tmp2  
SELECT tmp2
? _tally     &&  =1
? RECCOUNT()  &&  =1

SELECT *,"" AS Dummy FROM products WHERE product_id='    14' INTO CURSOR tmp3
select tmp3
? _tally   && =1
? reccount() && =1
SELECT * FROM products WHERE product_id='    14' AND .T. INTO CURSOR tmp4
select tmp4
? _tally  && =1
? reccount()  && =1

When you join several tables in a query, and use the * instead of listing fields individually, you might believe some of the fields have disappeared from the query output. The "Variable not found " error message will be the symptom in most cases. Your fields are there, but what happens is that FoxPro renames fields that are present in several tables :

SELECT * FROM Customer, Orders ;
                WHERE Customer.Cust_id = Orders.Cust_id
? Cust_id   &&  Variable not found

SELECT *, Customer.Cust_Id AS CustId, Customer.PostalCode AS PostCode ;
                   FROM Customer, Orders ;
                   WHERE Customer.Cust_id = Orders.Cust_id
? CustId  &&  FRANS
? PostCode &&  10100

SELECT *, Customer.Cust_Id AS Cust_Id, Customer.PostalCode AS PostalCode ;
                   FROM Customer, Orders ;
                   WHERE Customer.Cust_id = Orders.Cust_id
? CustId  &&  Variable not found
? PostCode &&  Variable not found
? Cust_Id_c  && FRANS

SELECT SUM(order_amt) FROM Orders
? order_amt           && variable not found
? sum_order_amt   && 1641167.9900

SELECT COUNT(*) FROM Orders
? cnt   &&  1078
SELECT COUNT(*) FOM Customer
? cnt   &&  91

You can order output by any field of any of the tables included in the FROM clause of your SELECT-SQL statement., or by any of the columns of your query output. You can designate ordering fields by their name, or by their order in the fields list :

SELECT Cust_id, Order_amt FROM orders ORDER BY Cust_id

SELECT Cust_id, Order_amt FROM Orders ORDER BY 1

SELECT Cust_Id, Order_amt FROM Orders ORDER BY 1, 2 DESCENDING

The missing Outer Join

When joining two or more tables, the query will include the records that appear in all tables. For instance, the customers that have orders in the Orders table will be included in the results of the following query :

SELECT Customer.Cust_id FROM customer, orders WHERE customer.cust_id = orders.cust_id

This second query provides one record for each customer that has no orders in the orders file.

SELECT customer.cust_id FROM customer WHERE cust_id NOT IN ;
       (SELECT DISTINCT cust_id FROM orders)

SELECT customer.cust_id, SUM(order_amt) AS tot_amt ;
       FROM customer,orders ;
       WHERE customer.cust_id=orders.cust_id ;
       GROUP BY 1 ;
       UNION ;
       SELECT customer.cust_id, NTOM(00000000) AS tot_amt FROM customer ;
       WHERE cust_id NOT IN (SELECT DISTINCT cust_id FROM orders)

How to display a timetable in a browse window using SQL & Genxtab

Christian Desbourse, January 1, 2001

Our sample data consists of two tables, an employee table and a table defining the timetable we want to display. The timetable is defined with the starting time (date, hour and minutes) and the employee in charge (hempl_id) of a number of time slot allocations of 30 minutes each. The data usually co...

The hidden face of SELECT SQL Part 2

Christian Desbourse, January 1, 2001

If you include two tables in a query without specifying a join condition, every record in the first table will be joined with every record in the second table, with huge results as a consequence.

The hidden face of SELECT SQL Part 3

Christian Desbourse, January 1, 2001

Simulate SEEK, DO..WHILE and SCOPE Do you want to retrieve the five companies following ‘France restauration’ in Customers ? Oh I know, xBase junkies can do that with a SEEK, and a DO WHILE and ....but aren’t we talking SQL ? We will use a UDF to control the scope : FUNCTION incremnt PAR...

Three functions to improve application security

Christian Desbourse, January 1, 2001

You have set up an extensive security system to control access to your application through user log-in procedures, and password entry. But some people tend to be security reluctant, they write their password on the wall, or worse, everybody is using the same "Hello" password.