Problem Note 39472: An SQLPLAN error occurs when you run an SQL procedure query that includes a function within a summary function
 |  |  |  |  |
When you use a LIBNAME engine with a PROC SQL query that is run against database management system (DBMS) data, you might receive an SQLPLAN error similar to the following:
ERROR: sqlplan internal error: Cannot find symbol #TEMG002, var=3,
tag=0003. This message can be caused by attempting to make
correlated references between two different items on a single FROM
clause.
The error might occur when a subquery in the procedure includes a function within a summary function, as shown in the following example:
libname oralib oracle user=user-name password=password path=pathname schema=schema-name;
proc sql;
create table test as
select sum(input(ddate,yymmdd8.)-input(adate,yymmdd8.)) as billed_days
from (select ddate, adate
from oralib.t1, oralib.t2
where t1.id = t2.id);
quit;
Currently, the only workaround is to separate the subquery and create a data set in a separate step. Then use that data set as input to the query.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.2 TS2M2 | 9.2 TS2M3 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft® Windows® for x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2008 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Microsoft Windows XP Professional | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Enterprise 32 bit | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Enterprise x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Home Premium 32 bit | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Home Premium x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Professional 32 bit | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Professional x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Ultimate 32 bit | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows 7 Ultimate x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Windows Vista | 9.2 TS2M2 | 9.2 TS2M3 | ||
| 64-bit Enabled AIX | 9.2 TS2M2 | 9.2 TS2M3 | ||
| 64-bit Enabled HP-UX | 9.2 TS2M2 | 9.2 TS2M3 | ||
| 64-bit Enabled Solaris | 9.2 TS2M2 | 9.2 TS2M3 | ||
| HP-UX IPF | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Linux | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Linux for x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
| OpenVMS on HP Integrity | 9.2 TS2M2 | 9.2 TS2M3 | ||
| Solaris for x64 | 9.2 TS2M2 | 9.2 TS2M3 | ||
A fix for this issue for Base SAS 9.21_M1 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/A50.html#39472
The following error occurs when you run an SQL procedure query:"ERROR: sqlplan internal error: Cannot find symbol #TEMG002, var=3, tag=0003. This message can be caused by attempting to make correlated references between two different items on a single FROM
clause."
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2010-04-29 13:40:49 |
| Date Created: | 2010-04-28 13:27:36 |