Jet SQL and Virtual tables/subqueries qiestions

[Mark Burns]

Is there some documentation somewhere I can reference for more information on
2 things:
1) The Jet Expression Evaluator (what functions & stuff are actually
available within Jet Expressions?)
2) The proper use of sub-queries / virtual tables within JET SQL?

Regarding #2, I tried to build a somewhat complex query, only to be turned
back by the Jet evaluator.
I could easily do something like this:

SELECT blah... FROM (SELECT Yadda...from yadda yadda WHERE yadda...) WHERE
.... ORDER BY ...;

which worked great.
However, whan I tried this:

SELECT Blah... FROM BlahBlah INNER JOIN (SELECT Yadda... from YaddaYadda
WHERE ...) As MyYadda ON BlahBlah.whatever = myYadda.whatever WHERE...;

The JET 4.x Evaluator failed to recognize the table name YaddaYadda (using
[]s didn't help or hurt)

However breaking the (SELECT Yadda... FROM YaddaYadda WHERE...) part out to
a separate query worked fine. Also, if I just replace the entire (Select
Yadda... FROM YaddaYadda...) with the same Tablename YaddaYadda in the
"outer" query, it would again evaluate and run.

So, what are the rules for what JET SQL subquery syntaxes will work and what
won't here?

 

[David W. Fenton]

So, what are the rules for what JET SQL subquery syntaxes will
work and what won't here?

I think there are differences in what is supported in different
versions of Access/Jet. I always use []. AS (the . is required), but
I've seen people post the more standard () AS, so it may be that
later versions of Jet support it. Also, with A2K3 you have the
option of using SQL 92, which might make the () AS version work.

I certainly use []. AS a lot, but it does mean you can't have any
brackets inside your virtual table (which means no spaces or funny
characters in fieldnames, which I wouldn't do, anyway, or
parameters, which I don't generally use in any case).

 

[Mark Burns]

David, et. al.

It took me some extra digging into the query and various field/table names,
and it came down to this: "...but it does mean you can't have any brackets
inside your virtual table..."
Inside my virtual table was field named "Temporary", which, of course, made
the Jet SQL Evaluator go insane (since it is one of those field names that
must be []'d).
....not that the "What table name is this?" type error messages I was getting
due to this error made any sense either... (Grrrrowl! snort...paws the ground
glaring around for someone to gore with my horns)

thanks for the help.

So, what are the rules for what JET SQL subquery syntaxes will
work and what won't here?

I think there are differences in what is supported in different
versions of Access/Jet. I always use []. AS (the . is required), but
I've seen people post the more standard () AS, so it may be that
later versions of Jet support it. Also, with A2K3 you have the
option of using SQL 92, which might make the () AS version work.

I certainly use []. AS a lot, but it does mean you can't have any
brackets inside your virtual table (which means no spaces or funny
characters in fieldnames, which I wouldn't do, anyway, or
parameters, which I don't generally use in any case).

 

[Mark Burns]

David,

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,...;

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.

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.

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

So, what are the rules for what JET SQL subquery syntaxes will
work and what won't here?

I think there are differences in what is supported in different
versions of Access/Jet. I always use []. AS (the . is required), but
I've seen people post the more standard () AS, so it may be that
later versions of Jet support it. Also, with A2K3 you have the
option of using SQL 92, which might make the () AS version work.

I certainly use []. AS a lot, but it does mean you can't have any
brackets inside your virtual table (which means no spaces or funny
characters in fieldnames, which I wouldn't do, anyway, or
parameters, which I don't generally use in any case).

 

['69 Camaro]

Hi, Mark.

Is there some documentation somewhere I can reference for more information
on
2 things:
1) The Jet Expression Evaluator (what functions & stuff are actually
available within Jet Expressions?)

The only place I can think of where one _might_ find such documentation on
the Jet Expression Service is the Microsoft Jet Database Engine Programmer's
Guide. No guarantees, though.

http://www.amazon.com/Microsoft-Dat...1604134?ie=UTF8&s=books&qid=1187814950&sr=1-2

2) The proper use of sub-queries / virtual tables within JET SQL?

I think I know of a book on that. Let me check, and I'll get back to you on
this on Friday.

However, whan I tried this:

SELECT Blah... FROM BlahBlah INNER JOIN (SELECT Yadda... from YaddaYadda
WHERE ...) As MyYadda ON BlahBlah.whatever = myYadda.whatever WHERE...;

The JET 4.x Evaluator failed to recognize the table name YaddaYadda (using
[]s didn't help or hurt)

This is by design. (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).

So, what are the rules for what JET SQL subquery syntaxes will work and
what
won't here?

1.) Never use Reserved words or illegal characters when naming objects,
i.e., tables, queries, columns, procedures, et cetera. Only use
alphanumerics and the underscore character for these names. People will
tell you that you can always "fix" these mistakes by placing the names
within brackets within the query. They're big fat liars. It _doesn't_
always work. Jet uses an internal syntax that replaces parentheses with
brackets and sometimes places brackets around a grouping where it thinks
parentheses should have been placed. Therefore, as soon as any closing
bracket is encountered after an opening bracket when parsing the SQL
statement, that signifies the end of that syntax grouping, even if that
makes the statement incomplete. If it's incomplete, Jet chokes when it
tries to execute the statement, and the developer wastes unnecessary time
troubleshooting the cause. Usually a _lot_ of unnecessary time.

Please see the following Web pages for Reserved words to avoid:

http://support.microsoft.com/default.aspx?scid=286335

http://support.microsoft.com/default.aspx?id=321266

2.) When initially creating a query that contains a subquery, one may use
parentheses around the subquery. However, after the query is saved and
compiled, subsequent editing of that query often requires (depending upon
the version of Jet) that the parentheses be replaced with Jet's internal
syntax, which is brackets and a trailing period after the closing bracket.
For example:

SELECT CS.CID, CS.State, CS.TotSales
FROM tblCustSales AS CS INNER JOIN (SELECT State, MAX(TotSales) AS Highest
FROM tblCustSales
GROUP BY State
ORDER BY State) AS H ON (CS.TotSales = H.Highest) AND (CS.State =
H.State);

If this query needs to be edited, then first exchange the parentheses for
brackets and the trailing period, then make your edits:

SELECT CS.CID, CS.State, CS.TotSales
FROM tblCustSales AS CS INNER JOIN [SELECT State, MAX(TotSales) AS Highest
FROM tblCustSales
GROUP BY State
ORDER BY State]. AS H ON (CS.TotSales = H.Highest) AND (CS.State =
H.State);

If I changed the "TotSales" column name in the tblCustSales table to "Tot
Sales," with an illegal space character, the same query above would become:

SELECT CS.CID, CS.State, CS.[Tot Sales]
FROM tblCustSales AS CS INNER JOIN [SELECT State, MAX([Tot Sales]) AS
Highest
FROM tblCustSales
GROUP BY State
ORDER BY State]. AS H ON (CS.[Tot Sales] = H.Highest) AND (CS.State =
H.State);

This query doesn't compile because Jet reads [SELECT State, MAX([Tot Sales]
and expects it to be a complete SELECT statement, which it isn't. It's that
closing bracket on the [Tot Sales] column name that trips it up.

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/expert_contributors2.html for contact
info.

 

[Mark Burns]

Gunny,

Thanks so much for the reply!

I read with interest your instructions, especially about the ']'s within the
subquery, but it's curious...
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...?
MSAccess.exe 2003 Sp2 (11.6566.8132)
MSJet40.dll 4.00.8618.0 8/4/2004 1:56AM
MSJTES40dll 4.00.8618.0 8/4/2004 1:56AM

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 (but your explanation for Access/Jet placing []s internally may
provide the explanation for that occurance).

???

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.
....but after getting snapped at by Access by just going to the 1st
sub-level, I'm rethinking...

Hi, Mark.

Is there some documentation somewhere I can reference for more information
on
2 things:
1) The Jet Expression Evaluator (what functions & stuff are actually
available within Jet Expressions?)

The only place I can think of where one _might_ find such documentation on
the Jet Expression Service is the Microsoft Jet Database Engine Programmer's
Guide. No guarantees, though.

http://www.amazon.com/Microsoft-Dat...1604134?ie=UTF8&s=books&qid=1187814950&sr=1-2

2) The proper use of sub-queries / virtual tables within JET SQL?

I think I know of a book on that. Let me check, and I'll get back to you on
this on Friday.

However, whan I tried this:

SELECT Blah... FROM BlahBlah INNER JOIN (SELECT Yadda... from YaddaYadda
WHERE ...) As MyYadda ON BlahBlah.whatever = myYadda.whatever WHERE...;

The JET 4.x Evaluator failed to recognize the table name YaddaYadda (using
[]s didn't help or hurt)

This is by design. (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).

So, what are the rules for what JET SQL subquery syntaxes will work and
what
won't here?

1.) Never use Reserved words or illegal characters when naming objects,
i.e., tables, queries, columns, procedures, et cetera. Only use
alphanumerics and the underscore character for these names. People will
tell you that you can always "fix" these mistakes by placing the names
within brackets within the query. They're big fat liars. It _doesn't_
always work. Jet uses an internal syntax that replaces parentheses with
brackets and sometimes places brackets around a grouping where it thinks
parentheses should have been placed. Therefore, as soon as any closing
bracket is encountered after an opening bracket when parsing the SQL
statement, that signifies the end of that syntax grouping, even if that
makes the statement incomplete. If it's incomplete, Jet chokes when it
tries to execute the statement, and the developer wastes unnecessary time
troubleshooting the cause. Usually a _lot_ of unnecessary time.

Please see the following Web pages for Reserved words to avoid:

http://support.microsoft.com/default.aspx?scid=286335

http://support.microsoft.com/default.aspx?id=321266

2.) When initially creating a query that contains a subquery, one may use
parentheses around the subquery. However, after the query is saved and
compiled, subsequent editing of that query often requires (depending upon
the version of Jet) that the parentheses be replaced with Jet's internal
syntax, which is brackets and a trailing period after the closing bracket.
For example:

