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?
You probably already know this but, in case not, it works error free on
SAS 9.1.3 on Windows Server 2003.
On Sat, 3 Apr 2010 16:43:42 -0400, Paul Dorfman <sashole@BELLSOUTH.NET>
>Consider this primitive data set:
>data vtest ;
> input ID CD: $1. AMT ;
>1 Y 10
>2 N 20
>2 Y 30
>3 N 40
>3 Y 50
>3 Y 60
>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
>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) ;
>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?