Concatenate Text Stored in Byte Arrays within BLOB Type Columns

2 01 2010

January 2, 2010

A couple of days ago I received a question on a private message board regarding how to duplicate the variable length text stored by an ERP system in a BLOB type column.  The ERP system stores the text as a byte array (one byte per character) that is terminated by an ASCII character 0 (a string termination character).  Previous versions of this ERP system actually stored the variable length text in a LONG RAW type column.  BLOB type columns are easier to deal with than LONG RAW columns, so I gave the person a simple INSERT… SELECT to essentially copy the BLOB value from one row to a new row:

INSERT INTO
  OPERATION_BINARY
SELECT
  WORKORDER_TYPE,
  'NEW_BASE_ID',
  'NEW_LOT_ID',
  'NEW_SPLIT_ID',
  'NEW_SUB_ID',
  NEW_SEQUENCE_NO,
  TYPE,
  BITS,
  BITS_LENGTH
FROM
  OPERATION_BINARY
WHERE
  WORKORDER_TYPE='M'
  AND WORKORDER_BASE_ID='ABC123'
  AND WORKORDER_LOT_ID='123'
  AND WORKORDER_SPLIT_ID='0'
  AND WORKORDER_SUB_ID='0'
  AND SEQUENCE_NO=10;

The above is nice and simple, except that it does not accomplish what the person wanted to have happen.

The OP then clarified that he would like to concatenate the text stored in one BLOB type column with the text stored in a BLOB type column found in a different row in the same table:
Row 1 has BLOB bits that says “I AM A NOTE“.
Row 2 has BLOB bits that says “I AM AN ADDITIONAL NOTE.
 
The end result should be Row 1 having a BLOB column with “I AM A NOTE. I AM AN ADDITIONAL NOTE.“, terminated with an ASCII 0 and with the total length of the byte array (including the ASCII 0) recorded in the BITS_LENGTH column.  Easy, right?

To process will be something like this:

  1. Convert the current BLOB value to a VARCHAR2 with UTL_RAW.CAST_TO_VARCHAR2
  2. Append a space to the above result
  3. Convert the BLOB value to be appended to a VARCHAR2 with UTL_RAW.CAST_TO_VARCHAR2 and append to the above result
  4. Append a CHR(0) character to the end of the above result.
  5. Find the length of the above and enter it into the BITS_LENGTH column.
  6. Use UTL_RAW.CAST_TO_RAW to convert the above VARCHAR2 result back into a BLOB and enter it into the BITS column.

Here is how I showed the OP to find a solution (note that I switched to a different table to simplify the explanation):
First, I find the first two rows in the PART_BINARY table with a non-null long description:

SELECT
  PART_ID
FROM
  PART_BINARY
WHERE
  BITS IS NOT NULL
  AND ROWNUM<3;

PART_ID
-------
5P8245
8X8202

Next, I check the current long descriptions:

SELECT
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS
FROM
  PART_BINARY
WHERE
  PART_ID='8X8202';

BITS
-----------------------
FROM VENDOR DEL TO ASSY

 

SELECT
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS
FROM
  PART_BINARY
WHERE
PART_ID='5P8245';

BITS
-----------
(CASE ASSY)

Now an experiment to see what the result would look like:

SELECT
  UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) ||
    ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)),
  LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) ||
    ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0))
FROM
  PART_BINARY PB1,
  PART_BINARY PB2
WHERE
  PB1.PART_ID='8X8202'
  AND PB2.PART_ID='5P8245';

----------------------------------------------------------
46524F4D2056454E444F522044454C20544F204153535920284341534520415353592900 36

(If you remove the UTL_RAW.CAST_TO_RAW in the above, you will see the actual string).

Now, we can convert the SELECT statement into an UPDATE statement like this:

UPDATE
  PART_BINARY
SET
  (BITS, BITS_LENGTH)=
  (SELECT
    UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) ||
      ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0)),
    LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB1.BITS,32000,1)) ||
      ' '||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(PB2.BITS,32000,1))||CHR(0))
    FROM
      PART_BINARY PB1,
      PART_BINARY PB2
    WHERE
      PB1.PART_ID='8X8202'
      AND PB2.PART_ID='5P8245')
WHERE
  PART_ID='8X8202';

