Date:         Wed, 4 May 2005 11:06:02 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Proc SQL (output just matches)

I added a bit to the test data and thereby got some more evidence.

   data b;
   input id $ more $;
   cards;
   9 more-1
   2 more-2a
   2 more-2a
   2 more-2b
   6 more-3
   1 more-3
   ;

Now the query

   select b.more from (select distinct id from b);

yields

   more

   more-3
   more-2a
   more-2b
   more-3
   more-1

This suggests that the query has been internally converted to

   select more from (select distinct id, more from b);

In fact, when I ran both of these statements with the _TREE option in
effect I got identical plans.

So it seems PROC SQL is trying to read my mind and substitute a valid
statement for my invalid one, without so much as a note in the log to
indicate what it is up to.

On Fri, 29 Apr 2005 09:42:34 -0400, Howard Schreier <hs AT dc-sug DOT org>
<nospam@HOWLES.COM> wrote:

>I am still perplexed, but meanwhile I've simplified the example (no more
>join; no more alias).
>
>Start with this table:
>
>   data b;
>   input id $ more $;
>   cards;
>   9 more-1
>   2 more-2a
>   2 more-2b
>   6 more-3
>   ;
>
>Run this code:
>
>   proc sql;
>   select b.more from (select distinct id from b);
>                       select distinct id from b;
>   quit;
>
>The first statement's intermediate result (inner SELECT) has one column
>(ID) and three rows, yet the outer SELECT finds a different column (MORE)
>and returns four rows:
>
>   more
>   ----
>   more-2a
>   more-2b
>   more-3
>   more-1
>
>The second statement is just a standalone run of the inner query from the
>first statement. Result:
>
>   id
>   --
>   2
>   6
>   9
>
>I still can't see what the first statement is doing, or how or why.
>
>On Thu, 28 Apr 2005 13:13:44 -0400, Howard Schreier <hs AT dc-sug DOT org>
><nospam@HOWLES.COM> wrote:
>
>>My theory on the local rather than global nature of the alias has been
>>disputed. However, I think that issue is really incidental.
>>
>>I'll ask a different question about Hunter's code, which does run and
which
>>does include the column MORE in the result. How does the outer SELECT find
>>MORE? The join sees two tables, A (which contains ID and STUFF) and the
>>result of the inner SELECT (which contains only ID).
>>
[snip]