How do I replace a packed decimal field that is contained in a larger character field after I have updated.

8. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Paul Nicolay

Posted Tue June 11, 2024 04:57 PM

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay
------------------------------

Original Message

9. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Daniel Gross

IBM Champion

Posted Wed June 12, 2024 03:59 AM

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross
------------------------------

Original Message

10. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.
Best Answer

Like

Paul Nicolay

Posted Wed June 12, 2024 04:30 AM

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay
------------------------------

Original Message

11. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Daniel Gross

IBM Champion

Posted Wed June 12, 2024 06:43 AM

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross
------------------------------

Original Message

12. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Paul Nicolay

Posted Wed June 12, 2024 08:06 AM
Edited by Paul Nicolay Wed June 12, 2024 08:06 AM

It seems I was missing one more cast to decimal.

Anyway, this is a fully working example;

set schema qtemp;
create or replace table test (col1 char(20));
delete from test;
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000054321F' AS CHAR(6)));
update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));
select interpret(blob(substr(col1, 7, 6)) as decimal(11)) from test;
select hex(col1) from test;

The statement the original poster was looking for is the...

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));

------------------------------
Paul Nicolay
------------------------------

Original Message

Original Message:
Sent: Wed June 12, 2024 06:43 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Wed June 12, 2024 04:29 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 03:58 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Tue June 11, 2024 04:56 PM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay

Original Message:
Sent: Tue June 11, 2024 11:09 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It inserts '(ABCD.' INTO SUBSTR(ABCD.FILL20, 7, 6)

No packing takes place.

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 08:35 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Birgitta,

Thank you for the quick response.

I tried this...but I am unfamiliar with EXECUTE IMMEDIATE.

The following SQL generates

SET ABCD.FILL20 = 'INSERT(ABCD.FILL20, 7, 6, CAST(X'''

|| '000' || SUBSTR((CAST(SUBSTR(HEX(SUBSTR(ABCD.FILL20, 7, 6 )), 1, 11) AS DEC(12, 4)) * ANCD.RATE), 1, 8)

|| 'C'' AS CHAR(6)))'

This statement...

SET ABCD.FILL20 = INSERT(ABCD.FILL20, 7, 6, CAST(X'000187380000C' AS CHAR(6)))

and it successfully inserts '(ADFP.' INTO SUBSTR(ABCD.FILL20, 7, 6)

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 03:57 AM
From: Birgitta Hauser
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Have you tried to prepare your update statement as string and then run it EXECUTE IMMEDIATE?

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Mon June 10, 2024 04:52 PM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

I am working on an software package that is over 20 years old. There is no opportunity to change file design.

in table ABCD, there is a 20 character field name FILL20.

In the 7th Character for 6 characters of FILL20 there is a field that contains an AMT, which in COBOL is defined PIC S9(07)V9(04) COMP-3. Packed Decimal.

On a regular basis I need to multiple this AMT value by a field called RATE which is DEC(5, 4).

Repack the result and put it back in FILL20 characters 7 for 6.

I need to perform this update in SQL.

See attachment for details.

Thanks in advance.

------------------------------
Frank Caggiano
------------------------------

#SQL

13. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

John Gojnich

Posted Thu June 13, 2024 06:51 AM

"I am working on a software package that is over 20 years old." So 40+ years! How about a UDF to set the rate?

**free
ctl-opt nomain ;

// setRate inside aBLOB
dcl-proc setRate export ;
  dcl-pi *n char(20) ;
    newRate packed(7:2) const ;
    oldBlob char(20);
  end-pi ;
 
  dcl-s  aBlob_p pointer ;
  dcl-ds aBlob based(aBlob_p) ;
    blob1 char(3) ;
    oldRate packed(7:2) ;
    blob2 char(13) ;
  end-ds ;
  
  aBlob_p = %addr(oldBlob) ;
  oldRate = newRate; 

  return aBlob;
end-proc ;

Then add some SQL ...

cl:CRTRPGMOD MODULE(setRate) SRCSTMF(setRate.rpg);
cl:CRTSRVPGM SRVPGM(inBlob) MODULE(setRate) EXPORT(*ALL) TEXT('set Rate');
CREATE OR REPLACE FUNCTION setRate (newRate DEC(7,2), oldBlob CHAR(20))
RETURNS CHAR(20)
LANGUAGE RPGLE
DETERMINISTIC
NO SQL
EXTERNAL NAME 'INBLOB(SETRATE)'
PARAMETER STYLE GENERAL
PROGRAM TYPE SUB ;

