How to generate 2 range Parallelly?-CodePudding

I want to generate incremental numbers from given range and insert into table. Below is screenshot of my temp table in which both the two range are available.

as you see in above screenshot I have two range. Now what I want that I want to create range with incremental number for both the range and insert into two column in another table with one to one mapping.

From below code I am successfully generating one range from two columns.

FOR i IN (SELECT TO_NUMBER(column_value)  range_value FROM XMLTABLE(lc_frm_srl || ' to ' || lc_to_srl))
LOOP
            
      insert into SML.temp_mtr_cca( MTR_SRL_NO)
             values(lc_prefix || i.range_value);
            
END LOOP;
        
commit;

e.g. expected result.

X1673740 - XF179320

X1673741 - XF179321

X1673742 - XF179322

CodePudding user response：

you need to have an equal range value for this solution. I hope this sample code be helpful.

DECLARE
    first_series_prefix  VARCHAR2(50);
    first_series_start   NUMBER;
    second_series_start  NUMBER;
    second_series_prefix VARCHAR2(50);
    range_value          NUMBER;
BEGIN
    first_series_prefix := 'X';
    first_series_start := 1673740;
    second_series_start := 179320;
    second_series_prefix := 'XF';
    range_value := 20;
    
INSERT INTO temp_mtr_cca ( combined_serials )
        SELECT
            first_series_prefix
            || first_series.first_serials
            || ' - '
            || second_series_prefix
            || second_series.second_serials final_result
        FROM
            (
                SELECT
                    ROWNUM rwn,
                    a      first_serials
                FROM
                    (
                        SELECT
                            first_series_start   level - 1 a
                        FROM
                            dual
                        CONNECT BY
                            level <= range_value
                    )
            ) first_series
            LEFT OUTER JOIN (
                SELECT
                    ROWNUM rwn,
                    a      second_serials
                FROM
                    (
                        SELECT
                            second_series_start   level - 1 a
                        FROM
                            dual
                        CONNECT BY
                            level <= range_value
                    )
            ) second_series ON first_series.rwn = second_series.rwn;

END;

CodePudding user response：

Your code cannot work with 'X1673740' to 'X1673760', because these are strings, but you need numbers that must not start with an 'X': '1673740' to '1673760'.

So, I assume your temp table actually contains these numbers instead of strings starting with 'X'. We can later add the 'X', once we generated the numbers (which you seem to be doing in your query already). Same for the 'XF' of your second number range.

One thing to note: The ending_modem_srl column is of no interest here, because the ranges must have the same length anyway to have this working, so let's use the length of starting_mtr_srl to ending_mtr_srl.

Once we have the mtr numbers, we can easily get the modem numbers, because modem_srl_no = mtr_srl_no starting_modem_srl - starting_mtr_srl, e.g. 79322 = 1673742 79320 - 1673740.

INSERT INTO sml.temp_mtr_cca(mtr_srl_no, modem_srl_no)
SELECT
  'X'   TO_CHAR(XMLCAST(column_value AS NUMBER)),
  'XF'   TO_CHAR(XMLCAST(column_value AS NUMBER)   t.starting_modem_srl - t.starting_mtr_srl)
FROM temp_table t
CROSS APPLY XMLTABLE(starting_mtr_srl || ' to ' || ending_mtr_srl);

COMMIT;