SELECT CS.CID, CS.State, CS.TotSales
FROM tblCustSales AS CS INNER JOIN (SELECT State, MAX(TotSales) AS Highest
FROM tblCustSales
GROUP BY State
ORDER BY State) AS H ON (CS.TotSales = H.Highest) AND (CS.State =
H.State);

If this query needs to be edited, then first exchange the parentheses for
brackets and the trailing period, then make your edits:

SELECT CS.CID, CS.State, CS.TotSales
FROM tblCustSales AS CS INNER JOIN [SELECT State, MAX(TotSales) AS Highest
FROM tblCustSales
GROUP BY State
ORDER BY State]. AS H ON (CS.TotSales = H.Highest) AND (CS.State =
H.State);

If I changed the "TotSales" column name in the tblCustSales table to "Tot
Sales," with an illegal space character, the same query above would become:

SELECT CS.CID, CS.State, CS.[Tot Sales]
FROM tblCustSales AS CS INNER JOIN [SELECT State, MAX([Tot Sales]) AS
Highest
FROM tblCustSales
GROUP BY State
ORDER BY State]. AS H ON (CS.[Tot Sales] = H.Highest) AND (CS.State =
H.State);

This query doesn't compile because Jet reads [SELECT State, MAX([Tot Sales]
and expects it to be a complete SELECT statement, which it isn't. It's that
closing bracket on the [Tot Sales] column name that trips it up.

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/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Mark.

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?

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

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/expert_contributors2.html for contact
info.

 

['69 Camaro]

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.

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.

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.

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/expert_contributors2.html for contact
info.

 

[David W. Fenton]

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]

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.

 

[David W. Fenton]

(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]

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]

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.

 

[Mark Burns]

(reply comments inline below)

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>

 

[Mark Burns]

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.

 

[Mark Burns]

(comments inline below)

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.

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

 

['69 Camaro]

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/expert_contributors2.html for contact
info.

 

[David W. Fenton]

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]

news:[email protected]:

[]

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.