Re: Jet SQL and Virtual tables/subqueries qiestions

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]).

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.

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.

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.

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'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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)
  • 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: Jet SQL and Virtual tables/subqueries qiestions
    ... Perahps we've begin to uncover some bugs in the Access query ... If you PASTE SQL test in the following format into the SQL window ... around a field or table name within the VirtTbl1 subquery, ... I would think that producing a correct error message for that sort of query ...
    (microsoft.public.access.queries)
Loading