Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (11g)
SQL Query [message #666913] |
Fri, 01 December 2017 02:39 |
|
LKT
Messages: 5 Registered: November 2017
|
Junior Member |
|
|
Hi,
I need to wright a query to find a max value in table.
My table is having 250 columns.
Its difficult to wright a max for every column.
Could you please help me to do this?
Thanks in Advance.
|
|
|
|
Re: SQL Query [message #666922 is a reply to message #666913] |
Fri, 01 December 2017 06:23 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
LKT wrote on Fri, 01 December 2017 02:39Hi,
I need to wright a query to find a max value in table.
My table is having 250 columns.
Its difficult to wright a max for every column.
Could you please help me to do this?
Thanks in Advance.
What's so difficult about it?
select max(col_a),
max(col_b),
max(col_c)
from my_table;
So you have 250 columns instead of 3. So you have to write more lines of code. Sure, it's a bit tedious, but if you had started when you posted your question, you'd have been done three hours ago. Facility with any competent text editor (or even a lame editor like notepad) would make the repetition easier.
[Updated on: Fri, 01 December 2017 06:24] Report message to a moderator
|
|
|
|
Re: SQL Query [message #666940 is a reply to message #666936] |
Sat, 02 December 2017 02:37 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
In the contest "how can I poke my eye" here are some examples.
SQL> set feed off head off sqlt off sqlbl on
SQL> select '
2 select ' ||
3 rtrim(xmlagg(xmlelement(c, 'max('||column_name||'),')).extract('//text()'),',') ||
4 ' from dept;
5
6 '
7 from user_tab_columns
8 where table_name='DEPT'
9 /
select max(DEPTNO),max(DNAME),max(LOC) from dept;
SQL> with
2 core as (
3 select substr(sys_connect_by_path(c,','),2) c
4 from (select 'max('||column_name||')' c, column_id id
5 from user_tab_columns where table_name='DEPT')
6 where connect_by_isleaf = 1
7 connect by prior id = id - 1
8 start with id = 1
9 )
10 select '
11 select '||c||' from dept;
12
13 '
14 from core
15 /
select max(DEPTNO),max(DNAME),max(LOC) from dept;
SQL> with
2 cols as (
3 select 'max('||column_name||')' c, column_id id
4 from user_tab_columns
5 where table_name='DEPT'
6 ),
7 core(c, id) as (
8 select c, id from cols where id = 1
9 union all
10 select core.c||','||cols.c, cols.id
11 from core, cols
12 where cols.id = core.id + 1
13 )
14 select '
15 select '||max(c)||' from dept;
16
17 '
18 from core
19 /
select max(DEPTNO),max(DNAME),max(LOC) from dept;
SQL> select '
2 select '||max(r)||' from dept;
3
4 '
5 from (
6 select r
7 from ( select 'max('||column_name||')' c, column_id id
8 from user_tab_columns
9 where table_name='DEPT' )
10 model
11 return updated rows
12 dimension by (id)
13 measures (cast(null as varchar2(100)) r, c)
14 rules ( r[any] order by id = decode(r[1], null, '', r[cv()-1]||',') || c[cv()] )
15 )
16 /
select max(DEPTNO),max(DNAME),max(LOC) from dept;
|
|
|
|
Re: SQL Query [message #666948 is a reply to message #666947] |
Sat, 02 December 2017 10:35 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Another step:
SCOTT> create or replace function get_table_max (p_table in varchar2)
2 return sys.odcivarchar2list
3 pipelined
4 is
5 cols sys.odcivarchar2list;
6 stmt varchar2(32767);
7 res clob;
8 begin
9 select column_name
10 bulk collect into cols
11 from user_tab_columns
12 where table_name = dbms_assert.sql_object_name(p_table);
13 if cols.count > 0 then
14 for i in cols.first..cols.last loop
15 stmt := stmt || '''
16 '|| rpad(cols(i),30,'.') ||' ''||max('||cols(i)||')||';
17 end loop;
18 stmt := 'select substr(' || rtrim(stmt,'|') || ',2) ' ||
19 'from ' || dbms_assert.sql_object_name(p_table);
20 execute immediate stmt into res;
21 else
22 res := 'Unknow table "'||dbms_assert.sql_object_name(p_table)||'"';
23 end if;
24 pipe row (res);
25 end;
26 /
Function created.
SCOTT> sho err
No errors.
SCOTT> create or replace function get_tables_max
2 return sys.odcivarchar2list
3 pipelined
4 is
5 cols sys.odcivarchar2list;
6 stmt varchar2(32767);
7 res clob;
8 begin
9 for tab in (select table_name from user_tables order by 1)
10 loop
11 pipe row ('');
12 pipe row ('MAX OF '||tab.table_name);
13 pipe row (rpad('-',length(tab.table_name)+7,'-'));
14 select column_value into res from table(get_table_max(tab.table_name));
15 pipe row (res);
16 end loop;
17 end;
18 /
Function created.
SCOTT> sho err
No errors.
SCOTT> set head off arrays 1
SCOTT> select * from table(get_tables_max());
MAX OF BONUS
------------
ENAME.........................
JOB...........................
SAL...........................
COMM..........................
MAX OF DEPT
-----------
DEPTNO........................ 40
DNAME......................... SALES
LOC........................... NEW YORK
MAX OF EMP
----------
EMPNO......................... 7934
ENAME......................... WARD
JOB........................... SALESMAN
MGR........................... 7902
HIREDATE...................... 23/05/1987 00:00:00
SAL........................... 5000
COMM.......................... 1400
DEPTNO........................ 30
MAX OF SALGRADE
---------------
GRADE......................... 5
LOSAL......................... 3001
HISAL......................... 9999
Now you have to remove from the column list those with a datatype that can't have a MAX like LONG, LOB, XMLTYPE.
[Updated on: Sat, 02 December 2017 10:36] Report message to a moderator
|
|
|
|
|
|
|
Re: SQL Query [message #666972 is a reply to message #666970] |
Mon, 04 December 2017 03:02 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: What about the rest of it?
Exactly what I wanted to post.
Post a complete description of your actual problem so we can provide a more accurate, efficient and reliable solution than those I have posted (and which have many drawbacks).
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:00:07 CDT 2024
|