No question at this time
DBA Top 10
1 M. Cadot 17900
2 A. Kavsek 15900
3 M. Hidayathullah ... 9200
4 B. Vroman 8600
5 T. Boles 4900
6 P. Wisse 4700
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 A. Hudspith 800
10 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48079
Total active users1704
Act. users last 24h9
Act. users last hour0
Registered user hits last week295
Registered user hits last month1831
Go up

query
Next thread: flash back tables
Prev thread: exporting oracle tables on command line as sql files

Message Score Author Date
hi I need a dynamic query that select the min d...... RUCHI TANEJA Nov 08, 2017, 08:35
Hello, do you mean something like this?<pre>SET...... Bruno Vroman Nov 08, 2017, 10:54
no i want like. table A having column create da...... RUCHI TANEJA Nov 08, 2017, 11:16
Hello Ruchi, some ambiguity... One thing I don'...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 08, 2017, 11:38

Follow up by mail Click here


Subject: query
Author: RUCHI TANEJA, India
Date: Nov 08, 2017, 08:35, 14 days ago
Os info: linux
Oracle info: 11gr2
Message: hi

I need a dynamic query that select the min date as column in a table and same table exist for many users.

so i want the min value of date column of a table userwise.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: Bruno Vroman, Belgium
Date: Nov 08, 2017, 10:54, 14 days ago
Message: Hello,

do you mean something like this?
SET RECSEP OFF

COL x NOPRINT
WITH all_of_them AS
( SELECT owner own
FROM all_tables
WHERE table_name = 'THIS_TABLE'
ORDER BY 1 )
SELECT 1 x
, CASE WHEN rownum > 1 THEN 'UNION ALL' || CHR(10) END ||
'SELECT ''' || a.own ||
CASE WHEN rownum = 1 THEN ''' own' ELSE '''' END ||
', MIN( dte ) ' ||
CASE WHEN rownum = 1 THEN 'min_date ' END ||
'FROM ' || a.own || '.this_table'
FROM all_of_them a
UNION ALL
SELECT 2, 'ORDER BY 1;' FROM dual
ORDER BY 1
;
that gives
SELECT 'BRUNO' own, MIN( dte ) min_date FROM BRUNO.this_table
UNION ALL
SELECT 'RUCHI', MIN( dte ) FROM RUCHI.this_table
UNION ALL
SELECT 'SCOTT', MIN( dte ) FROM SCOTT.this_table
ORDER BY 1
;
that gives
OWN MIN_DATE
------ --------------------
BRUNO 04-NOV-2017 21:08:00
RUCHI 08-NOV-2017 09:22:41
SCOTT 07-NOV-2017 04:58:17
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: RUCHI TANEJA, India
Date: Nov 08, 2017, 11:16, 14 days ago
Message: no i want like.

table A having column create date.

table b having column create date

table c haaving column create date

now all these tables exist in 10 different schemas.

My required output is i want that for every user what is the value of minimum create date column for every table.

AS owner details i can get from dba_objects also object_name is there in dba_objects and object_type is table.

owner table min(create_date)

Bruno A 10-NOV-17
B 02-MAR-12
C 03-AUG-83

RUCHI A 1-MAR-65
B 30-NOV-17
C 02-APR-16
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: Bruno Vroman, Belgium
Date: Nov 08, 2017, 11:38, 14 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Ruchi,

some ambiguity... One thing I don't understand.

If the date to identify is in fact data in the tables (like in my previous reply), than what about modifying "all_of_them" to
WITH all_of_them AS

( SELECT owner own, table_name t
FROM all_tables
WHERE table_name IN ( 'A', 'B', 'C' )
ORDER BY 1 )
and add a column "table_name" in the generated statements?

And if you simply want the creation date of the tables themselves, then there is no need for dynamic SQL:
COL owner FOR A20

COL table_name FOR A30
BREAK ON owner SKIP 1
SELECT owner, object_name table_name, created
FROM dba_objects
WHERE object_type = 'TABLE'
AND object_name IN ( 'A', 'B', 'C' )
ORDER BY 1, 2
;
Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here