create or replace variable newRate decimal(7,2) default(12345.67);
create or replace variable aBlob Char(20) default(x'0000007654321F00000000000000000000000000');

-- seems to work!
with variables (newBlob) as (values setrate(newRate, aBlob)) select hex(newBlob) from variables;

/John G

------------------------------
John Gojnich
------------------------------

Original Message

Original Message:
Sent: Wed June 12, 2024 08:05 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It seems I was missing one more cast to decimal.

Anyway, this is a fully working example;

set schema qtemp;
create or replace table test (col1 char(20));
delete from test;
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000054321F' AS CHAR(6)));
update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));
select interpret(blob(substr(col1, 7, 6)) as decimal(11)) from test;
select hex(col1) from test;

The statement the original poster was looking for is the...

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 06:43 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Wed June 12, 2024 04:29 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 03:58 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Tue June 11, 2024 04:56 PM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay

Original Message:
Sent: Tue June 11, 2024 11:09 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It inserts '(ABCD.' INTO SUBSTR(ABCD.FILL20, 7, 6)

No packing takes place.

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 08:35 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Birgitta,

Thank you for the quick response.

I tried this...but I am unfamiliar with EXECUTE IMMEDIATE.

The following SQL generates

SET ABCD.FILL20 = 'INSERT(ABCD.FILL20, 7, 6, CAST(X'''

|| '000' || SUBSTR((CAST(SUBSTR(HEX(SUBSTR(ABCD.FILL20, 7, 6 )), 1, 11) AS DEC(12, 4)) * ANCD.RATE), 1, 8)

|| 'C'' AS CHAR(6)))'

This statement...

SET ABCD.FILL20 = INSERT(ABCD.FILL20, 7, 6, CAST(X'000187380000C' AS CHAR(6)))

and it successfully inserts '(ADFP.' INTO SUBSTR(ABCD.FILL20, 7, 6)

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 03:57 AM
From: Birgitta Hauser
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Have you tried to prepare your update statement as string and then run it EXECUTE IMMEDIATE?

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Mon June 10, 2024 04:52 PM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

I am working on an software package that is over 20 years old. There is no opportunity to change file design.

in table ABCD, there is a 20 character field name FILL20.

In the 7th Character for 6 characters of FILL20 there is a field that contains an AMT, which in COBOL is defined PIC S9(07)V9(04) COMP-3. Packed Decimal.

On a regular basis I need to multiple this AMT value by a field called RATE which is DEC(5, 4).

Repack the result and put it back in FILL20 characters 7 for 6.

I need to perform this update in SQL.

See attachment for details.

Thanks in advance.

------------------------------
Frank Caggiano
------------------------------

#SQL

14. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Paul Nicolay

Posted Thu June 13, 2024 07:54 AM

A UDF is a cleaner solution but I wonder what the performance difference will be (performing the external call on each record).

BTW, why use a blob and not just a char(20)... a database that old will be defined like that. (normally the CCSID of the column should be set to 65535).

------------------------------
Paul Nicolay
------------------------------

Original Message

Original Message:
Sent: Thu June 13, 2024 06:50 AM
From: John Gojnich
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

"I am working on a software package that is over 20 years old." So 40+ years! How about a UDF to set the rate?

**freectl-opt nomain ;// setRate inside aBLOBdcl-proc setRate export ;  dcl-pi *n char(20) ;    newRate packed(7:2) const ;    oldBlob char(20);  end-pi ;   dcl-s  aBlob_p pointer ;  dcl-ds aBlob based(aBlob_p) ;    blob1 char(3) ;    oldRate packed(7:2) ;    blob2 char(13) ;  end-ds ;    aBlob_p = %addr(oldBlob) ;  oldRate = newRate;   return aBlob;end-proc ;

Then add some SQL ...

cl:CRTRPGMOD MODULE(setRate) SRCSTMF(setRate.rpg);cl:CRTSRVPGM SRVPGM(inBlob) MODULE(setRate) EXPORT(*ALL) TEXT('set Rate');CREATE OR REPLACE FUNCTION setRate (newRate DEC(7,2), oldBlob CHAR(20))RETURNS CHAR(20)LANGUAGE RPGLEDETERMINISTICNO SQLEXTERNAL NAME 'INBLOB(SETRATE)'PARAMETER STYLE GENERALPROGRAM TYPE SUB ;create or replace variable newRate decimal(7,2) default(12345.67);create or replace variable aBlob Char(20) default(x'0000007654321F00000000000000000000000000');-- seems to work!with variables (newBlob) as (values setrate(newRate, aBlob)) select hex(newBlob) from variables;

