Converting Microsoft SQL queries to regular SQL

[tdion]

Someone has handed bunch of my Microsoft SQL queries. They work fine, except the ones that refer to other queries.

For example:

SELECT Equip FROM qryStorage UNION SELECT Equip FROM qryBrokenStock .........

where qryStorage and qryBrokenStock are other MS Acces Queries. I am turning these into a JSP and need plain SQL to do this (without saved queries.) How can one accomplish this?

 

[Golom]

Depends on what version of Access you are running AND what's in the saved queries.

In Access 2K and above, Access allows a subquery within a FROM clause so your SQL can become
[blue][tt]
(SELECT Qry1.Equip
FROM (Select Equip , b, c From ...) As Qry1

UNION

(SELECT Qry2.Equip
FROM (Select Equip , e, f From ...) As Qry2
[/tt][/blue]

 

[tdion]

I don't think I made myself clear. I am trying to convert saved queries to standard SQL. The "AS qryStorage" will save the query as qryStorage in Access.

However, I am not using Access.... and I need to replicate the saved query capabilities in a non Access environment.

Any thoughts?

 

[Golom]

No. "As qryStorage" does not save the query under that name.

It assigns an alias (aka range variable) name to the in-line subquery that can be used to refer to the subquery within the main query. A quirk of SQL is that, if you use a subquery within a FROM clause, then you must assign a range variable name to it even if you don't explicitly use that name elsewhere in the statement.

What "non Access" environment are you using?

 

[tdion]

I really don't understand you. Maybe 50% of what you say...

I have written Java Server Pages and am accessing an Access database through a JDBC connection. JSP is an alternative to ASP for creating dynamic webpages with DB content (in case you didn't know.)

Tell me.... what exactly does the "AS qryStorage" segment do? Does is create a temporary reference to that query in the computer's RAM?

I was under the impression that "AS qryStorage" was proprietary to Access.... but from what you have said, it is a universal SQL command.

 

[tdion]

Can anyone help?

 

[lespaul]

You need to put the SQL from the OTHER queries into this query. So...

SELECT Equip FROM (PUT THE SQL FROM qryStorage HERE) UNION SELECT Equip FROM (PUT THE SQL FROM qryBrokenStock HERE) .........

Leslie

 

[Golom]

Leslie
Ahhh ... my translator arrives again!

tdion
Leslie is correct ... sorry if I mislead you.

"As SomeName" is standard SQL. It may be used with a field to give it a new name or it may be used with a table and/or query to provide a name that you use to refer to that table or query. In this instance I was trying to convey the message that
[blue][tt]
Select ... some fields ...

From [/tt][/blue][red][tt](Select ... From SomeTable ...) [/tt][/red][green][tt]As Alias
[/tt][/green]
means

Take the SQL from your saved query and insert it in place of what is in [red]red[/red]. Then assign a name for it (the part in [green]green[/green]) by which you will refer to it in this query.

 

[lespaul]

Well you know, if you didn't speak GEEK all the time, others might understand you too!

les

 

[Golom]

Leslie
I usually try not to but when discussing the inner mysteries of SQL syntax ... the SQL standard and virtually everything written about SQL is based on GEEK-SPEAK. Besides ... trying to do it in something approaching English may reveal how little I actually know. Can't have that.

 

[lespaul]

Yeah, I know that blank look non-SQL persons get when I start going on about the complexities of some query I was trying to get!

leslie