Complicated MLOAD Update

[etldev]

I need to be able to MLOAD into a table and during the MLOAD processing to determine whether the current row is the newest row in a grouping of history records already in the table and if it is to mark it. I can run a query to update records older than the current record and younger than the current record but I currently cannot tell at processing time where the current record falls into. I am trying to use this SQL but am getting the message 3538: A MultiLoad Update Statement is Invalid.... which I figure is to complicated for MLOAD. Any Ideas?

Thanks,

Michael

.DML LABEL UPDATE_DML_3
IGNORE MISSING UPDATE ROWS;
UPDATE &DBASE_TARGETTABLE..&TARGETTABLE
SET PROCESS_CD = 'LATEST AND GREATEST'
WHERE
s_id = :s_id and
s_i_id = :s_i_id and
o_dt = CASTo_dt AS TIMESTAMP(0)) and
o_dt IN
(
SELECT max(o_dt) from &DBASE_TARGETTABLE..&TARGETTABLE
WHERE s_id = :s_id and
s_i_id = :s_i_id
);

 

[dnoeth]

You can't use subqueries in MultiLoad DML statements, you can only access this row's columns.
You only can set that column after the load using a SQL update.

Dieter

 

[etldev]

Thanks Dieter!

I have something that should work but for some reason only a few items are showing the correct process_cd of 'LATEST AND GREATEST'. I assume that I have fastexported all the related rows that I have for my original mload and merge them together to be mloaded. Then I UpSert the load into my table. Afterwards I set all of the process_cd for the PK minus the datestamp to 'LATEST AND GREATEST'. Lastly, I Update the PK with datestamps less than the current datestamp to 'OLDER ROW'. I have found when I go back and look at my table sorted by PK that the first couple of items history is set correctly.... but the further down I look it is not set correctly and I don't understand why all rows where not touched so that query would update as it should have.

/* IF CURRENT ROW ALREADY EXIST THEN DO NOTHING */
/* IF IT DOESN'T EXIST THEN INSERT AS 'LATEST AND GREATEST'*/

.DML LABEL UPDATE_DML_1
DO INSERT FOR MISSING UPDATE ROWS;
UPDATE &DBASE_TARGETTABLE..&TARGETTABLE
SET s_s_cd = :s_s_cd
WHERE s_id = :s_id and
s_i_id = :s_i_id and
o_dt = CASTo_dt AS TIMESTAMP(0)) and
r_id = :r_id;

INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE VALUES
(
:S_ID
,:S_I_ID
,:C_DT
,:B_DT
,:B_AM
,:A_DT
,:CO_DT
,:I_ID
,:I_I_ID
,:I_I_CD
,:CO_AM
,:R_CD
,:R_S_CD
,:A_T_CD
,:R_AM
,:L_DT
,:O_DT
,'LATEST AND GREATEST'
,:S_S_CD
,:S_F_CD
,:R_ID
);

/* RETIRE ALL RELATED ROWS */

.DML LABEL UPDATE_DML_2
IGNORE MISSING UPDATE ROWS;
UPDATE &DBASE_TARGETTABLE..&TARGETTABLE
SET PROCESS_CD = 'LATEST AND GREATEST'
WHERE s_id = :s_id and
s_i_id = :s_i_id;

/* IF ROW IS CURRENTLY UNRETIRED AND IS OLDER THAN CURRENT THEN RETIRE */

.DML LABEL UPDATE_DML_3
IGNORE MISSING UPDATE ROWS;
UPDATE &DBASE_TARGETTABLE..&TARGETTABLE
SET PROCESS_CD = 'OLDER ROW'
WHERE s_id = :s_id and
s_i_id = :s_i_id and
o_dt < CASTo_dt AS TIMESTAMP(0));

.IMPORT INFILE exported_data.txt
FORMAT FASTLOAD
LAYOUT DATAIN_LAYOUT
APPLY UPDATE_DML_1
APPLY UPDATE_DML_2
APPLY UPDATE_DML_3;