Then we can test the result of the UPDATE:

SELECT
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS
FROM
  PART_BINARY
WHERE
  PART_ID='8X8202';

BITS
-----------------------------------
FROM VENDOR DEL TO ASSY (CASE ASSY)

The OP further clarified that the above approach works, but there are possibly 3,000 pairs of rows that need to be combined.  The original approach probably needs to be scrapped.

I then put together the following demonstration:
For experimentation, note that it is possible to do something like the following:

SELECT
  WORKORDER_TYPE,
  WORKORDER_BASE_ID,
  WORKORDER_LOT_ID,
  WORKORDER_SPLIT_ID,
  WORKORDER_SUB_ID,
  SEQUENCE_NO,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS,
  LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH,
  BITS_LENGTH
FROM
  OPERATION_BINARY OB
WHERE
  TYPE='D'
  AND ROWNUM<=10;

The above code sample converts the first 10 long descriptions from the operation binary table, ready to be used as VARCHAR2 data types.  If you remove the AND ROWNUM<10 restriction, you could easily create a view using the above:

CREATE OR REPLACE VIEW OPERATION_BINARY_VIEW AS
SELECT
  WORKORDER_TYPE,
  WORKORDER_BASE_ID,
  WORKORDER_LOT_ID,
  WORKORDER_SPLIT_ID,
  WORKORDER_SUB_ID,
  SEQUENCE_NO,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS,
  LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH,
  BITS_LENGTH
FROM
  OPERATION_BINARY OB
WHERE
  TYPE='D';

Let’s assume that you have an engineering master that you want to use as the source that will be appended to various work order operations.  You could use the view like this to see the source data:

SELECT
  VARCHAR_BITS,
  VARCHAR_BITS_LENGTH,
  BITS_LENGTH
FROM
  OPERATION_BINARY_VIEW
WHERE
  WORKORDER_TYPE='M'
  AND WORKORDER_BASE_ID='0011881110'
  AND WORKORDER_LOT_ID='00'
  AND WORKORDER_SPLIT_ID='0'
  AND WORKORDER_SUB_ID='0'
  AND SEQUENCE_NO=10;

VARCHAR_BITS                    VARCHAR_BITS_LENGTH BITS_LENGTH
------------------------------- ------------------- -----------
TACK AND WELD COMPLETE PER B\P.                  31          32

In the above, note that the VARCHAR_BITS column value is one less than the value in the BITS_LENGTH column – the BITS_LENGTH column value includes the CHR(0) at the end of the column data, while the other length does not.  Now, let’s find a victim series of work order lots:

SELECT
  VARCHAR_BITS,
  VARCHAR_BITS_LENGTH,
  BITS_LENGTH,
  WORKORDER_LOT_ID
FROM
  OPERATION_BINARY_VIEW
WHERE
  WORKORDER_TYPE='W'
  AND WORKORDER_BASE_ID='11111'
  AND WORKORDER_SPLIT_ID='0'
  AND WORKORDER_SUB_ID='0'
  AND SEQUENCE_NO=10
ORDER BY
  WORKORDER_LOT_ID;

VARCHAR_BITS                                    VARCHAR_BITS_LENGTH BITS_LENGTH WO
----------------------------------------------- ------------------- ----------- --
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 1
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 10
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 11
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 12
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 13
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 14
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 15
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 16
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 2
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 3
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 4
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 5
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 6
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 7
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 8
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT.                 48          49 9

There are 16 lots in this work order base ID, so let’s create an UPDATE statement to append our source engineering master’s operation long description to the long description for these lots:

UPDATE
  OPERATION_BINARY OB2
SET
  (BITS,BITS_LENGTH) =
  (SELECT
    UTL_RAW.CAST_TO_RAW(
      UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB2.BITS,32000,1))
      || ' ' || SOURCE_OB.VARCHAR_BITS || CHR(0)
      ) NEW_BITS,
    OB2.BITS_LENGTH+SOURCE_OB.VARCHAR_BITS_LENGTH+2 NEW_BITS_LENGTH
  FROM
    OPERATION_BINARY_VIEW SOURCE_OB
  WHERE
    SOURCE_OB.WORKORDER_TYPE='M'
    AND SOURCE_OB.WORKORDER_BASE_ID='0011881110'
    AND SOURCE_OB.WORKORDER_LOT_ID='00'
    AND SOURCE_OB.WORKORDER_SPLIT_ID='0'
    AND SOURCE_OB.WORKORDER_SUB_ID='0'
    AND SOURCE_OB.SEQUENCE_NO=10)
