get min and max from subquery-CodePudding

I'm trying to get min and max values from query

SELECT TABLE_NAME , COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('TABLE_A','TABLE_B')
and DATA_TYPE='NUMBER'
AND (DATA_PRECISION IS NULL OR DATA_SCALE IS NULL)

here what I get so far, but it shows nothing:

BEGIN DBMS_OUTPUT.ENABLE (buffer_size => NULL); END;


declare
l_max number;
begin
for "CUR_R" in
(SELECT TABLE_NAME , COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('TABLE_A','TABLE_B')
and DATA_TYPE='NUMBER'
AND (DATA_PRECISION IS NULL OR DATA_SCALE IS NULL)
)
loop

execute immediate 'select max(' || "CUR_R"."COLUMN_NAME" ||') from ' || "CUR_R"."TABLE_NAME" into l_max;
dbms_output.put_line("CUR_R"."TABLE_NAME" ||'.'|| "CUR_R"."COLUMN_NAME" ||' -> max value = '|| l_max);

end loop;

end;

maybe i missing something? also, I'm not an admin, just have grants to select to particular tables can't create procedure or temp table

I expect result of this structure:

owner	column_name	max_value	min_value

maybe I am missing something? also, I'm not an admin, just have grants to select to particular tables can't create procedure or temp table

CodePudding user response：

I guess you should remove (comment) precision and scale conditions in cursor's query.

I used user_tab_columns and tables I have in my schema, but ... that's it, more or less:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_max  NUMBER;
  3  BEGIN
  4     FOR cur_r IN (SELECT table_name, column_name
  5                     FROM user_tab_columns
  6                    WHERE     table_name IN ('EMP', 'DEPT')
  7                          AND data_type = 'NUMBER'
  8                          --  AND (   data_precision IS NULL
  9                          --       OR data_scale IS NULL)
 10                          AND 1 = 1)
 11     LOOP
 12        EXECUTE IMMEDIATE   'select max('
 13                         || cur_r.column_name
 14                         || ') from '
 15                         || cur_r.table_name
 16           INTO l_max;
 17
 18        DBMS_OUTPUT.put_line (
 19              cur_r.table_name
 20           || '.'
 21           || cur_r.column_name
 22           || ' -> max value = '
 23           || l_max);
 24     END LOOP;
 25  END;
 26  /
DEPT.DEPTNO -> max value = 40
EMP.EMPNO -> max value = 7934
EMP.MGR -> max value = 7902
EMP.SAL -> max value = 5000
EMP.COMM -> max value = 1400
EMP.DEPTNO -> max value = 30

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Your query will only return column values where the data type is NUMBER or NUMBER(*,X). If the data type is NUMBER(X) or NUMBER(X,Y) then the filter on DATA_PRECISION IS NULL OR DATA_SCALE IS NULL will exclude those columns.

For example, given the sample data:

CREATE TABLE table_a (
  value1 NUMBER,
  value2 NUMBER(*,0),
  value3 NUMBER(10),
  value4 NUMBER(5,2)
);

INSERT INTO table_a
SELECT LEVEL, 2*LEVEL, 3*LEVEL, 4*LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

CREATE TABLE table_b (
  value1 NUMBER,
  value2 NUMBER(*,0),
  value3 NUMBER(10),
  value4 NUMBER(5,2)
);

INSERT INTO table_b
SELECT LEVEL   5, 2*LEVEL - 2, 3*LEVEL   3, 1/LEVEL FROM DUAL CONNECT BY LEVEL <= 4;

Then:

DECLARE
  l_min number;
  l_max number;
BEGIN
  DBMS_OUTPUT.ENABLE();

  FOR c IN (
    SELECT OWNER,
           TABLE_NAME,
           COLUMN_NAME
    FROM   ALL_TAB_COLUMNS
    WHERE  TABLE_NAME IN ('TABLE_A','TABLE_B')
    AND    DATA_TYPE='NUMBER'
    AND    (DATA_PRECISION IS NULL OR DATA_SCALE IS NULL)
  )
  LOOP
    EXECUTE IMMEDIATE
      'select min("' || c.column_name ||'"),
              max("' || c.column_name ||'")
       from   "' || c.owner || '"."' || c.table_name || '"'
      INTO l_min, l_max;
    DBMS_OUTPUT.PUT_LINE(
      c.owner || '.' || c.table_name ||'.'|| c.column_name
      || ' -> min_value = ' || l_min
      || ', max value = '|| l_max
    );
  END LOOP;
END;
/

Outputs:

FIDDLE_TNHCDFVJDASVYWAHROPU.TABLE_A.VALUE1 -> min_value = 1, max value = 5
FIDDLE_TNHCDFVJDASVYWAHROPU.TABLE_A.VALUE2 -> min_value = 2, max value = 10
FIDDLE_TNHCDFVJDASVYWAHROPU.TABLE_B.VALUE1 -> min_value = 6, max value = 9
FIDDLE_TNHCDFVJDASVYWAHROPU.TABLE_B.VALUE2 -> min_value = 0, max value = 6

The columns value3 and value4 are excluded as they have both scale and precision specified in their data type.

fiddle

CodePudding user response：

Dynamic SQL Approach

This query produces the query that gets the minand max for each column (line by line).

The simplest approach for ad Hoc purposed is to run the query, copy the result and execute it. You may of course fetch the result in a CLOBvariable and execute immediate it for repeatable tasks.

SELECT 
'select '''||TABLE_NAME||''' TABLE_NAME ,''' || COLUMN_NAME||''' COLUMN_NAME,'|| ' max('|| COLUMN_NAME ||
') max_value,' || ' min('|| COLUMN_NAME ||') min_value from '|| TABLE_NAME ||
case when row_number() over (order by table_name desc, column_name desc)  != 1 then ' UNION ALL' end as sql_text
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('TABLE_A','TABLE_B')
and DATA_TYPE='NUMBER'
AND (DATA_PRECISION IS NULL OR DATA_SCALE IS NULL)
order by table_name, column_name;

Result

select 'TABLE_A' TABLE_NAME ,'X' COLUMN_NAME, max(X) max_value, min(X) min_value from TABLE_A UNION ALL
select 'TABLE_A' TABLE_NAME ,'Y' COLUMN_NAME, max(Y) max_value, min(Y) min_value from TABLE_A UNION ALL
select 'TABLE_B' TABLE_NAME ,'X' COLUMN_NAME, max(X) max_value, min(X) min_value from TABLE_B UNION ALL
select 'TABLE_B' TABLE_NAME ,'Y' COLUMN_NAME, max(Y) max_value, min(Y) min_value from TABLE_B

CodePudding user response：

why not query like:

select max(x), min(x)
from t
group by x