/John G

------------------------------
John Gojnich

Original Message:
Sent: Wed June 12, 2024 08:05 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It seems I was missing one more cast to decimal.

Anyway, this is a fully working example;

set schema qtemp;
create or replace table test (col1 char(20));
delete from test;
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000054321F' AS CHAR(6)));
update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));
select interpret(blob(substr(col1, 7, 6)) as decimal(11)) from test;
select hex(col1) from test;

The statement the original poster was looking for is the...

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 06:43 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Wed June 12, 2024 04:29 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 03:58 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Tue June 11, 2024 04:56 PM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay

Original Message:
Sent: Tue June 11, 2024 11:09 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It inserts '(ABCD.' INTO SUBSTR(ABCD.FILL20, 7, 6)

No packing takes place.

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 08:35 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Birgitta,

Thank you for the quick response.

I tried this...but I am unfamiliar with EXECUTE IMMEDIATE.

The following SQL generates

SET ABCD.FILL20 = 'INSERT(ABCD.FILL20, 7, 6, CAST(X'''

|| '000' || SUBSTR((CAST(SUBSTR(HEX(SUBSTR(ABCD.FILL20, 7, 6 )), 1, 11) AS DEC(12, 4)) * ANCD.RATE), 1, 8)

|| 'C'' AS CHAR(6)))'

This statement...

SET ABCD.FILL20 = INSERT(ABCD.FILL20, 7, 6, CAST(X'000187380000C' AS CHAR(6)))

and it successfully inserts '(ADFP.' INTO SUBSTR(ABCD.FILL20, 7, 6)

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 03:57 AM
From: Birgitta Hauser
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Have you tried to prepare your update statement as string and then run it EXECUTE IMMEDIATE?

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Mon June 10, 2024 04:52 PM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

I am working on an software package that is over 20 years old. There is no opportunity to change file design.

in table ABCD, there is a 20 character field name FILL20.

In the 7th Character for 6 characters of FILL20 there is a field that contains an AMT, which in COBOL is defined PIC S9(07)V9(04) COMP-3. Packed Decimal.

On a regular basis I need to multiple this AMT value by a field called RATE which is DEC(5, 4).

Repack the result and put it back in FILL20 characters 7 for 6.

I need to perform this update in SQL.

See attachment for details.

Thanks in advance.

------------------------------
Frank Caggiano
------------------------------

#SQL

15. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

John Gojnich

Posted Fri June 14, 2024 06:07 AM

Hmmm, as Kent Milligan says "Not All Db2 Functions Are Created Equal". I think the performance looks pretty good as well.

Test getRate & setRate vs interpret & insert

I'm not using any blobs, it's just a name. Tweaked some of the SQL OPTIONS: NOT FENCED, STATEMENT DETERMINISTIC, ...

CREATE OR REPLACE FUNCTION getRate (oldBlob CHAR(20))
RETURNS DEC(11,0)
LANGUAGE RPGLE
	NOT FENCED
	STATEMENT DETERMINISTIC
	NO EXTERNAL ACTION
NO SQL
EXTERNAL NAME 'INBLOB(GETRATE)'
PARAMETER STYLE GENERAL
PROGRAM TYPE SUB ;

------------------------------
John Gojnich
------------------------------

Original Message

Original Message:
Sent: Thu June 13, 2024 07:54 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

A UDF is a cleaner solution but I wonder what the performance difference will be (performing the external call on each record).

BTW, why use a blob and not just a char(20)... a database that old will be defined like that. (normally the CCSID of the column should be set to 65535).

------------------------------
Paul Nicolay

Original Message:
Sent: Thu June 13, 2024 06:50 AM
From: John Gojnich
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

"I am working on a software package that is over 20 years old." So 40+ years! How about a UDF to set the rate?

**freectl-opt nomain ;// setRate inside aBLOBdcl-proc setRate export ;  dcl-pi *n char(20) ;    newRate packed(7:2) const ;    oldBlob char(20);  end-pi ;   dcl-s  aBlob_p pointer ;  dcl-ds aBlob based(aBlob_p) ;    blob1 char(3) ;    oldRate packed(7:2) ;    blob2 char(13) ;  end-ds ;    aBlob_p = %addr(oldBlob) ;  oldRate = newRate;   return aBlob;end-proc ;

