 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: 16 Aug 1998
The following is an unedited post from Jerry Sykes:
Here's a primer on SQL Select theory in Approach for Non-SQL database sources such as dBASE IV. SQL select is comprised of 4 sections
1. SELECT <fields>
2. FROM <table>
3. WHERE <Conditions are meet> optional
4. ORDER BY <Sort> optional
As with any script string, enclosed the entire SQL statement in double quotes.
q.SQL = " <SQLStatement>"
Before beginning with the Select part, lets look at the "FROM" statement
"FROM" has two parts; FROM ""table full path name"" aliasname
Alias is the important concept here. It is the short reference name you are calling this datasource. Here is a FROM clause from of one of mine;
FROM ""F:\Programs\RawMaterials\BoxTrans.dbf"" BoxTrans
then the variable substitution example from Help examples.
FROM """"+ Q.Tablename+ """"+ tname + "
Notice my database table full path name has is enclosed by double quotes, yet my alias has none. Approach will interpet this when it translates the full string to;
FROM "F:\Programs\RawMaterials\BoxTrans.dbf" BoxTrans
stripping the outer set of quotes. In the second example. Q.Tablename is the Approach Query class property that is meant to hold the full tablename path.
Assuming Q.tablename is set previously in the script and is a variable a third set of double quotes is necessary for runtime interpetation. In the same manner, the Alias variable must have one set of double quotes. The alias does not necessaraly have to be the database name. Any unique character combination except script keywords is ok. Such as;
FROM "F:\Programs\RawMaterials\BoxTrans.dbf" B
Just be sure you use the declared alias in all SQL alias.fieldname references.
Summing up the WHERE clause, if you want to supply the path in a SQL
statement, then
"" mappeddrive:\..\databasename "" alias
else
""" + scriptvariable + """ " + aliasvariable + "
where scriptvaiable is the full path and name and aliasvariable is what you are naming this datasource within this script.
After you understand the alias naming convention, the alias is used in the other three sections.
If all fields are desired then
SELECT * FROM
where * is a all fields wildcard
else
SELECT alias.fieldname1, alias.fieldname2 ... alias.fieldnameN FROM
the last field selected should have no comma after it, just a space then FROM.
An example;
SELECT BoxTrans.ItemNum, BoxTrans.JobNum, BoxTrans.TransDate,
BoxTrans.FoldWeekOf FROM
If you use a variable alias type, as in this example,
FROM """"+ Q.Tablename+ """"+ tname + " ,
then "+ tname + ".fieldname syntax is used. My preference is to avoid the "+
tname + " type of alias. It requires much more care in typing. If you are
using long fieldnames in your tables, it may be necessary to use double
quotes.
SELECT BoxTrans.""Item Number", BoxTrans.""Job Number"",
BoxTrans.""Transaction Date", BoxTrans.""Fold Week Of"" FROM
Continuing, the choice of syntax in your WHERE clause, follows the exact
syntax rules for the alias.fieldname part, but a slight variation is required for the criteria. From the Script Help...Approach Objects...SQL...Example,
WHERE ("& TName & ".""Room Name/Number"" = '" & DeletedRooms(i) & "')"
and one of mine
WHERE (BoxTrans.TransDate = '""'and BoxTrans.FoldWeekOf = '"+MySQL+"')
In both of these examples, the right hand conditional phrase takes a slightly different syntax. Instead of double quotes, you use
alias.fieldname = single quote double quote +VariableCondition+ double quote single quote.
Notice an & and a + are interchangable when building string combinations.
The best method of troubleshooting a bad syntax SQL statement is to put a
breakpoint in the script after the SQL. Drill to the SQL statement of the
query object in the variable pane. Click on the SQL statement in the Variables pane. The runtime/substitution construction of the SQL statement will be visible on the bottom line of the variables pane. The following complete script,
qu.SQL = "SELECT * FROM ""F:\Programs\RawMaterials\BoxTrans.dbf"" BoxTrans
WHERE (BoxTrans.TransDate = '""'and BoxTrans.FoldWeekOf = '"+MySQL+"') ORDER
BY BoxTrans.ItemNum,BoxTrans.JobNum"
shows this string in the variables pane at runtime, when viewing the query object at a breakpoint script halt.
SELECT* FROM "F:\Programs\RawMaterials\BoxTrans.dbf" BoxTrans WHERE
(BoxTrans.TransDate = '"'and BoxTrans.FoldWeekOf = '4/6/98') ORDER BY
BoxTrans.ItemNum,BoxTrans.JobNum