Clause order in SQL select query

[.Len B]

Are there any rules that govern clause order? Does the
parser care?

Obviously SELECT must be first but does WHERE have to
precede ORDER BY?

This question arises because I have a situation where
the VBA code would be easier if the WHERE could be the
final clause. That way I could have an unchanging
SELECT, FROM, ORDER BY string and just tag the WHERE; on
the tail. My thinking then turned to whether there is
a mandated order for other clauses too.

Is there an article somewhere to explain it?

 

[John Spencer]

Yes there is a strict order of clauses.

You can build your SQL string in VBA using several variables and then
concatenate the strings together.

Dim strWhere as String
Dim strSQL as String
Dim strOrderBy as String

strSQL="SELECT * FROM TableA"
strOrderBy=" ORDER BY TableA.Field1"

'Build strWhere however you wish
'Obviously I've hardcoded it here
strWhere = " WHERE Field2 is Null and Field3 is Not Null"

'Test to see if you have built strWhere or simply left it
'blank. Actually if you have left it blank then you don't
'need the test.
If Len(StrWhere)>0 then
StrSQL = StrSQL & strWhere
End If

StrSQL = StrSQL & strOrderBy



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

While I don't see a technical reason to not allow a different sequencing in
the instructions (given Jet SQL contains only one executable SQL statement),
the parser does not like the WHERE clause occurring after the GROUP BY or
the ORDER BY clause.

Why could you not make:

strSELECT = " SELECT ..."
strFROM = " FROM ... "
strORDERBY = " ORDER BY ..."
strWHERE = " WHERE ... "

ie. define the clause in the order you want, as string, then

strSQL= strSELECT & strFROM & strWHERE & strORDERBY

ie, form the SQL statement in the sequence the parser expect the clauses.
(note that I took care of having a space in front of each statement, so the
concatenation would not 'agglutinate' a key word to what was at the end of
the previous clause:

SELECT f1 FROM ...
and not
SELECT f1FROM ... )



Vanderghast, Access MVP

 

[Daryl S]

Len -

Yes the order is important, and ORDER BY comes after WHERE. You can search
the internet for SQL Syntax.

 

[.Len B]

Thanks John, vanderghast and Daryl,
I guess I was hoping to be simplify my programming and code maintenance.
Rather than concatenate a bunch of string variables, some with module
scope
and some only local as I originally planned, and then keeping track of
which
concatenation was which, I thought "Wouldn't it be nice if ..."

Back in September John W Vinson helped me solve a different problem and
when I asked why/how it worked, he replied in part
'- since (in SQL processing) the FROM clause is parsed before the SELECT
clause'
which was so blindingly obvious in hindsight, and it got me to briefly
thinking about
how I would write a parser. So, when faced with the prospect of naming a
whole bunch
of variables closely similar but different enough to remember the
distinctions later,
my thoughts returned to "Does the parser really care?"; syntax rules are
one thing but
practice might be different. That's why I thought I'd ask those who know
how the parser
really operates before I tried something which might bite me later.

Wait. I just realized. Since this SQL string is intended to be a
parameter passed to a
function I have written to populate an ocx control, I could define the
static bits in the
function itself. That way I only have to pass the WHERE clause and
concatenate it
within the function. Now I'll only have a bunch of strWhere variables to
track.

Thanks again guys for your advice.
--
Len
______________________________________________________
remove nothing for valid email address.
| Len -
|
| Yes the order is important, and ORDER BY comes after WHERE. You can
search
| the internet for SQL Syntax.
| --
| Daryl S
|
|
| ".Len B" wrote:
|
| > Are there any rules that govern clause order? Does the
| > parser care?
| >
| > Obviously SELECT must be first but does WHERE have to
| > precede ORDER BY?
| >
| > This question arises because I have a situation where
| > the VBA code would be easier if the WHERE could be the
| > final clause. That way I could have an unchanging
| > SELECT, FROM, ORDER BY string and just tag the WHERE; on
| > the tail. My thinking then turned to whether there is
| > a mandated order for other clauses too.
| >
| > Is there an article somewhere to explain it?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
| > .
| >