Re: SQL 'Select AS' syntax

Have you tried replacing
Select field1, field2, field3 from File1, File2 where key1 = key2 AS tbl
with
Select field1, field2, field3 from File1 AS tbl, File2 where key1 = key2

If you want all fields from all files associated as one big happy table
you could try

With tbl as (
Select field1, field2, field3

From File1, File2

Where key1 = key2
)

But, me, I'd avoid the where clause for a couple of reasons. One, it
flags you as someone who only learned SQL by taking Query/400 and doing a
RTVQMQRY against it. Two, you can do stuff with JOIN that Query/400
explains to you, but warns you with ANZQMQRY that they won't export
correctly with RTVQMQRY - like left outer join, (or as Query calls it:
Matching records with primary file).
I'd do it like:
With tbl as (
Select field1, field2, field3

From File1, File2

Left outer join on file1.key1 = file2.key2
)

Once you have this "tbl" you can do stuff like:

With tbl as (
Select field1, field2, field3

From File1, File2

Left outer join on file1.key1 = file2.key2
)
Select *
from tbl
order by field3

Or, if this is like many vendor packages with meaningless field names

With tbl as (
Select field1 as OrderNumber, field2 as OrderLine, field3 as ItemNumber

From File1, File2

Left outer join on file1.key1 = file2.key2
)
Select *
from tbl
order by ItemNumber



Rob Berendt