Re: SQL 'Select AS' syntax

[Pete Helgren <Pete@xxxxxxxxxx>]

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
>