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.
input id $ more $;
Now the query
select b.more from (select distinct id from b);
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>
>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 $;
> 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;
>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:
>The second statement is just a standalone run of the inner query from the
>first statement. Result:
>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>
>>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
>>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).