Then add some SQL ...

cl:CRTRPGMOD MODULE(setRate) SRCSTMF(setRate.rpg);cl:CRTSRVPGM SRVPGM(inBlob) MODULE(setRate) EXPORT(*ALL) TEXT('set Rate');CREATE OR REPLACE FUNCTION setRate (newRate DEC(7,2), oldBlob CHAR(20))RETURNS CHAR(20)LANGUAGE RPGLEDETERMINISTICNO SQLEXTERNAL NAME 'INBLOB(SETRATE)'PARAMETER STYLE GENERALPROGRAM TYPE SUB ;create or replace variable newRate decimal(7,2) default(12345.67);create or replace variable aBlob Char(20) default(x'0000007654321F00000000000000000000000000');-- seems to work!with variables (newBlob) as (values setrate(newRate, aBlob)) select hex(newBlob) from variables;

/John G

------------------------------
John Gojnich

Original Message:
Sent: Wed June 12, 2024 08:05 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It seems I was missing one more cast to decimal.

Anyway, this is a fully working example;

set schema qtemp;
create or replace table test (col1 char(20));
delete from test;
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000054321F' AS CHAR(6)));
update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));
select interpret(blob(substr(col1, 7, 6)) as decimal(11)) from test;
select hex(col1) from test;

The statement the original poster was looking for is the...

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 06:43 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Wed June 12, 2024 04:29 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 03:58 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Tue June 11, 2024 04:56 PM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay

Original Message:
Sent: Tue June 11, 2024 11:09 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It inserts '(ABCD.' INTO SUBSTR(ABCD.FILL20, 7, 6)

No packing takes place.

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 08:35 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Birgitta,

Thank you for the quick response.

I tried this...but I am unfamiliar with EXECUTE IMMEDIATE.

The following SQL generates

SET ABCD.FILL20 = 'INSERT(ABCD.FILL20, 7, 6, CAST(X'''

|| '000' || SUBSTR((CAST(SUBSTR(HEX(SUBSTR(ABCD.FILL20, 7, 6 )), 1, 11) AS DEC(12, 4)) * ANCD.RATE), 1, 8)

|| 'C'' AS CHAR(6)))'

This statement...

SET ABCD.FILL20 = INSERT(ABCD.FILL20, 7, 6, CAST(X'000187380000C' AS CHAR(6)))

and it successfully inserts '(ADFP.' INTO SUBSTR(ABCD.FILL20, 7, 6)

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 03:57 AM
From: Birgitta Hauser
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Have you tried to prepare your update statement as string and then run it EXECUTE IMMEDIATE?

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Mon June 10, 2024 04:52 PM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

I am working on an software package that is over 20 years old. There is no opportunity to change file design.

in table ABCD, there is a 20 character field name FILL20.

In the 7th Character for 6 characters of FILL20 there is a field that contains an AMT, which in COBOL is defined PIC S9(07)V9(04) COMP-3. Packed Decimal.

On a regular basis I need to multiple this AMT value by a field called RATE which is DEC(5, 4).

Repack the result and put it back in FILL20 characters 7 for 6.

I need to perform this update in SQL.

See attachment for details.

Thanks in advance.

------------------------------
Frank Caggiano
------------------------------

#SQL

16. RE: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Like

Frank Caggiano

Posted Thu June 13, 2024 11:15 AM

Hi Everyone,

Thanks to everyone for your time, knowledge, creativity and relentless pursuit of a solution.

This works...it is mostly from Paul Nicolay, but I will use all of your input in the final implementation.

Thanks again, you are all amazing!

SOLUTION:

UPDATE SCHEMA.ABCD ABCD
SET ABCD.FILL20 =
INSERT(ABCD.FILL20, 7, 6,
CAST(HEXTORAW(HEX(DECIMAL(INTERPRET(BLOB(SUBSTR(ABCD.FILL20, 7, 6)) AS DECIMAL(11)) * (ABCD.RATE), 11, 0))) AS CHAR(6)))
WHERE
ABCD.ID = '12345';

------------------------------
Frank Caggiano
------------------------------

Original Message

Original Message:
Sent: Wed June 12, 2024 08:05 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It seems I was missing one more cast to decimal.

Anyway, this is a fully working example;

set schema qtemp;
create or replace table test (col1 char(20));
delete from test;
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000054321F' AS CHAR(6)));
update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));
select interpret(blob(substr(col1, 7, 6)) as decimal(11)) from test;
select hex(col1) from test;