WHERE
  OB2.TYPE='D'
  AND OB2.WORKORDER_TYPE='W'
  AND OB2.WORKORDER_BASE_ID='11111'
  AND OB2.WORKORDER_SPLIT_ID='0'
  AND OB2.WORKORDER_SUB_ID='0'
  AND OB2.SEQUENCE_NO=10;

16 rows updated.

The inline view in the above combines the source OPERATION_BINARY long description with the long description (aliased as OB2) from the row that will be updated in the OPERATION_BINARY table.  The inline view also calculates the new value for the BITS_LENGTH column – I initially thought that it should be +1, not +2, but the following step shows that it should be +2 so that BITS_LENGTH is one greater than VARCHAR_BITS_LENGTH.  Now, we can check the outcome of the append:

SELECT
  VARCHAR_BITS,
  VARCHAR_BITS_LENGTH,
  BITS_LENGTH,
  WORKORDER_LOT_ID
FROM
  OPERATION_BINARY_VIEW
WHERE
  WORKORDER_TYPE='W'
  AND WORKORDER_BASE_ID='11111'
  AND WORKORDER_SPLIT_ID='0'
  AND WORKORDER_SUB_ID='0'
  AND SEQUENCE_NO=10
ORDER BY
  WORKORDER_LOT_ID;

VARCHAR_BITS                                                                    VARCHAR_BITS_LENGTH BITS_LENGTH WO
------------------------------------------------------------------------------- ------------------- ----------- --
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 1
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 10
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 11
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 12
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 13
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 14
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 15
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 16
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 2
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 3
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 4
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 5
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 6
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 7
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 8
USE PICK OFF FIXTURE TO TACK IN PLACE PER PRINT. TACK AND WELD COMPLETE PER B\P.                 81          82 9

If we are satisfied with the results, we can issue a COMMIT and check the result in the ERP system.  TEST, TEST, TEST before using the above in production.

Great, but not quite there yet – the OP further refined the question.  He would like to combine the text stored in the BLOBs for SEQUENCE_NO 10, 20, and 30 with the text stored in the BLOB for SEQUENCE_NO 40, and store those results in the row for SEQUENCE_NO 40, for any given combination of WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_SPLIT_ID, and WORKORDER_SUB_ID.   Additionally, the OP would like to be able to supply a list of WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_SPLIT_ID, and WORKORDER_SUB_ID to which this combining operation will be performed.

We have now progressed from difficult to impossible (well, almost impossible).  Here is what I suggested:

You want to automatically fold the long descriptions stored in BLOB columns for the first three operations into the fourth operation, and you want to be able to do it without repeatedly executing update statements.  WARNING – do not try this unless you understand what is happening and how to verify that the correct change is made before issuing the final COMMIT.  First, the view from the previous solution will be used:

CREATE OR REPLACE VIEW OPERATION_BINARY_VIEW AS
SELECT
  WORKORDER_TYPE,
  WORKORDER_BASE_ID,
  WORKORDER_LOT_ID,
  WORKORDER_SPLIT_ID,
  WORKORDER_SUB_ID,
  SEQUENCE_NO,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1)) VARCHAR_BITS,
  LENGTH(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB.BITS,32000,1))) VARCHAR_BITS_LENGTH,
  BITS_LENGTH
FROM
  OPERATION_BINARY OB
WHERE
  TYPE='D';

Next, we need a temporary holding table for the work orders and sub IDs you would like to modify.  Create the following table:

CREATE TABLE TEMP_WO_LIST AS
SELECT
  WORKORDER_TYPE,
  WORKORDER_BASE_ID,
  WORKORDER_LOT_ID,
  WORKORDER_SPLIT_ID,
  WORKORDER_SUB_ID
FROM
  OPERATION
WHERE
  1=2;

