Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Unanswered: Select-ing from variable number of tables

    Hello!

    I'd like to select the maximum value of a certain column (let's call it `X_IDNTY'), from every table, that has such a column. Is such a thing possible with SQL (perhaps, Sybase and/or Oracle flavors?), or do I need to write external program to get the list of tables first and construct the query outside?

    Something like:

    TABLE_NAME max(X_IDNTY)
    ------------ --------------
    ................. ...........

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In Oracle, you might use dynamic SQL, such as in this PL/SQL procedure:
    Code:
    SET SERVEROUTPUT ON SIZE 1000000;
    
    DECLARE
       CURSOR c1
       IS
          SELECT table_name
            FROM user_tab_columns
           WHERE column_name = 'VT';
    
       l_var    VARCHAR2 (1024);
       max_vt   ocitanje.vt%TYPE;
    BEGIN
       FOR c1r IN c1
       LOOP
          l_var := 'SELECT MAX(vt) FROM ' || c1r.table_name;
    
          EXECUTE IMMEDIATE l_var INTO max_vt;
    
          DBMS_OUTPUT.put_line (c1r.table_name || ': ' || max_vt);
       END LOOP;
    END;

Posting Permissions

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