0

Using the comments made in the conversation - appreciate all the suggestions - I have a PL/SQL iterative loop followed by the merge concept that was suggested earlier.

Ultimately, in the examples you see a table called header that has 4 rows of data. These are sample data made up so nothing needs to be obfuscated.

Each record is analyzed for certain columns in the table to determine based off logic in the LOOP below whether that column or any combination of column varying by each records needs to get updated. In some cases a record may not need to be updated, some cases where just 1 column, or cases more than one column needs to be updated.

The requirement though is that a source header record cannot just be updated. To satisfy the requirement if a record has a dollar amount of $50 for example - then a record has to be inserted to show a -$50 (only difference in this record than the source is the scenario) and then another record of $50 created showing where the money is moving to with the appropriate columns that required an update updated plus the scenario column.

The code (the loop) parses through the records and determines if any record is eligible for an update on a column, if it identifies at least one update then the two adjustment records need to be loaded into a mapping table to be used in the merge statements (someone here suggested this and was very helpful) to merge int the main header table. If the loop sees no updates are required based off the record values then no adjust records are written to the mapping table.

The code parses each important column (not scenario and the amount columns as those are never drivers) - you see the logic that if the value doesnt require an update then the new value equals the old value. If a column requires an update then the newvalue equals the new value. The counter notes whether for each parsed record whether there is at least one adjustment registered. If so - then that entire record is unique and needs to be written to the mapping table even if the majority of the column values didn't change.

The example below is very much a sample - the real use case will require many fields and will require many more variables and more updated logic for each field.

Note I use the %type - I would personally like to use the %rowtype to do the same thing inside the loop below. There will be cases where I would want to analyze the entire record in a more efficient way.

Is there a way I can accomplish the same logic (perhaps with a formal cursor or select into a variable) to accomplish the same thing?

Example Header Records

ORG_CODE PROJECT_CODE OUTPUT_CODE VERSION AMOUNT_DOLLARS
E4JAZ P04 O234 Actual 50
D_ORG P01 D_OUTPUT Actual 70
E4JAZ P_PROJ D_OUTPUT Actual 50

After the below loop code is executed - adjust_rows_mapping records (not the result post-merge but just the adjust_rows_mapping) - Note the header before the fields - aligned by comma

Note that despite the Header table having 3 records - only two of the 3 records (records 1 & 3) have at least one adjustment on a column registered. Record 1 requires 3 adjustments to all 3 fields (shown in the code logic) and record 3 requires just 1 (just to the org) also shown in the logic. Record 2 has no columns that per the logic does not have values that need adjustment. Therefore, the mapping table only gets two records inserted. The non-changing columns in record 3 stay the same despite the updated 1st column in the adjustment record.

OLD_ORG_CODE OLD_PROJECT_CODE NEW_ORG_CODE NEW_PROJECT_CODE OLD_OUTPUT_CODE NEW_OUTPUT_CODE
E4JAZ P04 F4JAZ P01 O234 test_output
E4JAZ P_PROJ F4JAZ P_PROJ D_OUTPUT D_OUTPUT

See code below - ultimately in the loop I am hoping to find a cleaner way using %ROWTYPE to accomplish this and also provide the ability to scale this up as there will be inner joins to mapping tables embedded in the cursor or perhaps sub-loops.

Any ideas would be greatly appreciated!

Thanks All!

DECLARE

v_old_org_code header.org_code%TYPE;
v_old_proj_code header.project_code%TYPE;
v_new_org_code header.org_code%TYPE;
v_new_proj_code header.project_code%TYPE;
v_old_output_code header.output_code%TYPE;
v_new_output_code header.output_code%TYPE;
v_old_version_code header.VERSION%TYPE;
v_new_version_code header.VERSION%TYPE;
v_new_amount_dollars header.AMOUNT_DOLLARS%TYPE;
v_old_amount_dollars header.AMOUNT_DOLLARS%TYPE;
v_counter NUMBER;

begin

FOR record IN (SELECT ORG_CODE, PROJECT_CODE, OUTPUT_CODE, VERSION, AMOUNT_DOLLARS FROM HEADER) LOOP

v_counter := 0;

v_old_org_code := record.ORG_CODE;
v_old_proj_code := record.PROJECT_CODE;
v_old_output_code := record.OUTPUT_CODE;
v_old_version_code := record.VERSION;
v_old_amount_dollars := record.AMOUNT_DOLLARS;