I created a dummy engineering master and then created 3 work orders from the master: 89890/1 through 89890/3.  I then modified the engineering master and created work order 89891/1.  These are the work orders I want to fix, like you are wanting to fix (actually, just Sub 0).  I insert the rows into the temporary holding table:

INSERT INTO TEMP_WO_LIST VALUES ('W','89890','1','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89890','2','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89890','3','0','0');
INSERT INTO TEMP_WO_LIST VALUES ('W','89891','1','0','0');

COMMIT;

OK, there are now 4 work orders listed in the temporary holding table, let’s see the long descriptions for the operations:

SELECT
  OBV.WORKORDER_BASE_ID,
  OBV.WORKORDER_LOT_ID,
  OBV.WORKORDER_SUB_ID,
  OBV.SEQUENCE_NO,
  OBV.VARCHAR_BITS
FROM 
  OPERATION_BINARY_VIEW OBV,
  TEMP_WO_LIST TWL
WHERE
  TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE
  AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID
  AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID
  AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID
  AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID
  AND OBV.SEQUENCE_NO IN (10,20,30);

WORKORDER_ WOR WOR SEQUENCE_NO VARCHAR_BITS
---------- --- --- ----------- ------------
89890      1   0            10 OP 10 DESC
89890      1   0            20 OP 20 DESC
89890      1   0            30 OP 30 DESC
89890      2   0            10 OP 10 DESC
89890      2   0            20 OP 20 DESC
89890      2   0            30 OP 30 DESC
89890      3   0            10 OP 10 DESC
89890      3   0            20 OP 20 DESC
89890      3   0            30 OP 30 DESC
89891      1   0            10 OP 10 DESC2
89891      1   0            20 OP 20 DESC2
89891      1   0            30 OP 30 DESC2

Notice in work order 89891/1 I added the number 2 to the end of each long description when I modified the engineering master before creating that work order.
 
Next, we need to use a trick to collapse 3 rows into a single row so that the long descriptions may be combined.  This may be done by using MAX and DECODE with a GROUP BY clause like this (note that I told SQL*Plus to line-wrap the column – just ignore that):

SELECT
  OBV.WORKORDER_BASE_ID,
  OBV.WORKORDER_LOT_ID,
  OBV.WORKORDER_SUB_ID,
  MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS||' ')) ||
  MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS||' ')) ||
  MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS)) VARCHAR_BITS,
  NVL(MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS_LENGTH))+1,0) +
  NVL(MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS_LENGTH))+1,0) +
  NVL(MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS_LENGTH)),0) VARCHAR_LENGTH
FROM 
  OPERATION_BINARY_VIEW OBV,
  TEMP_WO_LIST TWL
WHERE
  TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE
  AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID
  AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID
  AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID
  AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID
  AND OBV.SEQUENCE_NO IN (10,20,30)
GROUP BY
  OBV.WORKORDER_BASE_ID,
  OBV.WORKORDER_LOT_ID,
  OBV.WORKORDER_SPLIT_ID,
  OBV.WORKORDER_SUB_ID;

WORKORDER_ WOR WOR VARCHAR_BITS         VARCHAR_LENGTH
---------- --- --- -------------------- --------------
89890      1   0   OP 10 DESC OP 20 DES             32
                   C OP 30 DESC
89890      2   0   OP 10 DESC OP 20 DES             32
                   C OP 30 DESC
89890      3   0   OP 10 DESC OP 20 DES             32
                   C OP 30 DESC
89891      1   0   OP 10 DESC2 OP 20 DE             35
                   SC2 OP 30 DESC2

Now, like the final solution earlier, we convert the above into an UPDATE statement:

UPDATE
  OPERATION_BINARY OB2
