SQL wrong rewriting

[alfadj]

I have a problem with Access. Since I'm used (and prefer) to enter
queries in SQL mode, I'm facing the following:
Let's say I enter this query

SELECT A.* FROM (SELECT * FROM Orders WHERE OrderNo > 100) AS A

When I save it and re-open, I find it is converted to

SELECT A.* FROM [SELECT * FROM Orders WHERE OrderNo > 100]. AS A

and I get a syntax error.

Does anyone has faced this problem yet? Any suggestion?

Thanks

alfadj

 

[pietlinden]

I have a problem with Access. Since I'm used (and prefer) to enter
queries in SQL mode, I'm facing the following:
Let's say I enter this query

SELECT A.* FROM (SELECT * FROM Orders WHERE OrderNo > 100) AS A

When I save it and re-open, I find it is converted to

SELECT A.* FROM [SELECT * FROM Orders WHERE OrderNo > 100]. AS A

and I get a syntax error.

Does anyone has faced this problem yet? Any suggestion?

Thanks

alfadj

sounds like the QBE stuff is fixing your SQL. Why are you even
writing your select like that? (or is this a simplification that you
posted? what you're getting from the DB engine or whatever is the
"optimized" version...) Why not just use

SELECT A*
FROM Orders
WHERE OrderNo>100
AND...

which would be the logical equivalent of what you're doing and the
query engine or whatever won't get all worked up?

 

[alfadj]

I have a problem with Access. Since I'm used (and prefer) to enter
queries in SQL mode, I'm facing the following:
Let's say I enter this query

SELECT A.* FROM (SELECT * FROM Orders WHERE OrderNo > 100) AS A

When I save it and re-open, I find it is converted to

SELECT A.* FROM [SELECT * FROM Orders WHERE OrderNo > 100]. AS A

and I get a syntax error.

Does anyone has faced this problem yet? Any suggestion?

alfadj

sounds like the QBE stuff is fixing your SQL. Why are you even
writing your select like that? (or is this a simplification that you
posted? what you're getting from the DB engine or whatever is the
"optimized" version...) Why not just use

SELECT A*
FROM Orders
WHERE OrderNo>100
AND...

which would be the logical equivalent of what you're doing and the
query engine or whatever won't get all worked up?

Actually I've simplified the statement. Since I don't like to use many
intermediate queries stored in the DB, I prefer to use a single
statement like this:

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN (SELECT id, bar FROM
table2 WHERE cost>100) AS B ON A.id = B.id

After save and re-open I get

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN [SELECT id, bar FROM
table2 WHERE cost>100]. AS B ON A.id = B.id

(My statement is more complicated, this was just an example)

By the way, thanks

 

[Todos Menos [MSFT]]

dude Access is for kids

use SQL Server if you're going to pay this much attention

-Todos

I have a problem with Access. Since I'm used (and prefer) to enter
queries in SQL mode, I'm facing the following:
Let's say I enter this query
SELECT A.* FROM (SELECT * FROM Orders WHERE OrderNo > 100) AS A
When I save it and re-open, I find it is converted to
SELECT A.* FROM [SELECT * FROM Orders WHERE OrderNo > 100]. AS A
and I get a syntax error.
Does anyone has faced this problem yet? Any suggestion?
Thanks
alfadj

sounds like the QBE stuff is fixing your SQL. Why are you even
writing your select like that? (or is this a simplification that you
posted? what you're getting from the DB engine or whatever is the
"optimized" version...) Why not just use

SELECT A*
FROM Orders
WHERE OrderNo>100
AND...

which would be the logical equivalent of what you're doing and the
query engine or whatever won't get all worked up?

Actually I've simplified the statement. Since I don't like to use many
intermediate queries stored in the DB, I prefer to use a single
statement like this:

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN (SELECT id, bar FROM
table2 WHERE cost>100) AS B ON A.id = B.id

After save and re-open I get

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN [SELECT id, bar FROM
table2 WHERE cost>100]. AS B ON A.id = B.id

(My statement is more complicated, this was just an example)

By the way, thanks- Hide quoted text -

- Show quoted text -

 

[Tony Toews [MVP]]

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Dirk Goldgar]

In

I have a problem with Access. Since I'm used (and prefer) to enter
queries in SQL mode, I'm facing the following:
Let's say I enter this query

SELECT A.* FROM (SELECT * FROM Orders WHERE OrderNo > 100) AS A

When I save it and re-open, I find it is converted to

SELECT A.* FROM [SELECT * FROM Orders WHERE OrderNo > 100]. AS A

and I get a syntax error.

Does anyone has faced this problem yet? Any suggestion?

alfadj

sounds like the QBE stuff is fixing your SQL. Why are you even
writing your select like that? (or is this a simplification that you
posted? what you're getting from the DB engine or whatever is the
"optimized" version...) Why not just use

SELECT A*
FROM Orders
WHERE OrderNo>100
AND...

which would be the logical equivalent of what you're doing and the
query engine or whatever won't get all worked up?

Actually I've simplified the statement. Since I don't like to use many
intermediate queries stored in the DB, I prefer to use a single
statement like this:

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN (SELECT id, bar FROM
table2 WHERE cost>100) AS B ON A.id = B.id

After save and re-open I get

SELECT A.foo, B.bar FROM table1 AS A LEFT JOIN [SELECT id, bar FROM
table2 WHERE cost>100]. AS B ON A.id = B.id

(My statement is more complicated, this was just an example)

By the way, thanks

Note that your examples of the rewritten SQL *are* valid syntax for Jet
SQL. Where you run into trouble with this annoying rewriting is when
your SQL contains bracketed names; for example, if you had originally
written this:

SELECT A.* FROM
(SELECT * FROM Orders WHERE [OrderNo] > 100) AS A

.... it would be rewritten as this:

SELECT A.* FROM
[SELECT * FROM Orders WHERE [OrderNo] > 100]. AS A

.... and then would stumble over what it sees as improper bracketing.
I'm guessing that this is the cause of the syntax error you're getting.
If you have kept all spaces and other nonstandard characters out of your
table and field names, you should be able to remove the unnecessary
brackets around the names, and the SQL will be valid even after the
query designer does its hatchet-work.

 

[Todos Menos [MSFT]]

PLEASE NOTE THAT TONY IS NOT A CREDIBLE SOURCE

HE IS A MDB BABY