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...

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
PARAMETERS n_count
n_count = n_count + 1
RETURN .F.

cnt = 0
n_scope = 5
CoName = ‘France restauration’

SELECT company, incrmnt(@cnt) AS dummy FROM customers ;
               WHERE company > CoName AND cnt < n_scope + 1

GROUP BY and calculated fields

This is what you get when ORDER BY is followed by a calculated field :

SELECT YEAR(order_date) FROM orders GROUP BY YEAR(order_date)

SELECT YEAR(order_date) AS dummy FROM orders GROUP BY dummy
SELECT YEAR(date_order) FROM orders GROUP BY 1

Some operators are specific in Foxpro to the SELECT SQL statement and cannot be used anywhere else

SELECT order_date FROM orders WHERE order_date BETWEEN {01.01.1992} AND {30.06.1992}

SELECT order_date FROM orders ;
       WHERE order_date BETWEEN(order_date,01.01.1992,30.06.1992)

n1= SECONDS()
SELECT order_date FROM orders ;
       WHERE BETWEEN(order_date,{01.01.1992},{30.06.1992}) ;
       INTO ARRAY a1
n2= SECONDS()
SELECT order_date FROM orders ;
       WHERE order_date BETWEEN {01.01.1992} AND {30.06.1992} ;
       INTO ARRAY a2
n3= SECONDS()
? n2-n1
? n3-n2

SELECT-SQL in Multi-User applications

Input : Input tables are used to read data only, and should cause no problem in multi-user applications, unless one of the required tables has been opened exclusively by another user. If such a situation might occur in your application, you could check whether all tables are available before issuing the SELECT SQL command, and skip the command otherwise. Output to array : nothing to worry about here Output to cursor : In case the cursor name is not specified, Foxpro will determine a cursor name using SYS(2015), with no risk of duplicates. If you specify the cursor name, you are in the same conditions as with table output. Output to table : If the table is on a local drive, you have nothing to worry about. If the table is on a shared drive, make sure the table name is unique.

Finding high value orders for all customers

This should be an easy one :

SELECT orders.cust_id, ;
       MAX(order_amt) AS maxord, ;
       order_id from customer, orders ;
       WHERE customer.cust_id=orders.cust_id ;
       GROUP BY orders.cust_id

Can you trust this ? Let’s check, you never know...

SELECT cust_id, order_amt, order_id ;
               FROM orders ;
               ORDER BY cust_id, order_amt DESCENDING

A bit more work is involved to get the correct results for all customers, using a two step approach :

SELECT cust_id, order_amt, order_id ;
               FROM orders ;
               ORDER BY cust_id, order_amt DESCENDING ;
               INTO CURSOR temp

SELECT orders.cust_id, maxord, orders.order_id ;
                FROM temp, orders ;
                WHERE temp.cust_id=orders.cust_id AND ;
                 orders.order_amt=temp.maxord ;
                 ORDER BY orders.cust_id

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

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...

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.

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.