PROC ACCESS - View Issue

Amitkmr1979 — Tue, 12 May 2020 08:17:30 GMT

I am trying to create a view using proc access in our legacy system which uses DB2 on Z/OS. When I create view and try to run it I get the below error:

ERROR: View selection criteria or where clause is not valid.

I do not have any where clause in the view I am creating.

It is select all.

I am guessing there issue with one of the column name being more than 17 char in length in DB2. When I drop that column in data step I do not get the above error.

1) Do we have any restriction on column names length in access. I am running ver 8.2 on mainframe, but not sure if access was supported post v6/v7

2) What is a work around I can use if that is the restriction causing the view to fail? In access, else will have to go to SQL pass through.

The descriptor and the view statements are below

PROC ACCESS DBMS=DB2;                            
   CREATE &OUTNAME..&ACCESS..ACCESS;             
   TABLE=&DBNAME..&POTPOV&ACCESS;                
   SSID=&ESESID;                                 
   ASSIGN=N;                                     
   LIST ALL;                                     
RUN;                                             
                                                 
PROC ACCESS DBMS=DB2 ACCDESC=&OUTNAME..&ACCESS ; 
   CREATE &OUTNAME..&VIEWNAME..VIEW;             
   SELECT ALL;                                   
   UN=Y;                                         
   LIST VIEW;

When I see the log I can see one thing, the MQ_STATUS_TIMESTAM is actually MQ_STATUS_TIMESTAMP (With P in name). I tested theory of column length > 17 by creating another view which gave me same error for column name > 17 char and if I drop the column it works fine:

Function: CREATE  Descriptors- access: RNSENT view: RNSENTX    
  Item  Column Name        SAS Name Format                     
  1     TOLY_Cxxx         TOLY_Cxx $1.            * SELECTED *
  2     TOLY_BRCH          TOLY_BRC $2.            * SELECTED *
  3     TOLY_Pol         TOLY_POL $7.            * SELECTED *
  33    MQ_STATUS_TIMESTAM MQ_STATU DATETIME30.6   * SELECTED *

Re: PROC ACCESS - View Issue

Amitkmr1979 — Tue, 12 May 2020 22:35:13 GMT

Hi,

Any one who has faced similar issue while creating access descriptors and views? Or if anyone can pass me the SAS mail ID where I can send out the clarification/question.

Re: PROC ACCESS - View Issue

AlanC — Tue, 12 May 2020 23:16:51 GMT

I can't directly help you since I haven't used ACCESS/DB2 in 9 months or so. However, this is what I found: https://communities.sas.com/t5/Administration-and-Deployment/Accessing-db2-column-that-has-name-more-than-18-characters/td-p/515308

Search terms I used: SAS ACCESS DB2 17 COLUMN NAME

I know you wanted a quick direction . Hope that helps.

Re: PROC ACCESS - View Issue

Amitkmr1979 — Tue, 12 May 2020 23:52:46 GMT

Thanks AlanC, this helps.

topic Re: PROC ACCESS - View Issue in Developers

PROC ACCESS - View Issue

Re: PROC ACCESS - View Issue

Re: PROC ACCESS - View Issue

Re: PROC ACCESS - View Issue