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