v_new_org_code := record.ORG_CODE;
v_new_proj_code := record.PROJECT_CODE;
v_new_output_code := record.OUTPUT_CODE;
v_new_version_code := record.VERSION;
v_new_amount_dollars := record.AMOUNT_DOLLARS;



IF v_old_org_code = 'E4JAZ' THEN

v_new_org_code := 'F4JAZ';

v_counter := v_counter + 1;

end if;

if v_old_proj_code in ('P04', 'P07') then v_new_proj_code := 'P01';

v_counter := v_counter + 1;

end if;

if v_old_output_code in ('O234') then v_new_output_code := 'test_output';

v_counter := v_counter + 1;

END IF;


if v_counter > 0

then

insert into adjust_rows_mapping (old_org_code, old_project_code, new_org_code, new_project_code
, old_output_code, new_output_code)
--)

values (v_old_org_code, v_old_proj_code , v_new_org_code, v_new_proj_code
, v_old_output_code, v_new_output_code );




END IF;



END LOOP;




MERGE INTO header dst
USING (
  WITH adjust_rows (old_org_code, old_project_code, new_org_code, new_project_code, old_output_code, new_output_code) AS (
    SELECT * from adjust_rows_mapping
  )
  SELECT u.org_code,
         u.project_code,
         u.output_code,
         'Actual_Adjust' AS version,
         h.amount_dollars * u.multiplier AS amount_dollars
  FROM   adjust_rows a
         UNPIVOT (
           (old_org_code, old_project_code, old_output_code, org_code, project_code, output_code)
           FOR multiplier IN (
             (old_org_code, old_project_code, old_output_code, old_org_code, old_project_code, old_output_code) AS -1,
             (old_org_code, old_project_code, old_output_code, new_org_code, new_project_code, new_output_code) AS 1
          )
        ) u
        INNER JOIN header h
         ON h.org_code = u.old_org_code
            AND h.project_code = u.old_project_code
            and h.output_code = u.old_output_code
) src
ON (   src.org_code       = dst.org_code
   AND src.project_code   = dst.project_code
   AND src.output_code    = dst.output_code
   AND src.version        = dst.version
   AND src.amount_dollars = dst.amount_dollars )
WHEN NOT MATCHED THEN
  INSERT (Org_Code, Project_Code, Output_Code, Version, Amount_Dollars)
  VALUES (src.Org_Code, src.Project_Code, src.Output_Code, src.Version, src.Amount_Dollars);












END;
/
Improve this question
3
  • Show us some more sample table data, and the expected result. Commented Apr 1, 2024 at 19:20
  • Note that tables have rows and columns. (Not records/fields.) Commented Apr 1, 2024 at 19:21
  • Please edit the question and include a minimal reproducible example with: the CREATE TABLE and INSERTstatements for your sample data; an complete explanation of the logic that you want to implement; the expected output for that sample data; and YOUR attempt at a solution. Commented Apr 1, 2024 at 20:35

2 Answers 2

Reset to default
0

