Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Apr 2010
    Posts
    12

    Unanswered: Dynamic Table name

    Hello All DB pros,

    Really sorry if I am not doing this the right way, I am almost newbie in these forums, may be first ever thread I am writting.

    I am trying to make a query for Oracle DB 10g. Table names are based on year and months. For example :

    abd_201004 for 2010 April
    abd_200909 for 2009 September

    SO lets say I want to run a query for current month, it would somewhat like this :

    SELECT sum(apple) FROM "query for table name of current month";

    Can anyone please help me ?

    Piles of thanks in advance.

    Ammar Ahmed

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The only way to work around your broken data model, is to write a stored procedure that takes the table name as an input parameter, constructs a string with the necessary SQL and then returns the result.

    The correct solution is be to store everything in one table and have columns that identify month and year. That will save you a lot of trouble in the long run.

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Ammar,

    the good news is, that there is a relatively trivial solution to your problem.

    You have to create a function:
    Code:
    CREATE OR REPLACE function GetSum(pTable varchar2) 
    return number 
    is
      vResult   number;
    begin
      execute immediate 'select sum(apple) from ' || upper(pTable)  into vResult;
      return vResult;
    end;
    /
    and call that function with i.e.:

    Code:
    select GetSum('abd_201004') from dual;
    The bad news is, that you are using a very bad design.
    Your data design is not relational and therefore most operations (like the one above) will be very inefficient in every relational database.

    I would strongly urge you, to redesign your data model and keep keep your data in ONE table by adding a column (i.e. create_date) for the date, so your query would read:

    Code:
    SELECT sum(apple) 
    FROM    abd
    WHERE  to_char(create_date,'YYYYMM') = '201004';
    Last edited by magicwand; 04-10-10 at 14:44.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  4. #4
    Join Date
    Apr 2010
    Posts
    12
    Thanks a lot for your reply Shammat.

    Magicwand.... unfortunately I dont have much understanding. Though I understand that function is kind of defined query in a nutshell, you hit that nutshell and you got the query. But still, if I am not wrong, I see that I have to use table name i.e.
    abc_201004 as you wrote in :

    select GetSum('abd_201004') from dual;

    If that is the case then its not what I need, I need a formula that can eliminate that name give me a general formula to get it.

    I already had a formula to get this latest months table, and that is :

    select max(table_name) from user_tables where table_name like 'abc_2%';

    I tried :

    SELECT sum(apple) from (select max(table_name) from user_tables where table_name like 'abc_2%');

    but that is logically and systematically giving that table name itself (not as a FROM option) .

    Though I really like this function thing, can I use it as FROM option ?


    Please help man.


    Ammar Ahmed

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ammar173 View Post
    Please help man.
    As magicwand and I have already told you: clean up your data model...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps dynamic SQL is what you are looking for. Here's an example based on Scott's schema.
    Code:
    SQL> declare
      2    l_tab varchar2(30);
      3    l_exe varchar2(500);
      4    l_sal number;
      5  begin
      6    select table_name
      7      into l_tab
      8      from user_tables
      9      where table_name like 'EMP%';
     10
     11    l_exe := 'select sum(sal) from ' || l_tab;
     12
     13    execute immediate (l_exe) into l_sal;
     14
     15    dbms_output.put_line(l_tab ||': '|| l_sal);
     16  end;
     17  /
    EMP: 29025
    
    PL/SQL procedure successfully completed.
    
    SQL>

  7. #7
    Join Date
    Apr 2010
    Posts
    12
    Honestly speak, guys, I am not brave enough to touch this db

    I know I must not be understanding what you guys can but all I want is a query as a FROM option.

    Ammar Ahmed

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What exactly did you not understand from my previous example?

    However, the fact (sad but true) is: your model sucks (from my current point of view). All data should be kept in a single table, and you'd then query one table (whose name is not a mystery), using any condition you want in SELECT's WHERE clause.

    What made you create such a monster?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ammar173 View Post
    Honestly speak, guys, I am not brave enough to touch this db

    I know I must not be understanding what you guys can but all I want is a query as a FROM option.

    Ammar Ahmed
    You cannot use the result of a SELECT statement as the table name of another SELECT statement. Not in pure SQL.

    The only way to do this with the DB you have is either Littlefoot's or magicwand's solution. Or create the whole SELECT statement inside your application's programming language.

    If you cannot change the definition of the tables, then you could still create a VIEW that does a UNION over all tables.

  10. #10
    Join Date
    Apr 2010
    Posts
    12
    OHHHH, Littlefoot, you are not little, man. I now understand what you guys mean by " BAD MODEL " LOL.

    OK, so people, yes I agree that it sucks, you guys can understand when i tell you that I have been having this for like last 2 hours really it sucks, but it is what it is. I had very very hard times figuring queries out of it and on top of it, I am not a pro, I am just a googler .

    OK, so, this is what it is. Please help me with something.

    Ammar Ahmed

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please help me with something.
    customize Littlefoot's example to meet your exact requirements.
    use sqlplus along with CUT & PASTE so we can see what you do & how Oracle responds
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Ammar
    Please help me with something
    No problem; what is "something"?

    Did you try to adjust my example to your situation?

    Did you try to create a view (as Shammat suggested) and move on? Note that this approach means that you'll need to (re)create a view by adding every new table, as it appears.

  13. #13
    Join Date
    Apr 2010
    Posts
    12
    Ahh, Union, yes that can help.

    OK, hear is an idea, what if I union a whole years table ( from January 2010 to December 2010 ) and then take out from it what I need. Say :

    SELECT sum(apple)
    FROM cdr_201001, cdr_201002, cdr_201003, cdr_201004, cdr_201005, cdr_201006, cdr_201007, cdr_201008, cdr_201009, cdr_201010, cdr_201011, cdr_201012
    WHERE TO_CHAR(cdr_date,'YYYYMM') like TO_CHAR(sysdate,'YYYYMM')
    ;

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, not that way.
    Code:
    create view v_cdr_2010 as
    select * from cdr_201001
    union all
    select * from cdr_201002
    union all
    ...
    select * from cdr_201012;
    
    select sum(apple)
      from v_cdr_2010
      where ...

  15. #15
    Join Date
    Apr 2010
    Posts
    12
    Well, anacedent, its all giving

    ORA-00942: table or view does not exist
    ORA-00933: SQL command not properly ended

    Ammar Ahmed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •