SQL scalar functions can transform data into information.

Numbify Packed Decimals using SQL

--
--
-- Description: Convert packed decimal numbers into decimals
--
--
cl:addlible qsysinc;
cl:clrlib qtemp;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(NIB);
insert into qtemp.qcsrc values
 (1, 010101,'{'),
 (2, 010101, 'if (sizeof(BLOCK1.OUTPUT) == sizeof(BLOCK1.INPUT))'),
 (3, 010101,'{'),
 (4, 010101,'memcpy(&BLOCK1.OUTPUT, BLOCK1.INPUT, sizeof(BLOCK1.OUTPUT));'),
 (5, 010101,'BLOCK1.SQLP_I4 = 0;'),
 (6, 010101,'}'),
 (7, 010101,'else'),
 (10,010101,'{'),
 (11,010101,'BLOCK1.SQLP_I4 = -1;'),
 (12,010101,'}'),
 (13,010101,'}');

create or replace function coolstuff.binary4_to_decimal (
      p_input binary(4)
  )
  returns decimal(7)
  returns null on null input
  block1: begin
    declare input binary(4);
    declare output decimal(7);
    set input = p_input;
    include qtemp/qcsrc(nib);
    return output;
  end;

create or replace function coolstuff.binary10_to_decimal (
      p_input binary(10)
  )
  returns decimal(19)
  returns null on null input
  block1: begin
    declare input binary(10);
    declare output decimal(19);
    set input = p_input;
    include qtemp/qcsrc(nib);
    return output;
  end;

values coolstuff.binary4_to_decimal(X'0725840F');
values coolstuff.binary4_to_decimal(X'9876543D');
values coolstuff.binary10_to_decimal(X'1234567890123456789D');

Thanks Scott for this handy SQL Numberfier... I was just last week needed to extract some packed decimal data from the trigger buffer result of the DISPLAY_JOURNAL SQL service. Now with these types of functions I can wrap the result in a Dec(value,length,precision) function and do a little power of 10 arithmetic (.01) to move the decimal place and voila… I can get a Dec(7,2) value from your Dec(7) function result.

Values Dec( ZU018135.binary4_to_decimal(X'0725840F') * .01 , 7 , 2);
-- returns [ 7258.40 ]

Values Dec( ZU018135.binary4_to_decimal(X'9876543D') * .01 , 7 , 2);
-- returns [ -98765.43 ]

Nice going Matthew and thank you for sharing this example of taking Numbify to the next level.