• Print |
• E-mail  |
• Bookmark

In Base SAS® software, SQLPLAN internal errors might occur when you select columns from an SQL view or from an in-line view if one or more of the following conditions are true:

• a count(*) function and an ORDER BY clause are referenced in the outer SELECT statement
• the inner SELECT statement has an aliased column
• the outer SELECT statement redefines the aliased column and gives it a different name.

The following code illustrates syntax that can produce the SQLPLAN internal errors. Note that this code is intended to be run as a stand-alone piece of code, but it illustrates the incorrect syntax.

   proc sql;                              
      create view view1 as                    
         select distinct                        
                comm_level_1 as clevel               
                ,comm_level_1                        
                ,sum(curr_inv) as base_curr_inv       
         from libref.table                       
      group by clevel, comm_level_1;         
                                        
      create table test1 as                   
         select count(*) as rowcount,            
                clevel,                         
                comm_level1 as comm_level_2,    
                base_curr_inv as ordercol       
         from view1                              
      order by ordercol desc;                
   quit;     	

Select the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

proc sql ;                              
create view view1 as                    
 select distinct                        
   comm_level_1 as clevel               
  ,comm_level_1                         
  ,sum(curr_inv) as base_curr_inv       
 from libref.table                       
 group by clevel, comm_level_1;         
                                        
                                        
create table test1 as                   
select count(*) as rowcount,            
        clevel,                         
        comm_level1 as comm_level_2,    
        base_curr_inv as ordercol       
from view1                              
order by ordercol desc ;                
quit;                                   
                                        

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at: