USING FOXPRO'S SQL SELECT COMMAND

15.16.1 Overview and Initial Setup

Foxpro's SQL command, SELECT, is an extremely powerful command that can be entered from Foxpro's Command Window and generate just about any report you might desire to a browse window, screen or printer. The following examples illustrate just how powerful this command is.

Enter the following command at the DOS prompt to make a Foxpro directory and then copy pc/MRP's data base files into that directory:

MD\FOXMRP [Rtn]
COPY C:\PCMRP\ *.DBF C:\FOXMRP [Rtn]

Initialize Foxpro, and select the FOXMRP directory as the default directory by entering the following commands into Foxpro's Command Window

SET DEFAULT TO C:\FOXMRP [Rtn]

You are now ready to run Foxpro's SQL Select command

To print out all shortage parts for a sales/work order you would enter the following command into Foxpro's Command Window

SELECT PARTNO,DESCRIPT,QTYISSUED-QTYREQ FROM STOCKTRA TO PRINT

The above SELECT command will print out the partnumber field, the description field and shortage number from the stock room database file for each part number

If you wish to display the shortages on the screen instead of printing them, enter:

SELECT PARTNO,DESCRIPT,QTYISSUED-QTYREQ FROM STOCKTRA TO SCREEN

If you wish to display a browse window with elevators remove TO SCREEN from the command and enter:

SELECT PARTNO,DESCRIPT,QTYISSUED-QTYREQ FROM STOCKTRA

If you wanted to display shortages only you would enter:

SELECT PARTNO,DESCRIPT,FROM STOCKTRA WHERE (QTYISSUED -QTYREQ) < 0 TO SCREEN

If you wish to give a column a name such as shortage you would enter:

SELECT PARTNO,DESCRIPT,QTYISSUED-QTYREQ AS SHORTAGE FROM STOCKTRA WHERE (QTYISSUED-QTYREQ) < 0 TO SCREEN

If you wish to total a column a second select statement must be added

SELECT PARTNO,DESCRIPT,COST * QTYISSUED AS EXTNCOST FROM STOCKTRA TO SCREEN
SELECT SPACE(37),SUM(COST * QTYISSUED) AS TOTALCOST FROM STOCKTRA TO SCREEN

You can save yourself the trouble of retyping both lines perfectly over and over again by creating a program file called COST.PRG and save it. The next time you need to analyze the data enter DO COST in Foxpro's command window.

To create the file enter:

MODIFY COMMAND COST.PRG [Rtn]

You will get a window to enter your COST.PRG on Enter the following lines into the COST.PRG window

CLEAR
SET DEFAULT TO C:\FOXMRP
SELECT PARTNO,DESCRIPT,COST * QTYISSUED AS EXTNCOST FROM STOCKTRA TO SCREEN
SELECT SPACE(37),SUM(COST * QTYISSUED) AS TOTALCOST FROM STOCKTRA TO SCREEN

To save this file, click on close, click on save.

Now whenever you want this report to run simply enter DO COST in Foxpro's command window