1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to find missing data (NULL) by column value and a row and update it gap filling

Discussion in 'SQL PL/SQL' started by Angappan, Aug 27, 2009.

  1. Angappan

    Angappan Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I have a doubt here.

    Scenario

    I have a table with constants id batch_id, batch_date and batch_id and other columns from
    A, B,C,D,E........Z.


    Here some data might be null and some row might be missed. I want to find out which data is missed it any columns and which row is missed .

    I have to fill the missed data and missed row by corresponding batch_id,batch_date and batch_hour with previous week data if found(Today is Monday means i have to look the last week monday) ex: (out.date -in.date )=7


    For missed row, I planned to check the hours ....

    1, 2,3,4,5,5,6,7,8,8,9,11,12,13,14,,,,,,,,,,,24..
    Sometimes hour may repeat 5,5 and 8,8 but missed here is 10
    hour - previous record hour =1 means record available
    >2 record missed
    =0 means record repeated for same hour.


    I done some pseudo code for this one..
    I need it to dynamic tablename and dynamic columns.If dynamic means i can go with user_tabs_columns but how can i check the batch_id, batch_date and batch_hour.

    I facing some issue in row checking and updating.


    pseudo code:
    Code (SQL):

    CREATE OR REPLACE PROCEDURE missingdata_gapfilling
    AS
    BEGIN
       DECLARE
          CURSOR c2
          IS
             SELECT dc_out.batch_id batch_id_out, dc_out.a a_out, dc_in.a a_in,
                    dc_out.b b_out, dc_in.b b_in, dc_out.c c_out, dc_in.c c_in,
                    dc_out.d d_out, dc_in.d d_in, dc_out.e e_out, dc_in.e e_in,
                    dc_out.f f_out, dc_in.f f_in, dc_out.g g_out, dc_in.g g_in,
                    dc_out.h h_out, dc_in.h h_in, dc_out.i i_out, dc_in.i i_in,
                    dc_out.j j_out, dc_in.j j_in, dc_out.k k_out, dc_in.k k_in,
                    dc_out.l l_out, dc_in.l l_in, dc_out.m m_out, dc_in.m m_in,
                    dc_out.n n_out, dc_in.n n_in, dc_out.o o_out, dc_in.o o_in,
                    dc_out.p p_out, dc_in.p p_in, dc_out.q q_out, dc_in.q q_in,
                    dc_out.r r_out, dc_in.r r_in, dc_out.s s_out, dc_in.s s_in,
                    dc_out.t t_out, dc_in.t t_in, dc_out.u u_out, dc_in.u u_in,
                    dc_out.v v_out, dc_in.v v_in, dc_out.w w_out, dc_in.w w_in,
                    dc_out.x x_out, dc_in.x x_in, dc_out.y y_out, dc_in.y y_in,
                    dc_out.z z_out, dc_in.z z_in,
                    dc_out.batch_hour batch_hour_out,
                    dc_in.batch_hour batch_hour_in,
                    (dc_out.batch_date - dc_in.batch_date) diff,
                    dc_out.batch_date batch_date_out,
                    dc_in.batch_date batch_date_in
               FROM det_col_test dc_out INNER JOIN det_col_test dc_in
                    ON dc_out.batch_id = dc_in.batch_id
                  AND (dc_out.batch_date - dc_in.batch_date) = 7
                  AND ((dc_out.batch_hour - dc_in.batch_hour - 1) > 1)
                  AND (    dc_out.a IS NULL
                       AND dc_out.b IS NULL
                       AND dc_out.c IS NULL
                       AND dc_out.d IS NULL
                       AND dc_out.e IS NULL
                       AND dc_out.f IS NULL
                       AND dc_out.g IS NULL
                       AND dc_out.h IS NULL
                       AND dc_out.i IS NULL
                       AND dc_out.j IS NULL
                       AND dc_out.k IS NULL
                       AND dc_out.l IS NULL
                       AND dc_out.m IS NULL
                       AND dc_out.n IS NULL
                       AND dc_out.o IS NULL
                       AND dc_out.p IS NULL
                       AND dc_out.q IS NULL
                       AND dc_out.r IS NULL
                       AND dc_out.s IS NULL
                       AND dc_out.t IS NULL
                       AND dc_out.u IS NULL
                       AND dc_out.v IS NULL
                       AND dc_out.w IS NULL
                       AND dc_out.x IS NULL
                       AND dc_out.y IS NULL
                       AND dc_out.z IS NULL
                      )
                    ;
       BEGIN
          FOR i IN c2
          LOOP
             DBMS_OUTPUT.put_line (i.a_in);
             DBMS_OUTPUT.put_line (i.b_in);
             DBMS_OUTPUT.put_line (i.batch_id_out);
             DBMS_OUTPUT.put_line (i.batch_date_out);
             DBMS_OUTPUT.put_line (i.batch_hour_out);

             --DBMS_OUTPUT.put_line ('END');
             UPDATE det_col_test
                SET a = DECODE (a, NULL, i.a_in, a),
                    b = DECODE (b, NULL, i.b_in, b),
                    c = DECODE (c, NULL, i.c_in, c),
                    d = DECODE (d, NULL, i.d_in, d),
                    e = DECODE (e, NULL, i.e_in, e),
                    f = DECODE (f, NULL, i.f_in, f),
                    g = DECODE (g, NULL, i.g_in, g),
                    h = DECODE (h, NULL, i.h_in, h),
                    i = DECODE (i, NULL, i.i_in, i),
                    j = DECODE (j, NULL, i.j_in, j),
                    k = DECODE (k, NULL, i.k_in, k),
                    l = DECODE (l, NULL, i.l_in, l),
                    m = DECODE (m, NULL, i.m_in, m),
                    n = DECODE (n, NULL, i.n_in, n),
                    o = DECODE (o, NULL, i.o_in, o),
                    p = DECODE (p, NULL, i.p_in, p),
                    q = DECODE (q, NULL, i.q_in, q),
                    r = DECODE (r, NULL, i.r_in, r),
                    s = DECODE (s, NULL, i.s_in, s),
                    t = DECODE (t, NULL, i.t_in, t),
                    u = DECODE (u, NULL, i.u_in, u),
                    v = DECODE (v, NULL, i.v_in, v),
                    w = DECODE (w, NULL, i.w_in, w),
                    x = DECODE (x, NULL, i.x_in, x),
                    y = DECODE (y, NULL, i.y_in, y),
                    z = DECODE (z, NULL, i.z_in, z),
                    update_date = SYSDATE
              WHERE batch_id = i.batch_id_out
                AND batch_date = i.batch_date_out
                AND batch_hour = i.batch_hour_out;

             COMMIT;
          END LOOP;
       END;
    END;

    I want to make it dynamic columns and check it for row and update row.

    test data
    Code (SQL):

    CREATE TABLE det_col_test
    (   batch_id NUMBER, batch_date DATE, batch_hour NUMBER,
        a VARCHAR2(10), b VARCHAR2(10), c VARCHAR2(10),
        d VARCHAR2(10), e VARCHAR2(10), f VARCHAR2(10),
        g VARCHAR2(10), h VARCHAR2(10), i VARCHAR2(10),
        j VARCHAR2(10), k VARCHAR2(10), l VARCHAR2(10),
        m VARCHAR2(10), n VARCHAR2(10), o VARCHAR2(10),
        p VARCHAR2(10), q VARCHAR2(10), r VARCHAR2(10),
        s VARCHAR2(10), t VARCHAR2(10), u VARCHAR2(10),
        v VARCHAR2(10), w VARCHAR2(10), x VARCHAR2(10),
        y VARCHAR2(10), z VARCHAR2(10)
    );

    ALTER TABLE det_col_test ADD (update_date DATE);

    INSERT INTO det_col_test
                (batch_id,
                 batch_date,
                 batch_hour, a, b, c, d, e, f, g, h, i,
                 j, k, l, m, n, o, p, q, r, s,
                 t, u, v, w, x, y, z, update_date
                )
         VALUES (10,
                 TO_DATE ('08/15/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
                 10, 'FIRST', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date,
                 batch_hour, a, b, c, d, e, f, g, h, i,
                 j, k, l, m, n, o, p, q, r, s,
                 t, u, v, w, x, y, z, update_date
                )
         VALUES (10,
                 TO_DATE ('08/10/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
                 10, 'SECOND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date, batch_hour,
                 a, b, c, d, e, f, g, h, i, j,
                 k, l, m, n, o, p, q, r, s, t,
                 u, v, w, x, y, z,
                 update_date
                )
         VALUES (20,
                 TO_DATE ('08/15/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 3,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL,
                 TO_DATE ('08/25/2009 12:45:53 PM', 'MM/DD/YYYY HH:MI:SS AM')
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date,
                 batch_hour, a, b, c, d, e, f, g, h, i, j,
                 k, l, m, n, o, p, q, r, s, t,
                 u, v, w, x, y, z,
                 update_date
                )
         VALUES (10,
                 TO_DATE ('08/25/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
                 10, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL,
                 TO_DATE ('08/25/2009 12:45:53 PM', 'MM/DD/YYYY HH:MI:SS AM')
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date, batch_hour,
                 a, b, c, d, e, f, g, h, i, j,
                 k, l, m, n, o, p, q, r, s, t,
                 u, v, w, x, y, z, update_date
                )
         VALUES (20,
                 TO_DATE ('08/05/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 3,
                 NULL, 'THIRD', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date, batch_hour,
                 a, b, c, d, e, f, g, h, i, j,
                 k, l, m, n, o, p, q, r, s, t,
                 u, v, w, x, y, z, update_date
                )
         VALUES (30,
                 TO_DATE ('08/04/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 4,
                 NULL, NULL, 'FOURTH', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL
                );
    INSERT INTO det_col_test
                (batch_id,
                 batch_date, batch_hour,
                 a, b, c, d, e, f, g, h, i, j,
                 k, l, m, n, o, p, q, r, s, t,
                 u, v, w, x, y, z,
                 update_date
                )
         VALUES (30,
                 TO_DATE ('08/14/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 4,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, NULL, NULL, NULL,
                 TO_DATE ('08/25/2009 05:43:15 PM', 'MM/DD/YYYY HH:MI:SS AM')
                );
    COMMIT ;



    Thanks
    :)
     
  2. Angappan

    Angappan Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Re: How to find missing data (NULL) by column value and a row and update it gap filli

    Kindly tell me how can Make this one as dynamic I tried but logic is missing . Kindly tell me