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 *
