The SPACE Function Takes Up a Lot of Space

Published: October 18, 2006

Hey, Ted:

I have to build a file for input to another system. All fields are fixed-length, and numeric values are to be edited and right-adjusted. SQL's CHAR function takes care of the editing requirement, but the result is left-adjusted. How do I right-adjust an edited numeric value? Or will I have to rewrite my program using native I/O operations?

--Sarah

On the surface, this seemed like an easy request. It turned to be a little more complicated than I had thought.

I suggested Sarah use the SPACE function to generate enough leading blanks to force right alignment. For example, the following expression right-adjusts an edited version of field ABUBAL within an area of twenty bytes.

SELECT space(20 - length(trim(char(ABUBAL))))||trim(char(ABUBAL)), ...

The SPACE function takes one argument--the number of spaces to be generated. Suppose that the edited ABUBAL occupies seven bytes. SPACE returns 13 blanks, which are concatenated to the edited ABUBAL.

IMHO, this expression deserves its own spot in the county landfill. However, it works. Well, it works up to a point. Here was Sarah's final query.

INSERT INTO ABMASTF
SELECT ABTRNS, ABCUS, ABTDT,
  space(20 - length(trim(char(ABUBAL))))||trim(char(ABUBAL)),
  digits(ABNDD),
  space(20 - length(trim(char(ABTAMT))))||trim(char(ABTAMT)),
  ABREF, ABPONO, ABDIV,
  space(20 - length(trim(char(ABTAX))))||trim(char(ABTAX)),
  space(20 - length(trim(char(ABFRGT))))||trim(char(ABFRGT)),
  space(20 - length(trim(char(ABOTH))))||trim(char(ABOTH)),
  ABTTYP,
  ABCHKN, ABPNUM, ABRCDE, ABSLSN, ABSHPT, ABSRCI, ABTCCD,
  space(20 - length(trim(char(ABTCAM))))||trim(char(ABTCAM)),
  space(20 - length(trim(char(ABTCBA))))||trim(char(ABTCBA)),
  space(20 - length(trim(char(ABLCAM))))||trim(char(ABLCAM)),
  space(20 - length(trim(char(ABLCBA))))||trim(char(ABLCBA)),
  ABDIVC
FROM ABmast

At this point, her SQL command cancelled with error message SQL0101.

SQL statement too long or complex. ... 3 - The sum of the lengths of the non-LOB columns in a select list, table, view definition, or user defined table function is greater than 32766 or the definition contains a LOB and the sum of the lengths specified on the ALLOCATE clause for varying-length fields and the non-varying field lengths is greater than 32740. The maximum length is reduced if any of the columns are varying-length or allow null values.

I knew the field lengths did not add up to 32,766 bytes, so I contacted IBM for help with this one. Thanks to Sue Ramono and Jeff Tenner, I learned that the SPACE function returns a 4000-byte result. Sue and Jeff suggested using the SUBSTR (substring) function instead.

substr('                    ',1,
        20-length(trim(char(ABUBAL)))||trim(char(ABUBAL)),

The first argument is twenty spaces surrounded by single quotes. The second argument tells the position of the first blank to return (i.e., position one), and the third argument is the number of blanks to return. It's still ugly, but SUBSTR uses less resources. (In all fairness, I should point out that SQL is designed to retrieve data, not format it.)

While I was waiting to hear back from IBM, I toyed with a user-defined function to right-adjust a value. Here is the RADJ function, hastily thrown together and unproven in production.

create function mylib/radj                           
   (inString varchar(256), inLength integer)           
   returns varchar(256)                                
   language SQL                                        
   contains SQL                                        
   deterministic                                       
   returns null on null input                          
   no external action                                  
   begin                                               
      if inLength < length(trim(inString))             
         then signal sqlstate '22003'                  
              set message_text = 'Length is invalid.'; 
      end if;                                          
      return                                           
        (space(inLength-length(trim(inString))) concat 
         trim(inString));
   end

RADJ takes two arguments--the character value to be right-adjusted and the size of the area in which it is to be right-adjusted. I was able to run queries with many RADJ functions without getting the SQL0101 error.

SELECT radj(char(ABUBAL),20) ...

What an interesting profession! I learn something new every day.

--Ted