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!

New query question!!Help!!

Discussion in 'SQL PL/SQL' started by virusx1984, Feb 9, 2011.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    There is a table like:
    Table:x

    PN|Feb_1|Feb_2|Feb_3|Feb_4
    A|2|3|4|5
    B|3|4|5|6

    The result table I wanted is like:

    PN|Feb_1|Feb_2|Feb_3|Feb_4|Total
    A|2|3|4|5|14
    B|3|4|5|6|18

    The column "Total" is the summary of columns(Feb_1,Feb_2,Feb_3,Feb_4).

    Is there a sql text that can accomplish that? I mean not to add it one by one such as "select pn,Feb_1,Feb_2,Feb_3,Feb_4,(Feb_1+Feb_2+Feb_3+Feb_4) Total from A"

    I wonder if there is a function that can add columns value by giving the column id.
    "sum(2 to 4)" or something like that.
    I just what to offer the column ids.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No.

    No RDBMS gives the functionality like this. Because all the functions are based on expression. There is no column id based functions.

    Why do you want to do that ? You can create a view instead.
     
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Because when the number of the columns is large 20 for example , I have to write "(Feb_1+Feb_2+Feb_3 ... + Feb_20) Total".
    What if the number is 100 or even more?
     
  4. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Because when the number of the columns is large 20 for example , I have to write "(Feb_1+Feb_2+Feb_3 ... + Feb_20) Total".
    What if the number is 100 or even more?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then your table design is flawed and needs revision. Why would you create columns named after specific dates? Why not have a table with a PROCESS_DT column, store the date there, then sum the column containing production/inventory data based upon the date values? You can always pivot the table to produce a similar view to your current table definition by using decode, case or pivot (in 11g).
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    yes. it is a bad design.

    Anyway you have a solution ahead. But you have to implement at your ow risk. Beware of performance issues. :)

    Code (SQL):

    SQL> SELECT S.*  FROM FEB_SALE S;

    ITEM_CODE  FEB_01 FEB_02 FEB_03 FEB_04 FEB_05 FEB_06 FEB_07 FEB_08 FEB_09 FEB_10 FEB_11 FEB_12 FEB_13 FEB_14 FEB_15
    ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
    0001          100    299     78     76     86     54    665     43     33    322    232    676     45    334     76
    0002          891     68     78     67     78    709     78    454     56     99    232    789     77    600    787
    0003          578    876     78     76     57     55     80     80     87     67     44    343     33      3      3

    SQL> CREATE OR REPLACE FUNCTION GET_FEB_SALE (item VARCHAR2)
      2  RETURN NUMBER
      3  AS
      4  sale_amt    NUMBER(10,2)  := -1;
      5  sql_str     varchar2(4000):= 'SELECT ';
      6  BEGIN
      7
      8      FOR col IN (SELECT column_name
      9                  FROM        user_tab_columns
     10                  WHERE  TABLE_NAME = 'FEB_SALE'
     11                  AND         column_name LIKE 'FEB%')
     12      LOOP
     13
     14          sql_str := sql_str ||col.column_name||'+';
     15
     16      END LOOP;
     17
     18      sql_str := RTRIM(sql_str,'+')||' FROM FEB_SALE WHERE ITEM_CODE=:1';
     19
     20      DBMS_OUTPUT.PUT_LINE (sql_str);
     21
     22      BEGIN
     23
     24      EXECUTE IMMEDIATE sql_str INTO sale_amt USING item ;
     25
     26      EXCEPTION
     27      WHEN OTHERS THEN
     28          NULL;
     29      END;
     30
     31      RETURN sale_amt;
     32
     33  END;
     34  /

    FUNCTION created.

    SQL> SELECT S.*,GET_FEB_SALE(S.ITEM_CODE) TOTAL_SALE FROM FEB_SALE S;

    ITEM_CODE  FEB_01 FEB_02 FEB_03 FEB_04 FEB_05 FEB_06 FEB_07 FEB_08 FEB_09 FEB_10 FEB_11 FEB_12 FEB_13 FEB_14 FEB_15 TOTAL_SALE
    ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ----------
    0001          100    299     78     76     86     54    665     43     33    322    232    676     45    334     76       3119
    0002          891     68     78     67     78    709     78    454     56     99    232    789     77    600    787       5063
    0003          578    876     78     76     57     55     80     80     87     67     44    343     33      3      3       2460

    SQL>

     
     
  7. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Actually,I have designed the table as you told me.And It looks like:
    PN|Date|mount
    A|Feb_1|2
    A|Feb_2|3
    A|Feb_3|4
    A|Feb_4|5
    B|Feb_1|3
    B|Feb_2|4
    B|Feb_3|5
    B|Feb_4|6

    I wanted to get view like:
    PN|Feb_1|Feb_2|Feb_3|Feb_4|Total
    A|2|3|4|5|14
    B|3|4|5|6|18

    First, I used "case when" (that you taught me) to create a table like:
    PN|Feb_1|Feb_2|Feb_3|Feb_4
    A|2|3|4|5
    B|3|4|5|6

    And then , I don't know .. .So I came here and asked if anyone can help me.
     
  8. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Oh,I know.

    Code (SQL):
    SELECT *
      FROM (SELECT pn,
                   SUM(CASE WHEN DATE = 'Feb_1' THEN mount END) Feb_1,
                   SUM(CASE WHEN DATE = 'Feb_2' THEN mount END) Feb_2,
                   SUM(CASE WHEN DATE = 'Feb_3' THEN mount END) Feb_3,
                   SUM(CASE WHEN DATE = 'Feb_4' THEN mount END) Feb_4,
              FROM table_1
             GROUP BY pn) a,
           (SELECT pn, SUM(mount) Total
              FROM table_1
             GROUP BY pn
             WHERE DATE >= Feb_1
               AND DATE <= Feb_4) b
     WHERE a.pn = b.pn
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why maling Date as varchar2. Always keet date as date. otherwise you will face a lot of issues like this.

    Code (SQL):

    SQL> SELECT * FROM MDATE;

    MDATE
    ----------
    Feb_1
    Feb_2
    Feb_3
    Feb_4
    Feb_5
    Feb_6
    Feb_7
    Feb_8
    Feb_9
    Feb_10
    Feb_11
    Feb_12
    Feb_13
    Feb_14
    Feb_15
    Feb_16
    Feb_17
    Feb_18
    Feb_19
    Feb_20

    20 ROWS selected.

    SQL> SELECT * FROM MDATE WHERE MDATE >='Feb_1' AND MDATE<='Feb_20';

    MDATE
    ----------
    Feb_1
    Feb_2
    Feb_10
    Feb_11
    Feb_12
    Feb_13
    Feb_14
    Feb_15
    Feb_16
    Feb_17
    Feb_18
    Feb_19
    Feb_20

    13 ROWS selected.

    SQL>  SELECT * FROM MDATE WHERE MDATE >='Feb_5' AND  MDATE<='Feb_10';

    no ROWS selected

    SQL>
     
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also you can simplify the query as below.

    Code (SQL):

    SELECT PN,
           SUM(CASE WHEN TRUNC(MDATE) = TO_DATE('01-FEB-2011','DD-MON-YYYY') THEN AMOUNT END) FEB_1,
           SUM(CASE WHEN TRUNC(MDATE) = TO_DATE('02-FEB-2011','DD-MON-YYYY') THEN AMOUNT END) FEB_2,
           SUM(CASE WHEN TRUNC(MDATE) = TO_DATE('03-FEB-2011','DD-MON-YYYY') THEN AMOUNT END) FEB_3,
           SUM(CASE WHEN TRUNC(MDATE) = TO_DATE('04-FEB-2011','DD-MON-YYYY') THEN AMOUNT END) FEB_4,
          (SELECT SUM(AMOUNT)  
             FROM TABLE_1 T12
            WHERE T12.PN = T11.PN
              AND TRUNC(MDATE) >= TO_DATE('01-FEB-2011','DD-MON-YYYY')
              AND TRUNC(MDATE) <= TO_DATE('04-FEB-2011','DD-MON-YYYY')) TOTAL
     FROM TABLE_1 T11
    GROUP BY PN
     
     
  11. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    thanks rajavu. Actually, the type of Date of my table is not varchar2. It's date type.

    I just want to make the example look more simple and we can focus on how to create a view which has the Total column.
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    ok. Hope you got the answer. Otherwise, are you looking for something different ?