Leading blank with ST09190 if DBSQLPR query includes CASE

Products

• Datacom
• Datacom/AD

Issue/Introduction

Since applying APAR ST09190 we find that there is an inconsistency in the leading blank character of each output row (ST09190 was supposed to eliminate the leading blank incorrectly introduced in CA Datacom 12.0).

If the SQL statement includes a CASE statement then the unwanted leading-blank reappears.

For example:

SELECT SUBSTR(DIGITS(TBL.DBID),2) ||
TBL.AREA_NAME || TBL.TABLE_NAME ||
'4' ||
RTRIM(ARA.OCCURRENCE) || '(PROD,DD)'
FROM SYSADM.DIR_TABLE TBL, SYSADM.DIR_AREA ARA
WHERE TBL.DIR_NAME = ARA.DIR_NAME
AND TBL.DBID = ARA.DBID
AND TBL.AREA_NAME = ARA.AREA_NAME
AND TBL.OCCURRENCE = 'TRDR-TRN-TYP'
;

..generates..

0102A04F044TRDR-TRN-TYP(PROD,DD)

...correctly. But...

 SELECT SUBSTR(DIGITS(TBL.DBID),2) ||            

        TBL.AREA_NAME || TBL.TABLE_NAME ||       

        CASE ARA.DATA_SPACE_OPTION               

          WHEN '0' THEN '4'                      

          WHEN '1' THEN '4'                      

          WHEN '2' THEN '5'                      

          ELSE 'X'                               

        END ||                                   

        RTRIM(ARA.OCCURRENCE) || '(PROD,DD)'     

   FROM SYSADM.DIR_TABLE TBL, SYSADM.DIR_AREA ARA

  WHERE   TBL.DIR_NAME = ARA.DIR_NAME            

    AND       TBL.DBID = ARA.DBID                

    AND  TBL.AREA_NAME = ARA.AREA_NAME           

    AND TBL.OCCURRENCE = 'TRDR-TRN-TYP'          

;                                   

...generates...

 0102A04F044TRDR-TRN-TYP(PROD,DD)

(leading blank at the start of the row).

DBSQLPR OPTIONS are:  

AUTHID=SYSADM        

INPUTWIDTH=80        

NOCOLHDR             

NOECHO               

NOFORMFEED           

NOTYPE               

PAGELEN=2147483647   

PRTWIDTH=80          

PRTY=2               

ROWLIMIT=999999999   

Environment

Release : 15.1

Component : CA DATACOM SQL

Cause

Second query is NULLABLE.

Resolution

If you take out the NOTYPE and NOCOLHDR to see the datatype of the 1st column, you will see the first query is NOT NULLABLE and the 2nd is NULLABLE. 

It's the null indicator byte in the second query that is a space.