plsql to fetch the max sequence line for every header and update the null values of the column with next sequence number for the particular header

Question

I have a requirement like below . I have a table with 2 columns, (contract_id,line_num)

create table tx (contract_id number,line_num number);

I have data like

contract_id  ||   line_num
-----------      ---------
 1           ||      1
 1           ||      null
 1           ||      null
 2           ||       1
 2           ||      null
 2           ||      null
 3           ||      1
 3           ||      null

I have to write a plsql block , first I have to get the max(line_num) for each contract_id, and then update the next sequence number for each contract_id where ever there is null in line_num column for each contract_id using cursor for loop.

I should get like below

contract_id    ||     line_num
-----------          ---------
   1           ||      1
   1           ||      2
   1           ||      3
   2           ||      1
   2           ||      2
   2           ||      3
   3           ||      1
   3           ||      2

can u pls help me with this...

DECLARE
    var1   NUMBER := 0;
BEGIN
    SELECT MAX (gocpd.column46)
      INTO var1
      FROM gecm_okc_con_part_details gocpd, okc_rep_contracts_all orca
     WHERE     gocpd.contract_id = orca.contract_id
           AND orca.attribute12 = 'GE-Power' --AND GOCPD.COLUMN46 = NULL AND GOCPD.CONTRACT_ID = 525215; END
                                            ;

    BEGIN
        UPDATE GECM_OKC_CON_PART_DETAILS GOCPD
           SET GOCPD.COLUMN46 = var1 + 1
          FROM okc_rep_contracts_all orca
         WHERE     gocpd.contract_id = orca.contract_id
               AND orca.attribute12 = 'GE-Power'
               AND gocpd.column46 = NULL
               AND gocpd.contract_id = 525215;

        COMMIT;
    END;
END;

Please also provide an example where it is **not** null in `line_num` column and `max(line_num)` is not 1 and how the rows should be updated for such cases. Also tell us how should the next `contract_id` in the sequence be known when the `null` and `max(line_num)` is not in order? Do you have (or thinking about) another incrementing primary key column?, do tell us. It will save our time if you clarify these ambiguities. — Kaushik Nayak, Jun 01 '18 at 04:44
i just want to update the null values in the line_num column by the next sequence no of the line_num, based on the contract_id. — Pasha Md, Jun 01 '18 at 06:08
i want to update at one go whereever there is null in the line_num, for all the contract_id's. — Pasha Md, Jun 01 '18 at 06:09
DECLARE VAR1 NUMBER := 0; BEGIN BEGIN SELECT MAX(GOCPD.COLUMN46) INTO VAR1 FROM GECM_OKC_CON_PART_DETAILS GOCPD, OKC_REP_CONTRACTS_ALL ORCA WHERE GOCPD.CONTRACT_ID = ORCA.CONTRACT_ID AND ORCA.ATTRIBUTE12 = 'GE-Power' --AND GOCPD.COLUMN46 = NULL AND GOCPD.CONTRACT_ID = 525215; END; BEGIN UPDATE GECM_OKC_CON_PART_DETAILS GOCPD SET GOCPD.COLUMN46 = VAR1+1 FROM OKC_REP_CONTRACTS_ALL ORCA WHERE GOCPD.CONTRACT_ID = ORCA.CONTRACT_ID AND ORCA.ATTRIBUTE12 = 'GE-Power' AND GOCPD.COLUMN46 = NULL AND GOCPD.CONTRACT_ID = 525215; COMMIT; END; END; — Pasha Md, Jun 01 '18 at 06:38
I added your code in the comments in the question. Please don't include code as comments. It is not readable. You should edit your question to add code/data. — Kaushik Nayak, Jun 01 '18 at 06:51
You have not answered my queries in my comments. Please read my comment again and address each point if you need help.Thanks. — Kaushik Nayak, Jun 01 '18 at 06:55

score 0 · Answer 1 · answered Jun 01 '18 at 07:23

You have not provided enough details I requested and I cannot correlate your PL/SQL code with the data you provided.

I am assuming that you are simply interested to update the rows based on NULL values and then incrementing from the MAX(LINE_NUM). If yes, something like this should solve your purpose. If not, please add more details in your question.

MERGE INTO tx tgt USING (
    SELECT
        ROWID,
        contract_id,
        CASE
                WHEN line_num IS NULL THEN ROW_NUMBER() OVER(
                    PARTITION BY contract_id
                    ORDER BY
                        contract_id
                ) + MAX(line_num) OVER(
                    PARTITION BY contract_id
                ) - 1
                ELSE line_num
            END
        AS line_num
    FROM
        tx
)
src ON ( src.rowid = tgt.rowid )
WHEN MATCHED THEN UPDATE SET tgt.line_num = src.line_num;

SQL Fiddle Demo

plsql to fetch the max sequence line for every header and update the null values of the column with next sequence number for the particular header

1 Answers1