Select-ing from variable number of tables

teterin · #1 (**permalink**) 10-17-05, 12:33

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!

Littlefoot · #2 (**permalink**) 10-17-05, 14:28

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech