Re: SQL 'Select AS' syntax

Thanks All (Steve and Alan)

And thanks Rob for this tidbit as well. Is a " join on key1=key2 " inherently more efficient than a "where key1 = key2" ? (as well as it looks cooler?). I have been using "where" instead of "join" in all my SQL where an inner join is what I want. But if I get a performance boost, I'll change my ways......

"someone who only learned SQL by taking Query/400 and doing a RTVQMQRY against it" -- guilty as charged about 10 years ago. SQL was pretty pricey back then.....

Pete


rob@xxxxxxxxx wrote:

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