Jet SQL and Virtual tables/subqueries qiestions

"'69 Camaro" AM
wrote in :

(It's a standard SQL rule, not just Jet SQL.) Using a
subquery in the FROM clause is an inline view. Inline views don't
exist outside of the query that contains them. In other words,
the outer query can't reference anything inside the FROM clause's
subquery that doesn't use the subquery's alias, MyYadda, namely a
selected column within MyYadda, such as MyYadda.ID (not
YaddaYadda.ID, which is the qualified column name, because the
actual table's name is YaddaYadda in your example).

Hmm. What about correlated subqueries?

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

Mark Burns wrote in
:

What I was really hoping to do was something along these lines
(borrowing your example):
SELECT CS.CID, CS.State, CS.TotSales, [H].TaxRate
FROM tblCustSales AS CS INNER JOIN
(SELECT State, MAX(TotSales) AS Highest, nz([StTax].[TaxRate], 0)
as TaxRate
FROM tblCustSales RIGHT JOIN (SELECT State, SalesTax, TaxRate,
LocalRatesApply FROM tblSalesTaxInfo WHERE LocalRatesApply = false
and SalesTax = True) As StTax ON [CS].State = [StTax].State
GROUP BY [CS].State
ORDER BY [CS].State) AS H ON (CS.TotSales = H.Highest) AND
(CS.State =
H.State);

Why are you putting in all those completely unnecessary brackets?
There's no need for brackets around a table alias one character
wide, nor around any of the table or field names that have no spaces
in them.

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

"'69 Camaro" AM
wrote in :

Hi, Mark.

The example I've been using today actually had multiple [] pairs
within the
subquery (even around the [Temporary] reserved word), and it sure
seemed to
WORK in my Access version...?

Ah, yes. It works . . . until it doesn't. ;-) Whenever Jet
"helps" you, that's when things may not work any more -- even if
they've worked perfectly for years.

Well, for what it's worth, if you're writing the SQL on the fly in
code and executing it there, you very often can get away without the
Jet-edited syntax. I do that all the time with WHERE clauses,
especially those with OR's in them, which the SQL "beautifier"
always seems to make inordinately complicated.

Dunno if it would work in this case.

The rules are different for saved queries, though, as you say.

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

(reply comments inline below)

"'69 Camaro" wrote:

Hi, Mark.

The example I've been using today actually had multiple [] pairs within
the
subquery (even around the [Temporary] reserved word), and it sure seemed
to
WORK in my Access version...?

Ah, yes. It works . . . until it doesn't. ;-) Whenever Jet "helps" you,
that's when things may not work any more -- even if they've worked perfectly
for years.

grinds teeth Yes...I've kinda noticed that particular proclivity with
other Access queries before...

....Ever have fun with a Crosstab sub-query suddenly blowing up an
n-levels-deep outer query because there was suddenly bad data that couldn't
make the transition to a fieldname? (toss in some query parameters for good
measure, and you have the makings of a very long day on your
hands...especially with a boss yelling about needing that report for the
auditors who are here NOW...)

However, repeated trips through the Query Editor's "graphical" side did
eventually lead to a [].'d syntax that would NOT work until I reaplaced
the
[].s with ()s

Jet often rewrites the SQL when the query is put into Design View. It's
best not to use the Design View after the query with subqueries is initially
created. Just use the SQL View pane.

Ok, perhaps I've missed something very basic somewhere, but how do you
control that? Every time I click on the "Design view" button for a new query,
I get the designer view, and not the SQL view (that's always another click
away...).
SOMETIMES, I do later get it to default to the SQL view, (I think it's when
I save the query from that view last). but I've never quite grokked the rules
on that (I've always had it go straight the SQL view for non-designer-able
queries - like UNIONs).

What I was really hoping to do was something along these lines (borrowing
your example):
SELECT CS.CID, CS.State, CS.TotSales, [H].TaxRate
FROM tblCustSales AS CS INNER JOIN
(SELECT State, MAX(TotSales) AS Highest, nz([StTax].[TaxRate], 0) as
TaxRate
FROM tblCustSales RIGHT JOIN (SELECT State, SalesTax, TaxRate,
LocalRatesApply FROM tblSalesTaxInfo WHERE LocalRatesApply = false and
SalesTax = True) As StTax ON [CS].State = [StTax].State
GROUP BY [CS].State
ORDER BY [CS].State) AS H ON (CS.TotSales = H.Highest) AND (CS.State =
H.State);

...or something along those lines, anyway.

You're using unnecessary brackets in many of the identifiers. Only use
alphanumerics and the underscore character for identifiers. Create a new
query and paste your present query into the SQL View pane. Remove all
brackets and use parentheses around the subqueries. Tweak the query until
it shows the data the way you want it to, then save it. Eventually, Jet
will replace the parentheses around the subqueries with the brackets and
trailing period. Those should be the _only_ brackets you ever allow in any
Jet query. Ever.

....unless you have a field named "Temporary" in your structure... yeah,
yeah, I know I saw it on the JET reserved words table, but it was already
there...with other stuff already pointing to/using it...shrug

"David W. Fenton" wrote:

"'69 Camaro" AM
wrote in :

You may be interested in Jet's SHOWPLAN. Please see the following
Web page for a link to the article, "Use Microsoft Jet's ShowPlan
to write more efficient queries," currently article #25:

http://www.Access.QBuilt.com/html/articles.html

But SHOWPLAN never implemented output of the optimization of any
form of subquery -- it says that right in the SHOWPLAN output every
time you run it.

Exactly.
So, how does one _*easily*_ debug a report problem when there are multiple
levels of subqueries in different subtrees, and there may even be field names
in common across those queries or trees?
By my experience, one does it by first taking the word easy out of the
question crumbling it up and tossing it in the can...then one just digs in
until an answer is forthcoming.
There must be a way to put the "Easy" back into that sort of effort.

(comments inline below)

"David W. Fenton" wrote:

Mark Burns wrote in
:

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.

Hi, David.

What about correlated subqueries?

Any correlation with the inline view would have to use the alias for the
inline view, as well as the column name used within the inline view, unless
the column name was aliased, in which case the column alias must be used.
For example:

.. . . FROM (SELECT City, State, (Population / 1000) AS Pop FROM
tblPopulations WHERE State IN ('CA', 'OR', 'WA', 'NV', 'AZ') AS West . . .

To correlate with the "West" inline view, West.City, West.State, and
West.Pop can be used, but not West.Population (since Population isn't a
specific column named in the inline view, despite the fact that it's used
within the definition of a calculated column), nor tblPopulations,
tblPopulations.City, tblPopulations.State, and tblPopulations.Population,
even though those last four exist in the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

Mark Burns wrote in
:

Ok, perhaps I've missed something very basic somewhere, but how do
you control that? Every time I click on the "Design view" button
for a new query, I get the designer view, and not the SQL view
(that's always another click away...).
SOMETIMES, I do later get it to default to the SQL view, (I think
it's when I save the query from that view last). but I've never
quite grokked the rules on that (I've always had it go straight
the SQL view for non-designer-able queries - like UNIONs).

The last view that it was saved in seems to me to be the one it will
open in.

It will always open in SQL view for queries that can't be displayed
in the query designer (UNION, non-equi joins, etc.).

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

Mark Burns wrote in
:

"David W. Fenton" wrote:

Mark Burns wrote in
:

[]

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)

But this is a *special case*, because brackets have a particular
meaning when you're using virtual tables. Thus, the "optional" part
of it changes to "don't use brackets unless you *must*".

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 can't see how the query interpret could possible do anything with
sub-queries if it can't discern the top-level of structure of the
entire SQL string.

But, be my guest in writing a QBE grid that does what you propose --
it's far from a simple problem.

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