Assign Alias to Long SQL Column Names:

Document ID : KB000021142
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Long SQL column names can present a problem to CA Culprit, since you can create a null indicator name by appending _NULL_IND to the end of the previously generated REC card name. Is there a way to avoid potential errors by using an AS clause to assign an alias to each SQL column?

Environment:

z/OS

Answer: 

You can ASSIGN an ALIAS to the SQL Column Name using AS Clause to rename the SQL Column Name:

For example:

//SYSIN    DD *                                             
INPUT  DB(Q)    DICTIONARY=SYSDICT    SCHEMA=DEMOEMPL      
SQL SELECT "EMP_ID" AS "ID",                               
*           "EMP_FNAME" AS "FNAME",                         
*           "EMP_LNAME" AS "LNAME"                          
*    FROM EMPLOYEE                                          
*    WHERE "EMP_ID" > 2000                                  
*    ORDER BY ID;                                           
0151*001  ID                                               
0151*002  FNAME                                            
0151*003  LNAME                                            
/*

 

Please note that you may not use the alias names ID, FNAME, and LNAME on most clauses in the SQL statement (Aliases are allowed on the order by clause). However, the aliases will be used to generate REC parameters, and must be used on all subsequent CA Culprit statements.

Generated REC Cards

Figure 1

Additional Information:

CA Culprit User Guide, Using the AS Clause to Rename SQL Columns:

https://support.ca.com/cadocs/0/CA%20IDMS%2018%205%20User%20Bookshelf-ENU/Bookshelf_Files/PDF/IDMS_Culprit_User_ENU.pdf?