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!

improve procedure using pure sql (if possible)

Discussion in 'SQL PL/SQL' started by ecivgamer, May 18, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all, I'm newbie to PL/SQL. Please, be so kind, help me to improve procedure. I prefer pure sql, because as far as I know my Oracle 11 server allows it. Sure it's only brief example, actually I've got more than 300 variables, so it executes too long this way.
    Thanks ahead.

    Code (SQL):

    DECLARE
     
    var_ID VARCHAR2(20);
    var_LUAH NUMBER (16,2);
    var_LUSD NUMBER (16,2);
     
    BEGIN
     
        FOR rrk IN ( SELECT d.mydate FROM rep184_mediate#m d
                     ORDER BY d.mydate
        ) loop
     
        FOR rru IN (  SELECT * FROM
                     (SELECT DISTINCT d.contragentid FROM rep184_mediate_tmp_clnt d
                      WHERE d.contragentid IN (61404,61405)
                      ORDER BY d.contragentid )
        ) loop
     
    --var_ID
    SELECT TO_CHAR(TO_DATE(rrk.mydate),'YYYY') || TO_CHAR(TO_DATE(rrk.mydate),'MM') || TO_CHAR(TO_DATE(rrk.mydate),'DD') || '.' || rru.contragentid AS mykey
    INTO var_ID
    FROM dual;
     
    --var_LUAH
    SELECT nvl(SUM(b.summanow)/100,0) INTO var_LUAH FROM arc_balance b, aaccount d WHERE
    d.baccountid IN (2010,2020,2030,2062,2063,2072,2073,2074,2082,2083,2102,2103,2112,2113,2122,
    2123,2132,2133,2202,2203,2220,2232,2233)
    AND b.arcdate = to_date(rrk.mydate, 'DD.MM.YYYY')
    AND d.contragentid = rru.contragentid
    AND b.corrmode IN (0,2) --bez kor.provodok
    AND d.currencyid = 980
    AND d.id = b.accountid;
     
    --LUSD
    SELECT nvl(SUM(b.summanow)/100,0) INTO var_LUSD FROM dbe_balance b, dbeaccount d WHERE
    d.baccountid IN (2610,2635)
    AND b.arcdate = to_date(rrk.mydate, 'DD.MM.YYYY')
    AND d.contragentid = rru.contragentid
    AND b.corrmode IN (0,2) --bez kor.provodok
    AND d.currencyid = 840
    AND d.id = b.accountid;
     
    INSERT INTO rep184_mediate VALUES (var_ID,rrk.mydate,rru.contragentid,var_LUAH, var_LUSD);
        END loop;
        END loop;
    END;
     
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    This case "from dual" can be useful for you to help me:

    Code (SQL):
    DECLARE
     
    var_ID VARCHAR2(20);
    var_LPND VARCHAR2(20);
    var_LUSD VARCHAR2(20);
    var_Lkk VARCHAR2(20);
     
    BEGIN
     
        FOR rrk IN ( SELECT '01112010' AS mydate FROM dual d
                     UNION ALL
                     SELECT '01122010' AS mydate FROM dual d
        ) loop
     
        FOR rru IN (  SELECT '33' contragentid FROM dual d
                      UNION ALL  
                      SELECT '56' contragentid FROM dual d
        ) loop
     
    --var_ID
    SELECT substr(rrk.mydate,4,2) || '.' || rru.contragentid AS mykey
    INTO var_ID
    FROM dual;
     
    --var_LPND
    SELECT COUNT(*) AS q INTO var_LPND FROM (SELECT '01112010' AS arcdate, '33' AS contragentid, 'tt' AS t FROM dual
                                        UNION ALL
                                        SELECT '01122010' AS arcdate, '56' AS contragentid, 'un' AS t FROM dual
                                        UNION ALL
                                        SELECT '01122010' AS arcdate, '33' AS contragentid, 'kp' AS t FROM dual
                                        UNION ALL
                                        SELECT '01112010' AS arcdate, '56' AS contragentid, 'ur' AS t FROM dual) b
    WHERE b.arcdate = rrk.mydate
      AND b.contragentid = rru.contragentid;
     
    --val_LUSD
    SELECT COUNT(*) AS q INTO var_LUSD FROM (SELECT '01112010' AS arcdate, '33' AS contragentid, 'dr' AS y FROM dual
                                        UNION ALL
                                        SELECT '01122010' AS arcdate, '56' AS contragentid, 'rh' AS y FROM dual
                                        UNION ALL
                                        SELECT '01122010' AS arcdate, '33' AS contragentid, 'tr' AS y FROM dual
                                        UNION ALL
                                        SELECT '01112010' AS arcdate, '56' AS contragentid, 'wn' AS y FROM dual) d
    WHERE d.arcdate = rrk.mydate
    AND d.contragentid = rru.contragentid;

    --val_Lkk
    SELECT COUNT(*)+5 AS q INTO var_Lkk FROM (SELECT '555' AS kkt FROM dual) v ;
     
    INSERT INTO marco_ttmp VALUES (var_ID,rrk.mydate,rru.contragentid,var_LPND,var_LUSD,var_Lkk);
        END loop;
        END loop;
    END;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not knowing what you're trying to accomplish makes it difficult to provide an 'answer'. You provide code with no explanation; what is this process? What is the purpose of the code? Why do you have over 300 variables?

    You really need to explain this so all can understand.
     
  4. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi zargon, the purpose is to generate report, it's daily financial indicators (300 pieces) of customers. Indicators should be calculated using different source tables and gathered in result table.

    Hope it helps.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not really; where do these 300 pieces of data come from? Are they values from a spreadsheet? Do they reside in a database table? Do they simply appear out of thin air?
     
  6. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    These 300 pieces of data are different, some of them do not reside in a database tables, but should be calculated first, others do reside in source tables.
    For example, Total_Commission per month is calculated as sum of Commision per each day. So the source table is balance_t.
    User_Code is username of employee (of last open account). So the source table is account_t.

    I'm ready to answer other your questions.
     
  7. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    I've got decision, maybe you want to see.

    Code (SQL):


    SQL> DECLARE
      2  
      3  var_ID VARCHAR2(20);
      4  var_LPND VARCHAR2(20);
      5  var_LUSD VARCHAR2(20);
      6  var_Lkk VARCHAR2(20);
      7  
      8  BEGIN
      9  
     10      FOR rrk IN ( SELECT '01112010' AS mydate FROM dual d
     11                   UNION ALL
     12                   SELECT '01122010' AS mydate FROM dual d
     13      ) loop
     14  
     15      FOR rru IN (  SELECT '33' contragentid FROM dual d
     16                    UNION ALL  
     17                    SELECT '56' contragentid FROM dual d
     18      ) loop
     19  
     20  --var_ID
     21  SELECT substr(rrk.mydate,4,2) || '.' || rru.contragentid AS mykey
     22  INTO var_ID
     23  FROM dual;
     24  
     25  --var_LPND
     26  SELECT COUNT(*) AS q INTO var_LPND FROM (SELECT '01112010' AS arcdate, '33' AS contragentid, 'tt' AS t FROM dual
     27                                      UNION ALL
     28                                      SELECT '01122010' AS arcdate, '56' AS contragentid, 'un' AS t FROM dual
     29                                      UNION ALL
     30                                      SELECT '01122010' AS arcdate, '33' AS contragentid, 'kp' AS t FROM dual
     31                                      UNION ALL
     32                                      SELECT '01112010' AS arcdate, '56' AS contragentid, 'ur' AS t FROM dual) b
     33  WHERE b.arcdate = rrk.mydate
     34    AND b.contragentid = rru.contragentid;
     35  
     36  --val_LUSD
     37  SELECT COUNT(*) AS q INTO var_LUSD FROM (SELECT '01112010' AS arcdate, '33' AS contragentid, 'dr' AS y FROM dual
     38                                      UNION ALL
     39                                      SELECT '01122010' AS arcdate, '56' AS contragentid, 'rh' AS y FROM dual
     40                                      UNION ALL
     41                                      SELECT '01122010' AS arcdate, '33' AS contragentid, 'tr' AS y FROM dual
     42                                      UNION ALL
     43                                      SELECT '01112010' AS arcdate, '56' AS contragentid, 'wn' AS y FROM dual) d
     44  WHERE d.arcdate = rrk.mydate
     45  AND d.contragentid = rru.contragentid;
     46  
     47  --val_Lkk
     48  SELECT COUNT(*)+5 AS q INTO var_Lkk FROM (SELECT '555' AS kkt FROM dual) v ;
     49  
     50  dbms_output.put_line (var_ID||' '||rrk.mydate||' '||rru.contragentid||' '||var_LPND||' '||var_LUSD||' '||var_Lkk);
     51      END loop;
     52      END loop;
     53  END;
     54  /
    12.33 01112010 33 1 1 6
    12.56 01112010 56 1 1 6
    22.33 01122010 33 1 1 6
    22.56 01122010 56 1 1 6

    PL/SQL PROCEDURE successfully completed.

    SQL> WITH
      2    rrk AS (
      3      SELECT '01112010' AS mydate FROM dual d
      4      UNION ALL
      5      SELECT '01122010' AS mydate FROM dual d
      6    ),
      7    rru AS (
      8      SELECT '33' contragentid FROM dual d
      9      UNION ALL  
     10      SELECT '56' contragentid FROM dual d
     11    ),
     12    b AS (
     13      SELECT '01112010' AS arcdate, '33' AS contragentid, 'tt' AS t FROM dual
     14      UNION ALL
     15      SELECT '01122010' AS arcdate, '56' AS contragentid, 'un' AS t FROM dual
     16      UNION ALL
     17      SELECT '01122010' AS arcdate, '33' AS contragentid, 'kp' AS t FROM dual
     18      UNION ALL
     19      SELECT '01112010' AS arcdate, '56' AS contragentid, 'ur' AS t FROM dual
     20    ),
     21    d AS (
     22      SELECT '01112010' AS arcdate, '33' AS contragentid, 'dr' AS y FROM dual
     23      UNION ALL
     24      SELECT '01122010' AS arcdate, '56' AS contragentid, 'rh' AS y FROM dual
     25      UNION ALL
     26      SELECT '01122010' AS arcdate, '33' AS contragentid, 'tr' AS y FROM dual
     27      UNION ALL
     28      SELECT '01112010' AS arcdate, '56' AS contragentid, 'wn' AS y FROM dual
     29    ),
     30    v AS ( SELECT '555' AS kkt FROM dual ),
     31    kkt AS ( SELECT COUNT(*)+5 kkt FROM v )
     32  SELECT substr(rrk.mydate,4,2) || '.' || rru.contragentid var_id,
     33         rrk.mydate,
     34         rru.contragentid,
     35         COUNT(b.arcdate) var_lpnd,
     36         COUNT(d.arcdate) var_lusd,
     37         kkt.kkt var_lkk
     38  FROM kkt CROSS JOIN rrk CROSS JOIN rru
     39       LEFT OUTER JOIN b ON b.arcdate = rrk.mydate AND b.contragentid = rru.contragentid
     40       LEFT OUTER JOIN d ON d.arcdate = rrk.mydate AND d.contragentid = rru.contragentid
     41  GROUP BY rrk.mydate, rru.contragentid, kkt.kkt
     42  /
    VAR_I MYDATE   CO   VAR_LPND   VAR_LUSD    VAR_LKK
    ----- -------- -- ---------- ---------- ----------
    22.33 01122010 33          1          1          6
    12.33 01112010 33          1          1          6
    12.56 01112010 56          1          1          6
    22.56 01122010 56          1          1          6

    4 ROWS selected.

     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Nice work; how does it scale to having 300 values?
     
  9. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Zargon, actually I can't find answer yet.