One possible approach would be to create a pipelined table function (https://oracle-base.com/articles/misc/pipelined-table-functions) which returns the adjustment records. You could then just insert the records returned by the pipelined table function into whatever table they belong.

The advantage of a pipelined table function is that it can return as few or as many records as are needed by using PIPE ROW.

The flow of the function might be something like:

if something_changed then
   PIPE ROW(build adjustment row here);
end if;

if something_else_changed then
   PIPE ROW(build another adjustment row here);
end if;

You could even include two or more PIPE ROW statements in each "if" should multiple adjustment records need to be generated for a given type of change.

One other advantage is that it would be relatively easy to test the function and iterate adjustments to the logic without having to go back and reset your tables.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Use a MERGE statement:

MERGE INTO header dst
USING (
  WITH adjust_rows (old_org_code, old_project_code, new_org_code, new_project_code) AS (
    SELECT 'E4JAZ', 'P04', 'F4JAZ', 'P01' FROM DUAL
  )
  SELECT u.org_code,
         u.project_code,
         h.output_code,
         'Actual_Adjust' AS version,
         h.amount_dollars * u.multiplier AS amount_dollars
  FROM   adjust_rows a
         UNPIVOT (
           (old_org_code, old_project_code, org_code, project_code)
           FOR multiplier IN (
             (old_org_code, old_project_code, old_org_code, old_project_code) AS -1,
             (old_org_code, old_project_code, new_org_code, new_project_code) AS 1
          )
        ) u
        INNER JOIN header h
         ON h.org_code = u.old_org_code
            AND h.project_code = u.old_project_code
) src
ON (   src.org_code       = dst.org_code
   AND src.project_code   = dst.project_code
   AND src.output_code    = dst.output_code
   AND src.version        = dst.version
   AND src.amount_dollars = dst.amount_dollars )
WHEN NOT MATCHED THEN
  INSERT (Org_Code, Project_Code, Output_Code, Version, Amount_Dollars)
  VALUES (src.Org_Code, src.Project_Code, src.Output_Code, src.Version, src.Amount_Dollars)

Which, for the sample data:

CREATE TABLE HEADER (
  Org_Code,
  Project_Code,
  Output_Code,
  Version,
  Amount_Dollars
) AS
SELECT 'E4JAZ', 'P04', 'O234', CAST('Actual' AS VARCHAR2(20)), 50.00 FROM DUAL

Then, after the MERGE, the table contains:

ORG_CODE PROJECT_CODE OUTPUT_CODE VERSION AMOUNT_DOLLARS
E4JAZ P04 O234 Actual 50
F4JAZ P01 O234 Actual_Adjust 50
E4JAZ P04 O234 Actual_Adjust -50

fiddle

If you want to include output_code then include that as well. Similarly, if you want to generate the codes from the existing rows rather than hard coding them then you can do that too using CASE expressions:

MERGE INTO header dst
USING (
  WITH adjust_rows (
    old_org_code,
    old_project_code,
    old_output_code,
    new_org_code,
    new_project_code,
    new_output_code
  ) AS (
    SELECT DISTINCT
           org_code,
           project_code,
           output_code,
           CASE WHEN org_code IN ('E4JAZ') THEN 'F4JAZ' ELSE org_code END,
           CASE WHEN project_code IN ('P04', 'P07') THEN 'P01' ELSE project_code END,
           CASE WHEN output_code IN ('O234') THEN 'test_output' ELSE output_code END
    FROM   header
    WHERE  org_code IN ('E4JAZ')
    OR     project_code IN ('P04', 'P07')
    OR     output_code IN ('O234')
  )
  SELECT u.org_code,
         u.project_code,
         u.output_code,
         'Actual_Adjust' AS version,
         h.amount_dollars * u.multiplier AS amount_dollars
  FROM   adjust_rows a
         UNPIVOT (
           (old_org_code, old_project_code, old_output_code, org_code, project_code, output_code)
           FOR multiplier IN (
             (old_org_code, old_project_code, old_output_code, old_org_code, old_project_code, old_output_code) AS -1,
             (old_org_code, old_project_code, old_output_code, new_org_code, new_project_code, new_output_code) AS 1
          )
        ) u
        INNER JOIN header h
         ON h.org_code = u.old_org_code
            AND h.project_code = u.old_project_code
            AND h.output_code = u.old_output_code
) src
ON (   src.org_code       = dst.org_code
   AND src.project_code   = dst.project_code
   AND src.output_code    = dst.output_code
   AND src.version        = dst.version
   AND src.amount_dollars = dst.amount_dollars )
WHEN NOT MATCHED THEN
  INSERT (Org_Code, Project_Code, Output_Code, Version, Amount_Dollars)
  VALUES (src.Org_Code, src.Project_Code, src.Output_Code, src.Version, src.Amount_Dollars)

fiddle

Improve this answer

4 Comments

If I were to add an additional row to the header table where only the Org Code needs to be altered but not the Project Code - noting that the adjustment records are only created on the record requiring edits on Org Code and Project Code but not the record with the Org Code. This is due to the adjust record requiring both elements to be mapped - how do I do it if it is either or? So if I have two records I would see a total of 6 but with the Code I only still see 4.
Ultimately the "mapping" table is based on each column value independently of each other. In the example - it is matching against the uniqueness of two columns needing updated adjustment rows when other rows could only need just one and are being ignored in this logic.
@smomotiu I am not sure I understand your additional question. You need to provide a minimal reproducible example with the CREATE TABLE and INSERT statements for your sample data and then explain the logic that you want to implement (referring to the sample data) and the expected output for that sample data. If your new requirements are in addition to your current requirements then ask a new question and if it is a clarification of your existing question then edit your question.
Thanks MT0 for the help. I went ahead and updated where I stand now using your merge concept but added a PL/SQL loop to make what you said work. Really cool idea you did - not sure if you can help me on the updated question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.