Home » Other » Client Tools » Re: Finding Largest Among Matrix
Re: Finding Largest Among Matrix [message #25944] Wed, 28 May 2003 01:44 Go to next message
Chidambar Rajpurohit
Messages: 8
Registered: May 2003
Junior Member
Thanx guyes, Now im facing a other problem, I wanted to write a procedure which will take the Table name as Parameter, bcaz in my applications im ving multiple tables like that .. So can we write a generic procedure which will give me the maximum value instead of mentioning column name

Thanks in Advance..
Re: Finding Largest Among Matrix [message #25953 is a reply to message #25944] Wed, 28 May 2003 07:25 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
quote from Chidambar Rajpurohit:
----------------------------------------------------------------------
Thanx guyes, Now im facing a other problem, I wanted to write a procedure which will take the Table name as Parameter, bcaz in my applications im ving multiple tables like that .. So can we write a generic procedure which will give me the maximum value instead of mentioning column name

Thanks in Advance..

----------------------------------------------------------------------
SQL> CREATE TABLE t1 (a NUMBER, b VARCHAR2(5), c NUMBER, d NUMBER);
  
Table created.
  
SQL> INSERT INTO t1
  2  SELECT ROWNUM
  3  ,      TO_CHAR(ROWNUM * 100)
  4  ,      ROWNUM * 4
  5  ,      ROWNUM * 2
  6  FROM   sys.all_users
  7  WHERE  ROWNUM <= 15;
  
15 rows created.
  
SQL> COMMIT;
  
Commit complete.
  
SQL> CREATE TABLE t2 (
  2      col1    NUMBER
  3  ,   col2    NUMBER
  4  ,   col3    NUMBER
  5  ,   col4    NUMBER
  6  ,   col5    NUMBER
  7  ,   col6    NUMBER
  8  ,   col7    NUMBER
  9  ,   col8    NUMBER
 10  ,   col9    NUMBER
 11  ,   col10   NUMBER
 12  );
  
Table created.
  
SQL> INSERT INTO t2 VALUES (27 ,50 ,14 ,45 ,92 ,65 ,95 ,37 ,57 ,24);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (72 ,20 ,94 ,18 ,63 ,83 ,55 ,52 ,74 ,43);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (73 ,49 ,54 ,64 ,8 ,44 ,60 ,78 ,38 ,67);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (33 ,1 ,69 ,30 ,29 ,10 ,6 ,11 ,62 ,88);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (21 ,41 ,34 ,76 ,51 ,82 ,96 ,3 ,58 ,99);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (84 ,23 ,40 ,7 ,91 ,66 ,17 ,75 ,25 ,70);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (15 ,97 ,98 ,47 ,31 ,85 ,71 ,46 ,26 ,32);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (81 ,9 ,59 ,4 ,5 ,19 ,93 ,61 ,39 ,28);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (42 ,80 ,87 ,53 ,77 ,13 ,90 ,86 ,89 ,2);
  
1 row created.
  
SQL> INSERT INTO t2 VALUES (48 ,56 ,68 ,35 ,12 ,16 ,79 ,22 ,36 ,0);
  
1 row created.
  
SQL> COMMIT;
  
Commit complete.
  
SQL> SELECT * FROM t1;
  
         A B              C          D
---------- ----- ---------- ----------
         1 100            4          2
         2 200            8          4
         3 300           12          6
         4 400           16          8
         5 500           20         10
         6 600           24         12
         7 700           28         14
         8 800           32         16
         9 900           36         18
        10 1000          40         20
        11 1100          44         22
        12 1200          48         24
        13 1300          52         26
        14 1400          56         28
        15 1500          60         30
  
15 rows selected.
  
SQL> SELECT * FROM t2;
  
      COL1       COL2       COL3       COL4       COL5       COL6       COL7       COL8       COL9      COL10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        27         50         14         45         92         65         95         37         57         24
        72         20         94         18         63         83         55         52         74         43
        73         49         54         64          8         44         60         78         38         67
        33          1         69         30         29         10          6         11         62         88
        21         41         34         76         51         82         96          3         58         99
        84         23         40          7         91         66         17         75         25         70
        15         97         98         47         31         85         71         46         26         32
        81          9         59          4          5         19         93         61         39         28
        42         80         87         53         77         13         90         86         89          2
        48         56         68         35         12         16         79         22         36          0
  
10 rows selected.
  
SQL> CREATE OR REPLACE FUNCTION get_largest_number (
  2      p_table_name    IN  VARCHAR2
  3  )
  4  RETURN NUMBER
  5  IS
  6      CURSOR c_num_cols IS
  7          SELECT   utc.column_name
  8          FROM     sys.user_tab_columns   utc
  9          WHERE    utc.table_name = UPPER(TRIM(p_table_name))
 10          AND      utc.data_type = 'NUMBER'
 11          ORDER BY utc.column_id
 12      ;
 13      l_sql_stmt          VARCHAR2(2000) := 'SELECT GREATEST(';
 14      l_number            NUMBER;
 15  BEGIN
 16      FOR build_sql IN c_num_cols LOOP
 17          l_sql_stmt := l_sql_stmt
 18                        || 'MAX('
 19                        || build_sql.column_name
 20                        || '),';
 21      END LOOP;
 22      -- Strip off the final comma
 23      l_sql_stmt := SUBSTR(l_sql_stmt,1,LENGTH(l_sql_stmt) - 1)
 24                    || ') FROM '
 25                    || UPPER(p_table_name);
 26      EXECUTE IMMEDIATE l_sql_stmt
 27      INTO              l_number;
 28      RETURN (l_number);
 29  END get_largest_number;
 30  /
  
Function created.
  
SQL> SELECT get_largest_number('t1') FROM DUAL;
  
GET_LARGEST_NUMBER('T1')
------------------------
                      60
  
SQL> SELECT get_largest_number('t2') FROM DUAL;
  
GET_LARGEST_NUMBER('T2')
------------------------
                      99
  
SQL>
HTH,

A
Previous Topic: Lost Database...
Next Topic: Space problem
Goto Forum:
  


Current Time: Thu Mar 28 18:08:18 CDT 2024