Date:         Sat, 3 Apr 2010 17:37:18 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: SQL view bug?
Comments: To: Paul Dorfman <sashole@BELLSOUTH.NET>

Paul,

You probably already know this but, in case not, it works error free on
SAS 9.1.3 on Windows Server 2003.

Art
--------
On Sat, 3 Apr 2010 16:43:42 -0400, Paul Dorfman <sashole@BELLSOUTH.NET>
wrote:

>Consider this primitive data set:
>
>data vtest ;
>   input ID CD: $1. AMT ;
>cards ;
>1 Y 10
>2 N 20
>2 Y 30
>3 N 40
>3 Y 50
>3 Y 60
>;
>run ;
>
>and even more primitive SAS SQL code run against it:
>
>proc sql ;
>   create view vtest_view as
>   select ID
>        , CD = "Y"                   as A
>        , calculated A and (AMT > 5) as B
>   from   vtest
>   ;
>   create table vtest_sum as
>   select ID
>        , sum (A) as SUM_A
>        , sum (B) as SUM_B
>   from   vtest_view
>   group  ID
>   ;
>quit ;
>
>This results in the following SAS SQL
>
>ERROR: sqlplan internal error: Cannot find symbol B, var=4, tag=0001. This
>message can be caused by attempting to make correlated references between
>two different items on a single FROM clause.
>
>This happens under both Windows adn AIX UNIX running the same 9.2.
>Although I kind of comprehend what the message alludes to, I still fail to
>see how that can be an excuse for 2 reasons.
>
>First, the error message disappears, and the processing completes error-
>free, if instead of the *view* VTEST_VIEW I create a *table* and reference
>it in the ensuing statement.
>
>Second, if I create a completely equivalent *DATA step* view, say
>
>data ds_view / view = ds_view ;
>   set vtest ;
>   A = (CD = "Y") ;
>   B = (A and AMT > 5) ;
>run ;
>
>and let the summarizing step consume it instead of the *SQL view*, it has
>no problem running normally, either.
>
>(If anyone is interested why I need a view in the first place, in the real
>life the mocked-up CD="Y" condition is *much* more involved, and I do not
>want SAS to sift through the computations twice for performance reasons -
>otherwise I would simply make the boolean expressions arguments of the SUM
>SQL function in a single SQL step.)
>
>Looks like a veritable bug to me. Anyone thinks it is a feature?
>
>Kind regards
>------------
>Paul Dorfman
>Jax, FL
>------------