If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Clause order in SQL select query
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. |
#2
|
|||
|
|||
Clause order in SQL select query
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 ..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? |
#3
|
|||
|
|||
Clause order in SQL select query
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 ".Len B" wrote in message ... 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. |
#4
|
|||
|
|||
Clause order in SQL select query
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. . |
#5
|
|||
|
|||
Clause order in SQL select query
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. "Daryl S" wrote in message ... | 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. | | | . | |
Thread Tools | |
Display Modes | |
|
|