Writing packed data (variable) to part of character field with SQL

[WilliamTasker]

Hi All

My googling has found a few ideas about how to get packed data OUT of a character field using SQL, but so far I haven't found any information on how to get it IN there.

I refer to, for instance, a general parameter file that has a key field and a general use character field that would usually be handled in RPG using a data structure so that it can contain whatever combination of character/zoned/packed data is required. However I'm working with embedded SQL and prefer to stick with that if possible.

It's easy enough if I want to put a constant value in. Say a 7,0 decimal value of 7654321 :

update hjwup                                  
set UPCOMP = insert(UPCOMP, 7, 4, x'7654321F')

In this case the character data between 1-6 is undisturbed but 7-10 contains hex:
7531
642F

I suppose I could build the statement as a string and execute it using EXECUTE IMMEDIATE but I thought I'd see if there's a more elegant solution.

[B.Hauser]

So what's the question, i.e. where is the problem?

[WilliamTasker]

The question is: How do I "pack" a variable to put into a character field using SQL.

My example is with a constant value.

I'd like to do this:
update hjwup set UPCOMP = insert(UPCOMP, 7, 4, x(myvar)) ... where "myvar" will be different each time the statement executes. But x' ' only works with constants.

[WilliamTasker]

Perhaps it would clarify if I show how the (unknown) value is being retrieved (in a different program at a later stage):

decimal(substr(hex(substr( UPCOMP,7,4)),1,7),7,0)

[Scott Klement]

I don't think you are supposed to do that :-) Char/Varchar fields are meant for text, not packed numbers. So why would the SQL standard provide a way to pack data and put it into a text field? Seems like a very bizarre thing to do. Why not define your field as decimal if you want it packed? Obviously, if all else fails, you can use native I/O to write this.

[B.Hauser]

The only way is to use dynamic SQL.
Here an example:
I first created a temporary file and populated it with strings consisting of Characters and a packed decimal value.
In the dynamic compound statement I replaced the packed value 12345F with 11111F;

Declare Global Temporary Table tempTable
(MyChar  VarChar(256) Default '');    

insert into TempTable
Values ('ABCDE' concat x'12345F' concat 'FGHI'),
       ('XXXXX' concat x'55666F' concat 'YYYY'),
       ('YYYYY' concat x'22334F' concat 'ZZZZ');            

Select Hex(myChar) from TempTable;      

Begin
  Declare NumVar  Dec(5 , 0) Default 11111;

  Execute Immediate 'Update TempTable Set MyChar = Replace( MyChar, x''12345F'', x''' concat Hex(NumVar) concat ''')';

End;

Birgitta

[WilliamTasker]

Thanks for your replies.

[WilliamTasker]

The only way is to use dynamic SQL.
Here an example:

Thanks for that example. Although I reworked the code to add an extra workfile after I read Scott's reply (somehow I didn't get notified of the your later post), I'm sure this will come up again.