Re: Jet SQL and Virtual tables/subqueries qiestions

(comments inline below)

"David W. Fenton" wrote:

Mark Burns <MarkBurns@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:9E00BD15-A4CE-490F-BB6D-246839168749@xxxxxxxxxxxxx:

I've done a little more digging, and the results are both
interesting and puzzling.
Perahps we've begin to uncover some bugs in the Access query
builder/Jet SQL Parser somewhere here...?

If you PASTE SQL test in the following format into the SQL window
of Query Builder, it should be able to resolve the query
regardless of whether or not there are []s in the virtual table
subquery. SELECT [VirtTbl1].Yadda as Blah,...
FROM (SELECT Yadda, [Yadda2],... FROM YaddaYadda...(joins et. al.
here) WHERE Yadda.... ORDER BY Yadda...) As VirtTbl1
GROUP BY [VirtTbl1].Yadda,...
HAVING [VirtTbl1].Yadda,...
ORDER BY [VirtTbl1].Blah,...;

This also works:
SELECT [VirtTbl1].Yadda as Blah,...
FROM [SELECT Yadda, [Yadda2],... FROM YaddaYadda...(joins et. al.
here) WHERE Yadda.... ORDER BY Yadda...]. As VirtTbl1
GROUP BY [VirtTbl1].Yadda,...
HAVING [VirtTbl1].Yadda,...
ORDER BY [VirtTbl1].Blah,...;

I've never seen SQL like that work, because you have an
unnecessarily bracketed field inside the virtual table brackets
(i.e., [Yadda2]).

I tosssed that in deliberately to simulate what I'd seen specifically in my
query using the Jet4 reserved word [Temporary] as a fieldname.

However, if you change either or those examples above like this:
"... As [VirtTbl1] ..."
You will get weird (and compeltely unhelpful) syntax/identifier
errors out of the query builder and/or Jet SQL parser.

I wouldn't call it a bug -- it's just that you have to be careful
about brackets. Don't put in brackets when you don't need them.
Pretty simple, that.

Yet, doesn't the Access/Jet syntax documentation state that []s are always
OPTIONAL around table and fieldnames? (implying that []s either there or not
there - unless required to be there by other syntax rules like reserved words
or spaces - shoud both work the same)

Also, if you make a mistake, like leaving an unbalanced extra "]"
around a field or table name within the VirtTbl1 subquery, the
query editor gets the error message completely wrong - that the
parser's guess at highlighting the problem SQL string area selects
the outer query instead of just the inner subquery SQL is just one
clue that the parser (or something) has gotten hopelessly lost.

Yes, but that's an incredibly hard problem to solve. You can end up
with wrong error messages on unbalanced parentheses, too. It'
enormously difficult to resolve those kinds of things, particularly
because there's often more than on possible interpretation of what's
missing.

If the SQL parser were reentrant and sent subqueries to itself recursively,
I would think that producing a correct error message for that sort of query
syntax error would not be all that bad of a problem...at least you could
produce an error message with choices like "Either this is wrong (bad SQL
clause in 'outer' query), or that is wrong (unbalanced bracket around
identifier in subquery)" because the recursive/reentrant call would be able
to consider its subquery SQL text in isolation from the outer SQL query text
and therefore be able to produce at least a guess as to what was wrong given
its reduced evaluation context.

I haven't played around trying to use nested virtual table
subqueries again yet...but if the lack of proper error messages
for problems with only one level of subquery is any indication,
I'm probably better off not even going there.

I don't believe you can nest them, as it would violate the brackets
rule. Of course, if you've got it working with (), then you might
not encounter that issue. You *can*, however, have more than one
virtual table in your FROM list:

FROM []. AS tbl1 INNER JOIN []. AS tbl2 ON (tbl1.ID = tbl2.ID)

But I've never encountered a situation where I needed to do that.

Ah, well at least I now know THAT is possible/permissible.

It's really unfortunate though - since I'm buidling these SQL
query strings in code on-the-fly, I'd really rather be able to
nest virtual table subqueries a few levels deep right in a single
SQL string rather than suffer the overhead of creating, carying
around, and cleaning up 'n' extraneous querydef objects (depending
upon how many levels deep I need to go logically).

Are there SQL dialects that give you that flexibility? I've never
needed it, myself.

I don't know - the business need arose for me before the SQL... ;-)

I'd still prefer that option even if debugging query problems
might be harder that way.
I could easily argue that debugging problems in querydefs stacked
'n'-deep is no picnic either - especially since Access give us no
tools to examine the heirarchy of query calls/evaluation. Wouldn't
it be great if a Query Call Tree View of some sort were made
available? It could be most helpful in debugging query logic
issues - especially when you may have similar/identical field
names in multiple queries and you're not certain if you're pulling
the correct one from the correct subquery in a form or report...

I don't use as many saved queries as I used to, because once I
became experienced, I realized I didn't need them at all. And it's
not because I use lots of virtual tables (which I do), but because I
can write more efficient SQL for a particular task nowadays than I
could back when I saved everything as a standalone query.

I hear that - I guess I just need to get it through my head that I can't
always rely n the Access query editor to help me field-test my more complex
sub-query laden outer queries.

.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Multiple AND criteria
    ... A subquery is like a whole SELECT query inside another query. ... Then switch it to SQL view to ... FROM tblPart INNER JOIN tblPartAttrib ...
    (microsoft.public.access.queries)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: The complement of a select query
    ... That is not the SQL I suggested. ... the subquery. ... FROM Zip01 as TableA LEFT JOIN as ... Your query that does work will always be slow. ...
    (microsoft.public.access.queries)
Quantcast