| Author |
Message
|
| DELLIPIZ |
 Posted: Wed Jul 27, 2005 3:05 pm Post subject: Concatenating fields to create a string with positioning |
 |
|
Acolyte
Joined: 08 Oct 2003
Posts: 70
|
Hi,
I am using WMQI 2.1 for an XML to XML transformation. However, in addition to that, I need to insert a column on a DB2 table. That column is defined as VARCHAR(4000). It that COLUMN, I need to put multiple pieces of data from my input XML. However, that column is positional, meaning that:
POSITION 1 - 10 - should contain FIELD1
POSITION 11 - 20 - should contain FIELD2
POSITION 21 - 40 - should contain FIELD3
etc etc etc
My problem is that any of the fields might not be the same length as the max length. So FIELD1 might only be 3 characters, so then I would need to put 7 blanks afterwards, so that FIELD2 can still start in POSITION 11.
I was wondering if anyone would have any suggestions of how to handle this, since there are alot of FIELDS involved in creating the input to this COLUMN.
Thanks!
-Lori |
|
| Back to top |
|
 |
| JT |
| Posted: Wed Jul 27, 2005 3:36 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
This is un-tested code, but you could use something like this:
| Code: |
DECLARE blanks CHARACTER ' ';
SET databaseColumn = FIELD1 || SUBSTRING(blanks FROM 1 FOR (10 - LENGTH(FIELD1)) ||
FIELD2 || SUBSTRING(blanks FROM 1 FOR (10 - LENGTH(FIELD2)) ||
FIELD3 || SUBSTRING(blanks FROM 1 FOR (20 - LENGTH(FIELD3)); |
|
|
| Back to top |
|
|
| DELLIPIZ |
| Posted: Sun Jul 31, 2005 9:27 pm Post subject: |
|
|
Acolyte
Joined: 08 Oct 2003
Posts: 70
|
Hi,
I tried that, but it didn't seem to work for me.  So I tried a different approach. I created an MRM that makes up all the fields that needs to be inserted into the one database column that is defined as VARCHAR(4000).
So my COBOL MRM (CWF) was created as follows:
01 REQUEST-TAB.
05 REQ-INFO.
10 FIELD1 PIC X(20).
10 FIELD2 PIC X(20).
10 FIELD3 PIC X(10).
10 FIELD4 PIC X(10).
etc
So in one compute node, I set the fields as follows:
InputRoot.MRM.REQ-INFO.FIELD1 = STRING(20)= 'HI'
InputRoot.MRM.REQ-INFO.FIELD2 = STRING(20) = 'THERE'
InputRoot.MRM.REQ-INFO.FIELD3 = STRING(10) = SPACES
InputRoot.MRM.REQ-INFO.FIELD4 = STRING(10) = 'FRIEND'
etc
Then I tried two things in another compute node:
1)
SET VAR_STRING_HOLD = InputRoot.MRM.REQ_INFO;
INSERT INTO Database.TEST(VAR_STRING)
Values(VAR_STRING_HOLD);
OUTPUT: It put a null in the column. It did not put my values.
2)
SET VAR_STRING_HOLD = InputRoot.MRM.REQ_INFO.FIELD1 || InputRoot.MRM.REQ_INFO.FIELD2 ||
InputRoot.MRM.REQ_INFO.FIELD3 || InputRoot.MRM.REQ_INFO.FIELD4;
INSERT INTO Database.TEST(VAR_STRING)
Values(VAR_STRING_HOLD);
But when it put the values on the table, instead of putting something like:
'HI THERE FRIEND '
It is putting:
'HITHEREFRIEND'
So I can't keep the spaces.
-------------------------------------
Any suggestion of how I can keep the spaces or how I can create the MRM to get my expected results on the table????
Thanks again!
-Lori |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Aug 01, 2005 3:11 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Using a model is a good idea.
Getting a DBA to straighten out this mess of a database table is a better idea.
But to use the model, you need to
a) make sure that the model is active for the data - probably using Create Field to build the first field in the record and assign the necessary information to associate the model with the data
b) Use ASBITSTREAM to produce a formatted output of the model in the same way it would get produced if you wrote it to a queue.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| recallsunny |
| Posted: Mon Aug 01, 2005 6:30 am Post subject: |
|
|
Disciple
Joined: 15 Jun 2005
Posts: 163
Location: Massachusetts
|
If appending SPACES is the only solution, then try the "SPACE" function
| Code: |
SET databaseColumn =
FIELD1 || SPACE(10-(LENGTH(FIELD1)) ||
FIELD2 || SPACE(10-(LENGTH(FIELD2)) ||
FIELD3 || SPACE(20-(LENGTH(FIELD3));
|
CAST it as CHAR before inserting...
Cheers
 |
|
| Back to top |
|
|
| kspranava |
| Posted: Mon Aug 01, 2005 10:54 pm Post subject: OVERLAY |
|
|
Centurion
Joined: 27 Apr 2003
Posts: 124
|
Hi DELLIPIZ,
Check out OVERLAY function. |
|
| Back to top |
|
|
|
|