The statement the original poster was looking for is the...

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex(decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1 , 11, 0))) as char(6)));

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 06:43 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Paul,

I think you are missing the correct data type and length. My example produces the following in hex:

C1C2C31234567FC4C5C6

When length is important, always use the DEC function with 3 parameters.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Wed June 12, 2024 04:29 AM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Daniel,

The statement you posted is not working... the cast/hextoraw doesn't generate the correct "packing".

I tried combining it all;

update test set col1 = INSERT(col1, 7, 6, cast(hextoraw(hex( decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) )) as char(6) ) );

... but I get 4040404040400000000000124040404040404040 in hex representation (while the 000000012 should be 12346F in my example.

------------------------------
Paul Nicolay

Original Message:
Sent: Wed June 12, 2024 03:58 AM
From: Daniel Gross
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

in fact it seems, that "unpacking" is so far easy using INTERPRET - but "packing" is much harder.

I managed to get it done - somehow - but it's not so obvious:

values 'ABC' || cast( hextoraw( hex( dec(12345.67, 7, 2) ) ) as char(4)) || 'DEF';

So - this line of code is only an example, because I didn't have a table on hand. Now dig into that code from the inside.

I create a packed decimal with DEC - at this place your field should be.
The packed decimal value gets converted to hexadecimal char string.
Now that hex string gets converted back to "raw" format - the result is a binary string.
The binary string has to be CAST to a normal string with the given length.
And now I can concatenate that result with other strings.

As I said - that isn't very obvious - but AFAIK is seems the only way to go.

So to use that all, you have to replace the DEC(...) with you packed value - resulting from INTERPRET and your calculation.

If I have time today, I will try to construct another example.

HTH

Daniel

------------------------------
Daniel Gross

Original Message:
Sent: Tue June 11, 2024 04:56 PM
From: Paul Nicolay
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi,

I still fail to reverse the value back to some binary data, but this already the first part

set schema qtemp;
create table test (col1 char(20));
-- creating a test record
insert into test values(' ');
update test set col1 = INSERT(col1, 7, 6, CAST(X'00000012345F' AS CHAR(6)));
-- calculating the value plus one which works, but the insert is wrong :-(
update test set col1 = INSERT(col1, 7, 6, decimal(interpret(blob(substr(col1, 7, 6)) as decimal(11)) + 1) );

Kind regards,

Paul

------------------------------
Paul Nicolay

Original Message:
Sent: Tue June 11, 2024 11:09 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

It inserts '(ABCD.' INTO SUBSTR(ABCD.FILL20, 7, 6)

No packing takes place.

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 08:35 AM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Hi Birgitta,

Thank you for the quick response.

I tried this...but I am unfamiliar with EXECUTE IMMEDIATE.

The following SQL generates

SET ABCD.FILL20 = 'INSERT(ABCD.FILL20, 7, 6, CAST(X'''

|| '000' || SUBSTR((CAST(SUBSTR(HEX(SUBSTR(ABCD.FILL20, 7, 6 )), 1, 11) AS DEC(12, 4)) * ANCD.RATE), 1, 8)

|| 'C'' AS CHAR(6)))'

This statement...

SET ABCD.FILL20 = INSERT(ABCD.FILL20, 7, 6, CAST(X'000187380000C' AS CHAR(6)))

and it successfully inserts '(ADFP.' INTO SUBSTR(ABCD.FILL20, 7, 6)

------------------------------
Frank Caggiano

Original Message:
Sent: Tue June 11, 2024 03:57 AM
From: Birgitta Hauser
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

Have you tried to prepare your update statement as string and then run it EXECUTE IMMEDIATE?

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Mon June 10, 2024 04:52 PM
From: Frank Caggiano
Subject: How do I replace a packed decimal field that is contained in a larger character field after I have updated.

I am working on an software package that is over 20 years old. There is no opportunity to change file design.

in table ABCD, there is a 20 character field name FILL20.

In the 7th Character for 6 characters of FILL20 there is a field that contains an AMT, which in COBOL is defined PIC S9(07)V9(04) COMP-3. Packed Decimal.

On a regular basis I need to multiple this AMT value by a field called RATE which is DEC(5, 4).

Repack the result and put it back in FILL20 characters 7 for 6.

I need to perform this update in SQL.

See attachment for details.

Thanks in advance.

------------------------------
Frank Caggiano
------------------------------

#SQL

Programming Languages on Power

Power Programming Languages