SET
  (BITS,BITS_LENGTH)=
  (SELECT
   UTL_RAW.CAST_TO_RAW(
    MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS||' ')) ||
    MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS||' ')) ||
    MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS||' ')) ||
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(OB2.BITS,32000,1)) || CHR(0) ) VARCHAR_BITS,
    NVL(MAX(DECODE(OBV.SEQUENCE_NO,10,OBV.VARCHAR_BITS_LENGTH))+1,0) +
    NVL(MAX(DECODE(OBV.SEQUENCE_NO,20,OBV.VARCHAR_BITS_LENGTH))+1,0) +
    NVL(MAX(DECODE(OBV.SEQUENCE_NO,30,OBV.VARCHAR_BITS_LENGTH))+1,0) +
    OB2.BITS_LENGTH + 1 VARCHAR_LENGTH
  FROM 
    OPERATION_BINARY_VIEW OBV,
    TEMP_WO_LIST TWL
  WHERE
    TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE
    AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID
    AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID
    AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID
    AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID
    AND OBV.SEQUENCE_NO IN (10,20,30)
    AND OBV.WORKORDER_TYPE=OB2.WORKORDER_TYPE
    AND OBV.WORKORDER_BASE_ID=OB2.WORKORDER_BASE_ID
    AND OBV.WORKORDER_LOT_ID=OB2.WORKORDER_LOT_ID
    AND OBV.WORKORDER_SUB_ID=OB2.WORKORDER_SUB_ID
  GROUP BY
    OBV.WORKORDER_BASE_ID,
    OBV.WORKORDER_LOT_ID,
    OBV.WORKORDER_SPLIT_ID,
    OBV.WORKORDER_SUB_ID)
WHERE
  (WORKORDER_TYPE,
   WORKORDER_BASE_ID,
   WORKORDER_LOT_ID,
   WORKORDER_SPLIT_ID,
   WORKORDER_SUB_ID) IN
  (SELECT
    WORKORDER_TYPE,
    WORKORDER_BASE_ID,
    WORKORDER_LOT_ID,
    WORKORDER_SPLIT_ID,
    WORKORDER_SUB_ID
  FROM
    TEMP_WO_LIST)
  AND OB2.SEQUENCE_NO=40;

4 rows updated. 

Then we verify that we obtained the desired results:

SELECT
  OBV.WORKORDER_BASE_ID,
  OBV.WORKORDER_LOT_ID,
  OBV.SEQUENCE_NO SEQ,
  OBV.VARCHAR_BITS,
  OBV.VARCHAR_BITS_LENGTH VB_LENGTH,
  OBV.BITS_LENGTH B_LENGTH
FROM
  OPERATION_BINARY_VIEW OBV,
  TEMP_WO_LIST TWL
WHERE
  TWL.WORKORDER_TYPE=OBV.WORKORDER_TYPE
  AND TWL.WORKORDER_BASE_ID=OBV.WORKORDER_BASE_ID
  AND TWL.WORKORDER_LOT_ID=OBV.WORKORDER_LOT_ID
  AND TWL.WORKORDER_SPLIT_ID=OBV.WORKORDER_SPLIT_ID
  AND TWL.WORKORDER_SUB_ID=OBV.WORKORDER_SUB_ID
ORDER BY
  OBV.WORKORDER_BASE_ID,
  OBV.WORKORDER_LOT_ID,
  OBV.SEQUENCE_NO;

WORKORDER_ WOR SEQ VARCHAR_BITS          VB_LENGTH   B_LENGTH
---------- --- --- -------------------- ---------- ----------
89890      1    10 OP 10 DESC                   10         11
89890      1    20 OP 20 DESC                   10         11
89890      1    30 OP 30 DESC                   10         11
89890      1    40 OP 10 DESC OP 20 DES         44         45
                   C OP 30 DESC OP 40 D
                   ESC
89890      2    10 OP 10 DESC                   10         11
89890      2    20 OP 20 DESC                   10         11
89890      2    30 OP 30 DESC                   10         11
89890      2    40 OP 10 DESC OP 20 DES         44         45
                   C OP 30 DESC OP 40 D
                   ESC
89890      3    10 OP 10 DESC                   10         11
89890      3    20 OP 20 DESC                   10         11
89890      3    30 OP 30 DESC                   10         11
89890      3    40 OP 10 DESC OP 20 DES         44         45
                   C OP 30 DESC OP 40 D
                   ESC
89891      1    10 OP 10 DESC2                  11         12
89891      1    20 OP 20 DESC2                  11         12
89891      1    30 OP 30 DESC2                  11         12
89891      1    40 OP 10 DESC2 OP 20 DE         48         49
                   SC2 OP 30 DESC2 OP 4
                   0 DESC2

In the above, B_LENGTH must be 1 greater than VB_LENGTH.  If we are happy, we can issue a COMMIT.


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: