Re: Jet SQL and Virtual tables/subqueries qiestions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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-Database-Programmers-Professional-Editions/dp/1572313420/ref=sr_1_2/102-4155424-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.


.

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: SQL Query between 2 dates
    ... When you run this as a query, JET calls the Expression Service which figures out the values and substitutes them, so the query runs. ... When you include literal date values in a string, they must be delimited with #, and they should be formatted in the native JET format The Formatfunction call performs this operation. ... Dim rs AS DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Rediculous query run tim
    ... While Jet optimizer performs well in some situation, ... another db engine, neither between version, of the same engine, since the ... to a field turns it into a computed expression) LAST. ... than it would be by creating it in a new field in an intermediate query. ...
    (microsoft.public.access.queries)