TOm:

Based on this, can you always define a variable, define a string for it and the opne cursor for it.

will this also work from in list search like:

l_list=('va','ca','pa');
l_query:='select * from table where col in '||l_list;
open cursor for l_query;

This Solves Lot of My problems. Once again, you are the Best.

TOm:

A followup to what you wrote:

execute immediate 'alter session set cursor_sharing=force';
execute immediate 'alter session set cursor_sharing='exact';

what doest this do?

Thanks

Simple, concise suggestion and explanation of a useful development principle!

Thanks Tom!

Hi Tom:

The above result can be easily obtained in MS Access using crosstab query. Why Oracle does NOT provide crosstab query?

Although not recommended, liked the example using
cursor_sharing parameter.

Dear Sir,
can I have a query that selects rows from all tables in my
schema.Is a single select possible?Please inform me if you
have a query like that.
Thanks.

Dear Sir,
Well and wish the same from you.I have some questions related to queries.
1)Suppose if I have a string like "Oracle",How to write a query that prints each character in each line?
2)Have a look at the following queries.
(i)select ename,deptno,sal from emp e1 where sal = (select max(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno;
(ii)select ename,deptno,sal from emp where sal in(select
max(sal) from emp group by deptno;
My Question is Is there any other way that this query can
be issued?
3) How to fix the "Single row subquery returns more than one
row" message?
Expecting your reply.
Thanks

1) 


ops$tkyte@ORA920> variable x varchar2(20)
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec :x := 'Oracle'
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select substr( :x, rownum, 1 )
  2    from all_objects
  3   where rownum <= length(:x);
 
S
-
O
r
a
c
l
e
 
6 rows selected.



2) given that you gave me 2 totally different queries that return 2 totally different answers -- I'd have to say "i must be able to give an infinite set of responses since you are just looking for random queries" :)


ops$tkyte@ORA920> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA920> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA920> drop table dept;
 
Table dropped.
 
ops$tkyte@ORA920> create table dept as select * from scott.dept;
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> update emp set sal = 2000;
 
14 rows updated.
 
ops$tkyte@ORA920> update emp set sal = 3000 where rownum = 1;
 
1 row updated.
 
ops$tkyte@ORA920> commit;
 
Commit complete.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename,deptno,sal
  2    from emp e1
  3   where sal = (select max(sal)
  4                  from emp e2
  5                 where e1.deptno = e2.deptno)
  6   order by deptno;
 
ENAME          DEPTNO        SAL
---------- ---------- ----------
CLARK              10       2000
KING               10       2000
MILLER             10       2000
SMITH              20       3000
ALLEN              30       2000
TURNER             30       2000
JAMES              30       2000
WARD               30       2000
MARTIN             30       2000
BLAKE              30       2000
 
10 rows selected.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename,deptno,sal
  2    from emp
  3   where sal in (select max(sal)
  4                   from emp
  5                  group by deptno );
 
ENAME          DEPTNO        SAL
---------- ---------- ----------
ALLEN              30       2000
WARD               30       2000
JONES              20       2000
BLAKE              30       2000
SCOTT              20       2000
TURNER             30       2000
JAMES              30       2000
MILLER             10       2000
FORD               20       2000
ADAMS              20       2000
KING               10       2000
CLARK              10       2000
MARTIN             30       2000
SMITH              20       3000
 
14 rows selected.


<b>see, totally different answers to your query with the same data</b>  This is why I have a chapter in my new book on "don't tune a query, tune a question"

I have to assume that you want the set of people, by department, that make the TOP salary in that dept...  queries such as:

select ename, deptno, sal
  from ( select ename, deptno, sal,
                rank() over ( partition by deptno order by sal desc nulls last ) r
           from emp )
 where r = 1;
                                                                                                                
select ename, deptno, sal
  from ( select ename, deptno, sal,
                max(sal) over ( partition by deptno ) max_sal
           from emp )
 where sal = max_sal
/
                                                                                                                
select ename, deptno, sal
  from ( select ename, deptno, sal,
                first_value(sal) over ( partition by deptno order by sal desc nulls last ) first_sal
           from emp )
 where sal = first_sal
/

will do that.


3) there is nothing to fix here.  

You have a logic/algorithm problem.

You are basically saying something like:


select * from t where x = ( subquery )

and subquery ISN'T a single value but a set.  what would you have us do?  you need to phrase your problem in english and then we might be able to tell you how to solve it.  

the error "single row subquery" isn't an "oracle" error -- it is a bug in your logic/code/data....



 

Dear Sir,
 Thanks for your reply.My second question was
   2)I have put a query which returns highest salary getting employees departmentwise.Both the queries return the same resultset in my system.I use oracle v8.0 personal edition.Queries and their resultsets follow after this.
** What I asked you was "Is there any other way that this query can be expressed?".But you are agonising over my question.I don't know why
Any how Thanks for your time spending.If you have any other way of expressing
the same query,Please do write a follow up.

SQL> select ename,sal,deptno from emp e1 where sal = (select max(sal) from
  2  emp e2 where e1.deptno = e2.deptno) order by deptno;

ENAME             SAL     DEPTNO                                                
---------- ---------- ----------                                                
KING             5000         10                                                
SCOTT            3000         20                                                
FORD             3000         20                                                
BLAKE            2850         30                                                

SQL> select ename,sal,deptno from emp where sal in(select max(sal) from emp
  2  group by deptno) order by deptno;

ENAME             SAL     DEPTNO                                                
---------- ---------- ----------                                                
KING             5000         10                                                
SCOTT            3000         20                                                
FORD             3000         20                                                
BLAKE            2850         30                                                

SQL> spool off
 

Dear sir,
well and wish the same from you.
1)I tried to put a query which groups employees under their managers.But it always returned errors.I tried a pl/sql block which returned the result set I was looking for.Can this anonymous block be transformed into a sql query?Could you please help? Please don't forget to specify the different formats of the same query.
Thanks in advance.
SQL> begin
  2   for x in (select distinct mgr from emp order by mgr) loop
  3     dbms_output.put_line('Manager:'||x.mgr);
  4    for y in (select ename from emp where mgr = x.mgr) loop
  5      dbms_output.put_line(y.ename);
  6    end loop;
  7    dbms_output.put_line(' ');
  8   end loop;
  9  end;
 10  /
Manager:7566                                                                    
SCOTT                                                                           
FORD                                                                            
Manager:7698                                                                    
ALLEN                                                                           
MARTIN                                                                          
TURNER                                                                          
JAMES                                                                           
Manager:7782                                                                    
MILLER                                                                          
Manager:7788                                                                    
ADAMS                                                                           
Manager:7839                                                                    
JONES                                                                           
BLAKE                                                                           
CLARK                                                                           
JOE                                                                             
Manager:7902                                                                    
SMITH                                                                           
Manager:                                                                        

PL/SQL procedure successfully completed.

SQL> spool off
2)Is there a query format like 
   select 'select * from a' from b;
 If yes,please provide some examples.
 Thanks for your previous reply. 

  1  select mgr, cursor( select ename from emp emp2 where emp2.mgr = emp.mgr )
  2* from ( select distinct mgr from emp ) emp
ops$tkyte@ORA920LAP> /

       MGR CURSOR(SELECTENAMEFR
---------- --------------------
      7566 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
SCOTT
FORD

      7698 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
----------
ALLEN
WARD
MARTIN
TURNER
JAMES


is one way.  

ops$tkyte@ORA920LAP> break on mgr skip 1
ops$tkyte@ORA920LAP> select mgr, ename from emp order by mgr, ename;

       MGR ENAME
---------- ----------
      7566 FORD
           SCOTT

      7698 ALLEN
           JAMES
           MARTIN
           TURNER
           WARD

      7782 MILLER

      7788 ADAMS

      7839 BLAKE
           CLARK
           JONES

      7902 SMITH

           KING


14 rows selected.

is perhaps another. 

Hi Tom,
Need your help.
For example I have tables and data as bellow.
I have same empname in all deptno with diff in sal.
If there is no employee in a deptno. still i have to display empname with sal = 0.
Please help. Thanks in advance.

SQL> desc tt
 Name                                      Null?    Type
 ----------------------------------------- -------- -------- ENAME                                         VARCHAR2(10)
 SAL                                           NUMBER
 DNO                                           NUMBER

SQL> 
SQL> 
SQL> select * from tt;

ENAME             SAL        DNO
---------- ---------- ----------
A                 100         10
A                 200         10
A                 300         20

Elapsed: 00:00:00.66
SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- -------- DNO                                              NUMBER

SQL> select * from t;

       DNO
----------
        10
        20
        30

Like to display output as bellow.

ENAME             SAL        DNO
---------- ---------- ----------
A                 100         10
A                 200         10
A                 300         20
A                 0           30

 

Hi,
Thank you very much.
My intension is
In a year 2003 I have fixed customers for 12 months.
So I have year and month info in one table.
My senario is I haven't made any sales to my fixed customers in some months in a year.
This info I am not logging in my transaction table.
Means I don't have any info about customers for few months.
So in this case I need to generate a report for 12 months. If no sales in a moth still i have to show customer name with zero amount.

SQL> desc t_cust
 Name                                      Null?    Type
 ----------------------------------------- -------- -------
 CNO                                                NUMBER
 AMT                                                NUMBER
 MTH                                                NUMBER
 YR                                                 NUMBER

SQL> select * from t_cust;

       CNO        AMT        MTH         YR
---------- ---------- ---------- ----------
      1001        100          1       2003
      1001        200          2       2003
      1001          4          4       2003
      1001        500          2       2003
      1002       2000          1       2003
      1002       3000          2       2003
      1002       2000          3       2003
      1002       3000          3       2003
      1002       6000          6       2003
SQL> desc myr
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 MTH                                                NUMBER
 YR                                                 NUMBER

SQL> select * from myr;

       MTH         YR
---------- ----------
         1       2003
         2       2003
         3       2003
         4       2003
         5       2003
         6       2003
         7       2003
         8       2003
         9       2003
        10       2003
        11       2003
        12       2003
Please provide solution. Thanks in advance.
I am able to get for one customer. 
select mth, yr, cno, sum(amt)
from
(
select a.mth mth, a.yr yr,
decode(a.cnt,0,(select unique cno from t_cust where cno = 1001),cno) cno, nvl(a.amt,0) amt, a.c
from
(
select cno cno, myr.mth mth, myr.yr yr, amt amt, count(cno) over ( partition by myr.mth ) cnt
from t_cust, myr
where t_cust.mth(+) = myr.mth
and t_cust.yr(+) = myr.yr
and t_cust.cno(+) = 1001
) a
)
group by mth, yr, cno


 

Hi Tom
I have a data in two table as given below

Table..A
colA1 colA2
1 null
2 null
3 null
4 11
5 null
6 null

Table..B
colB1 colA2
1 11
2 12
3 13

I want to update column colA2 of table..A with colA2 of tableB
in a single statement.The final result of Table..A is like this

Table..A
colA1 colA2
1 null
2 12
3 null
4 11
5 13
6 null

Here Value 11 already exist in TableA so need not to update
so the rest 12,13 from TableB should update in TableA
any order.

Can you please give me the solution .

Hi Tom,
Need your help to write query to cut a string into parts.
I have data as bellow.

Table : TB_ODW_GO

REF_NBR LOG_DESC
----------------------------------------------------------
0234567 -111: ORDER:10901 NOT FOUND-222 ZIPCODE: NOTFOUND
1274033 -111: ORDER:12343 NOT FOUND

I want to display output as bellow.

REF_NBR ERR_CD ERR_MSG
-----------------------------------------------------------
0234567 -111 ORDER:10901 NOT FOUND
0234567 -222 ZIPCODE: NOTFOUND
1274033 -111 ORDER:12343 NOT FOUND

Thanking you in advance.

ARC.

Hi Tom,
I need it for 'n' lines.

Thanks
ARC

you'll be doing it procedurally -- in 9i, you can write a pipelined function to do it.

something like this.  I made a simplifying assumping that the string was formated as

-<ERROR_CODE>: <MESSAGE>-<ERROR_CODE>: <MESSAGE>


that is, - precedes error code, error code terminated by :.  If that is not your case, you need to write the code to parse your string:


ops$tkyte@ORA920PC> create table t ( ref_nbr number, log_desc varchar2(80) );
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into t values ( 0234567, '-111: ORDER:10901 NOT FOUND-222: ZIPCODE: NOTFOUND' );
 
1 row created.
 
ops$tkyte@ORA920PC> insert into t values ( 1274033, '-111: ORDER:12343 NOT FOUND' );
 
1 row created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace type myScalarType as object
  2  ( ref_number number, err_cd number, err_msg varchar2(40) )
  3  /
 
Type created.
 
ops$tkyte@ORA920PC> create or replace type myTableType as table of myScalarType
  2  /
 
Type created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace function parse( p_cur in sys_refcursor )
  2  return myTableType
  3  PIPELINED
  4  as
  5      type array is table of t%rowtype index by binary_integer;
  6      l_data array;
  7      l_rec  myScalarType := myScalarType(null,null,null);
  8      l_tmp  long;
  9      n      number;
 10  begin
 11      loop
 12          fetch p_cur bulk collect into l_data limit 100;
 13          for i in 1 .. l_data.count
 14          loop
 15              l_rec.ref_number := l_data(i).ref_nbr;
 16              l_data(i).log_desc := l_data(i).log_desc || '-';
 17
 18              loop
 19                  n := instr( l_data(i).log_desc, '-', 2 );
 20                  exit when (nvl(n,0)=0);
 21
 22                  l_tmp := substr( l_data(i).log_desc, 1, n-1);
 23                  l_data(i).log_desc := substr( l_data(i).log_desc, n );
 24
 25                  n := instr( l_tmp, ':' );
 26                  l_rec.err_cd  := substr(l_tmp, 1, n-1 );
 27                  l_rec.err_msg := trim( substr( l_tmp, n+1 ) );
 28                  pipe row(l_rec);
 29              end loop;
 30          end loop;
 31          exit when p_cur%notfound;
 32      end loop;
 33      close p_cur;
 34      return;
 35  end;
 36  /
 
Function created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from TABLE( parse( cursor(select * from t) ) );
 
REF_NUMBER     ERR_CD ERR_MSG
---------- ---------- ----------------------------------------
    234567       -111 ORDER:10901 NOT FOUND
    234567       -222 ZIPCODE: NOTFOUND
   1274033       -111 ORDER:12343 NOT FOUND
 
 

Tom,
Thanks for solution. Really it is very good and also I came to know about "pipelined" with this solution which I am not aware of before.
The same thing I achived using PL/SQL records and table in version 8.1.7
Thanks again.

ARC

Tom,
Is this possible. ?
I want to web app user sorting option.

No error raised below but the ORDER BY didn't happen
------------------------
DECLARE
l_sort INTEGER := 2;

CURSOR empcur
IS
SELECT empno, ename FROM emp ORDER BY l_sort ;

CURSOR empcur2(psort IN INTEGER)
IS
SELECT empno, ename FROM emp ORDER BY psort ;

BEGIN
FOR i IN empcur
LOOP
dbms_output.put_line(i.ename);
END LOOP;
dbms_output.put_line('-----------------');

FOR i IN empcur2(l_sort)
LOOP
dbms_output.put_line(i.ename);
END LOOP;
END ;

thanks

Dear Sir,
Well and wish the same from you.
Is there any other way to put this query?
sql> select ename,nvl(comm,'Commission is null') from emp
or
sql> select ename,decode(comm,null,'Commission is null',comm) from emp
It is just a matter of eagerness and curiosity.I think
*case* is possible but is there any other way?
Please do reply.
Bye!

CREATE TABLE T ( GP VARCHAR2(10), GPP VARCHAR2(20))
/

Table created.

ALTER TABLE T ADD CONSTRAINT PK1 PRIMARY KEY ( GP, GPP);

/

INSERT INTO T VALUES('A', 'A');



INSERT INTO T VALUES('A', 'B');



INSERT INTO T VALUES('Z', 'A');


I want to find out all those GP values which have a common GPP value? How can I do it, and what are
the multiple ways I can do it. Please help.

ops$tkyte@ORA9IR2> select *
  2    from (
  3  select gp, gpp, count(*) over (partition by gpp) cnt
  4    from t
  5         )
  6   where cnt > 1;
 
GP         GPP                         CNT
---------- -------------------- ----------
A          A                             2
Z          A                             2


first one that popped into my head... 

select a.* from t a , t b where a.gpp = b.gpp and a.rowid <> b.rowid
GP GPP
---------- --------------------
Z A
A A
2 rows selected

there are potentially an infinite number of ways to do this, yes.  

That one would overdo it in the event there are three records:

ops$tkyte@ORA9IR2>  INSERT INTO T VALUES('x', 'A');
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (
  3  select gp, gpp, count(*) over (partition by gpp) cnt
  4    from t
  5         )
  6   where cnt > 1;
 
GP         GPP                         CNT
---------- -------------------- ----------
A          A                             3
Z          A                             3
x          A                             3
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select       a.* from t a , t b where a.gpp = b.gpp and a.rowid <> b.rowid
  2  /
 
GP         GPP
---------- --------------------
Z          A
x          A
A          A
x          A
A          A
Z          A
 
6 rows selected.
 

I think yours may be.

select distinct a.* from t a , t b where a.gpp = b.gpp and
a.rowid <> b.rowid.

Now they give identical results. Do u agree.
Will the analytic soln outperform the distinct self join soln
Thanx

hi Tom & all learners

I am a beginner with oracle.
what would be the best suggestion to get a perfect hand in oracle other than time and practice
really appreciate suggestions/ books name/sites form all learners/experts on my email.
THANKS in advance to all

learnz2004@yahoo.com

The famous DEPT and EMP tables.
How to write an SQL which returns true only if all depts have employees.

My attempt works but I need tom's Brilliant and simple solution and other possible solutions.
1)
select count(*) from dual where exists(
select null from dept having count(*) = (select count(distinct deptno) from emp).
2)select count(*) from emp e,dept d where d.deptno = e.deptno(+) and e.deptno is null

The basic emp table has no emps for deptno = 40, once one is added the above query returns 1 else 0.
Can u please give atleast 3 or 4 or more alternatives ?
Thanx much

Thanx for the Quiz answers.You have a lot of gray cells.

Tom,


getting stuck with this query. Need your help.

create table t1 (c1 char(2),c2 number,c3 number,c4 date);
create table t2 (c1 char(2),c2 number,c3 number,c4 date);

insert into t1 values('a',100,0,'03-MAY-04');
insert into t1 values('b',100,0,'03-MAY-04');
insert into t1 values('a',0,100,'03-MAY-04');
insert into t1 values('b',0,100,'03-MAY-04');
insert into t1 values('a',150,0,'05-MAY-04');
insert into t1 values('a',0,175,'05-MAY-04');

insert into t values('a',100,100,'03-MAY-04');
insert into t values('b',100,100,'03-MAY-04');
insert into t values('a',150,175,'03-MAY-04');


table: t1
c1 c2 c3 C4

a 100 0 03-MAY-04
b 100 0 03-MAY-04
a 0 100 03-MAY-04
b 0 100 03-MAY-04
a 150 0 05-MAY-04
a 0 175 05-MAY-04


table t2:

c1 c2 c3
a 100 100 03-MAY-04
b 100 100 03-MAY-04
a 150 175 05-MAY-04



How to compare these two tables. The data in the two tables
are actually the same except the way it is spread. How to write
a query to prove thay they are the same. Also how do I get the
table t1 data to look like so that I can use MINUS operator against
the two tables.

table t1:

c1 c2 c3 c4
a 100 100 03-MAY-04
b 100 100 03-MAY-04
a 150 175 05-MAY-04

I think he is looking for a simple :

select c1, sum(c2), sum(c3), c4
from t1
group by c1,c4
minus
select c1,c2,c3,c4
from t2

I think I used the incorrect word "spread". It is just that
the second table holds the same values as the first table but in
a different way. Also I am not looking at the Sum function there as suggested. My aim is to find out if there is any difference in the two tables. Just a MINUS wil show differences as the data in table1 exists in two different rows while the data in table2 has the same data in one row.

For eg: table1:

a 100 0 03-MAY-04
a 0 100 03-MAY-04

Table2:

a 100 100 03-MAY-04

In this case, the data is the same in both the tables, but how do I
do that through a query? If I can get the data in the table1 to look
like
a 100 100 03-MAY-04

then I can use the MINUS function to get the differences in the entire
table. Hope I am clear.

Thanks for all the help.

Excuse me for not making things clear for you.
This is what the scenario looks like:

CREATE OR REPLACE TYPE my_obj AS OBJECT (
c1 VARCHAR2(6),
c2 VARCHAR2(6),
c3 number,
c4 DATE,

);

/
CREATE OR REPLACE TYPE my_list AS TABLE OF my_OBJ;
/

My_pkg has the undernoted declaration:

TYPE my_list_pkg IS TABLE OF my_OBJ INDEX BY PLS_INTEGER;

My_pkg_body has ---the actuals are lot bigger.The relevant part shown

Procedure get_list(c1 in varchar2,c2 in varchar2,c3 in number,c4 in date, p_out out my_list)

as

l_listA My_pkg.my_list_pkg;
l_listB My_pkg.my_list_pkg;

----
I have two queries, the first doing a bulk collect into l_listA and second a bulk
collect into l_listB. And then doing the following :

size := l_listA.count;

p_out := my_list();


for cnt in 1..l_listB.count loop
l_listA(size + cnt) := l_listB(cnt);
end loop;

p_out.EXTEND(l_listA.COUNT);
for cnt in 1..l_listA.count loop
p_out(cnt) := l_listA(cnt);
end loop;
RETURN ;


When this list is inserted into table1 it goes in this fashion ( in two rows):

c1 c2 c3 c4

a 100 0 03-MAY-04
a 0 150 03-MAY-04

There is another table table2 that has the same data (in one row )but in this fashion:


c1 c2 c3 c4
a 100 150 03-MAY-04

The relationship is: where table1.c1=table2.c1 for all columns

My point is to compare the two tables to see if there is any difference. With this data,
the result should be that there is no difference. how should the query be written to
get the difference in the two tables?

Yes Tom, I got it with Gary's query. Thanks again.

hi tom,
i am executing this query (first with all_rows optimizer)
SELECT * FROM(  SELECT R.RECNUMBER, COUNT(R.RECNUMBER) APUESTAS
FROM STKTRANSACTION T, STKRECORD R
WHERE T.TRNSORTEODATE BETWEEN TO_DATE('01-01-2004','DD-MM-YYYY')
AND TO_DATE('31-01-2004','DD-MM-YYYY')
AND R.RECTRNID = T.TRNID
GROUP BY (R.RECNUMBER)  ORDER BY 2 DESC)
WHERE ROWNUM <6

Plan:

Elapsed: 00:00:21.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16195 Card=5 Bytes
          =227420)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=16195 Card=11371 Bytes=227420)
   3    2       SORT (ORDER BY STOPKEY) (Cost=16195 Card=11371 Bytes=2
          84275)

   4    3         SORT (GROUP BY) (Cost=16195 Card=11371 Bytes=284275)
   5    4           HASH JOIN (Cost=14991 Card=223186 Bytes=5579650)
   6    5             INDEX (FAST FULL SCAN) OF 'INDX_CMPID_SRTDATE' (
          NON-UNIQUE) (Cost=2918 Card=70301 Bytes=984214)

   7    5             INDEX (FAST FULL SCAN) OF 'INDX_FK_RECTRNID' (NO
          N-UNIQUE) (Cost=6801 Card=15004857 Bytes=165053427)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1132328  consistent gets
       3254  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          5  rows processed


Now with the First_Rows Hint:

  1  SELECT * FROM(  SELECT /*+ FIRST_ROWS */R.RECNUMBER, COUNT(R.RECNUMBER) APUESTAS
  2  FROM STKTRANSACTION T, STKRECORD R
  3  WHERE T.TRNSORTEODATE BETWEEN TO_DATE('01-01-2004','DD-MM-YYYY')
  4  AND TO_DATE('31-01-2004','DD-MM-YYYY')
  5  AND R.RECTRNID = T.TRNID
  6  GROUP BY (R.RECNUMBER)  ORDER BY 2 DESC)
  7* WHERE ROWNUM <6
SQL> /

RECNUMBER    APUESTAS
---------- ----------
1362            10896
327              7966
315              6081
362              5282
321              5054

Elapsed: 00:00:20.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=16195 Card
          =5 Bytes=227420)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=16195 Card=11371 Bytes=227420)
   3    2       SORT (ORDER BY STOPKEY) (Cost=16195 Card=11371 Bytes=2
          84275)

   4    3         SORT (GROUP BY) (Cost=16195 Card=11371 Bytes=284275)
   5    4           HASH JOIN (Cost=14991 Card=223186 Bytes=5579650)
   6    5             INDEX (FAST FULL SCAN) OF 'INDX_CMPID_SRTDATE' (
          NON-UNIQUE) (Cost=2918 Card=70301 Bytes=984214)

   7    5             INDEX (FAST FULL SCAN) OF 'INDX_FK_RECTRNID' (NO
          N-UNIQUE) (Cost=6801 Card=15004857 Bytes=165053427)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1132335  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          5  rows processed

As you can see the consistent gets are very high on both accounts. But while using First_rows the physical reads come down to 0. So are the consistent gets are high b'coz of count. The stktransaction table has 4752646 records and stkrecord table has twice that. Is there a better way to do this query. We need to generate the Top 5 only.
Any other data required, i will be glad to provide. Waiting for your response. 

Tom
Getting lost with this update.
table a:

tid cash key

100 1999.00 U
100 -99.00 G


s
table b: ( id is unique)

id tid

200 100

table c:

id cash key
200 9.00 U
200 -1.00 G

I need to update table c with cash values from table a using table b for gettting
the id value.

Result expected:

table c:

id cash key

200 1999.00 U
200 -99.00 G


My trial: ( Looks horrible!)

update c set cash = ( select cash from a where a.tid =(select b.tid from b,c
where b.id = c.id and a.key=c.key) ;

table a:

tid cash key

100 1999.00 U
100 -99.00 G


create table a (tid number,cash number(10,2),key char(1));

insert into a values(100,1999.00,'U');
insert into a values(100,-99.00,'G');
commit;

table b: ( id is unique)

id tid

200 100

Create table b (id number unique,tid number);
insert into b values(200,100);
commit;

table c:

id cash key
200 9.00 U
200 -1.00 G

create table c(id number,cash number(10,2),key char(1));
insert into c values(200,9.00,'U');
insert into c values(200,-1.00,'G');
commit;

I need to update table c with cash values from table a using table b for
gettting
the id value.

Result expected:

table c:

id cash key

200 1999.00 U
200 -99.00 G

sorry if that annoyed you, I'm just trying to figure out how to make this text more clear, it doesn't seem to be working.  It is the text you read before submitting comment:

<b>
Please limit this to a comment or question relevant to the other text on this page.  Please do not try to start a new thread of discussion here (I will ignore any followups that are not related to the discussion on this page). Also if your followup includes an example you want me to look at, I'll need it to have a create table, insert into statements and such that I can easily cut and paste into sqlplus myself (like I give you) in order to play with. I spent too many hours turning something like:

I have a table like:

scott@ORA9IR2> desc dept
 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 DEPTNO                                        NUMBER(2)
 DNAME                                         VARCHAR2(14)
 LOC                                           VARCHAR2(13)

with the data:
 
scott@ORA9IR2> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

I *need* to have

I have a table:

create table dept( deptno number(2) primary key, dname varchar2(14), loc varchar2(13) );

with this data:

insert into dept values ( 10, 'accounting', 'new york' );
....

and please -- NO tablespaces/storage clauses, etc. simple, concise examples!
</b>

And the problem was you gave me exactly what I said "don't do".  If anyone has ideas on better wording -- please feel free to contribute.


I'm looking for that sort of stuff (create table, inserts) before I even read the question anymore.



ops$tkyte@ORA9IR2> select * from c;
 
        ID       CASH K
---------- ---------- -
       200          9 U
       200         -1 G
 
ops$tkyte@ORA9IR2> update c
  2     set cash = ( select a.cash
  3                    from a,b
  4                   where b.id = c.id
  5                     and b.tid = a.tid
  6                     and a.key = c.key )
  7  /
 
2 rows updated.
 
ops$tkyte@ORA9IR2> select * from c;
 
        ID       CASH K
---------- ---------- -
       200       1999 U
       200        -99 G
 
ops$tkyte@ORA9IR2>

 

the queries are resolved greatly but i dont have much idea about plsql kindly suggest how to learn the same in a short period.

sapna

Tom,

I have the script below script which gives the values with different datebands entered in master table. I am looking for a output that can be run only with SQL query instead of going for PL/SQL program.

Please advice

-----------------------------------------------------------

SCRIPT :

prompt Created on 22 June 2004 by Sreedhar

set feedback off
set define off

create table CRP_T_PRICE
(
  DATEBAND_FROM          DATE,
  DATEBAND_TO            DATE,
  PRICE                 NUMBER
);

prompt Loading CRP_T_PRICE Table...

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('01-03-2004', 'dd-mm-yyyy'), to_date('31-03-2004', 'dd-mm-yyyy'), 0);

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('01-04-2004', 'dd-mm-yyyy'), to_date('11-05-2004', 'dd-mm-yyyy'), 0);

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('12-05-2004', 'dd-mm-yyyy'), to_date('11-06-2004', 'dd-mm-yyyy'), 6522);

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('12-06-2004', 'dd-mm-yyyy'), to_date('19-06-2004', 'dd-mm-yyyy'), 6522);

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('20-06-2004', 'dd-mm-yyyy'), to_date('31-07-2004', 'dd-mm-yyyy'), 6885);

insert into CRP_T_PRICE (DATEBAND_FROM, DATEBAND_TO, PRICE)
values (to_date('01-08-2004', 'dd-mm-yyyy'), to_date('31-10-2004', 'dd-mm-yyyy'), 6522);

Commit;


set feedback on
set define on
prompt Done.

-----------------------------------------------------------


SQL> SELECT * FROM CRP_T_PRICE;


DATEBAND_FROM    DATEBAND_TO    PRICE
01/03/2004    31/03/2004    0
01/04/2004    11/05/2004    0
12/05/2004    11/06/2004    6522
12/06/2004    19/06/2004    6522
20/06/2004    31/07/2004    6885
01/08/2004    31/10/2004    6522


OUTPUT I AM LOOKING FOR IS ............


DATEBAND_FROM    DATEBAND_TO    PRICE
01/03/2004    11/05/2004    0
12/05/2004    19/06/2004    6522
20/06/2004    31/07/2004    6885
01/08/2004    31/10/2004    6522 

ops$tkyte@ORA9IR2> select min(dateband_from), max(dateband_to), price
  2    from (
  3  select dateband_from, dateband_to, price,
  4         max(rn) over (order by dateband_from) grp
  5    from (
  6  select dateband_from, dateband_to, price,
  7         case when lag(price) over (order by dateband_from) <> price or
  8                   row_number() over (order by dateband_from) = 1
  9              then row_number() over (order by dateband_from)
 10          end rn
 11    from crp_t_price
 12         )
 13         )
 14   group by grp, price
 15   order by 1
 16  /
 
MIN(DATEB MAX(DATEB      PRICE
--------- --------- ----------
01-MAR-04 11-MAY-04          0
12-MAY-04 19-JUN-04       6522
20-JUN-04 31-JUL-04       6885
01-AUG-04 31-OCT-04       6522
 
ops$tkyte@ORA9IR2>


see

"analytics to the rescue"

this is a technique i call "carry forward" -- the max() trick.  we simply mark the beginning of whatever defines our "group" with row_number() (and if we need to carry forward something else -- row_number() || something_else) and use max to carry it forward.

suggest you run the query bit by bit to see what it does (run the inline views one by one, building on each one -- that is how I develop them).
 

I have a quick question. Given the data below:
----
scott@ORA10G> drop table t1;

Table dropped.

scott@ORA10G> create table t1
2 (
3 x number,
4 y number,
5 z number
6 );

Table created.

scott@ORA10G>
scott@ORA10G> insert into t1 values( 1, 2, 3 );

1 row created.

scott@ORA10G> insert into t1 values( 2, 2, 10 );

1 row created.

scott@ORA10G> insert into t1 values( 3, 5, 5 );

1 row created.

scott@ORA10G> commit;

Commit complete.

scott@ORA10G>
scott@ORA10G> select * from t1;

X Y Z
---- ---- ----
1 2 3
2 2 10
3 5 5
------

I want to get all the column values along with average
of each column for all rows. Since the average
will be the same in this case in all rows, is
it possible to not repeat the average values?
Apart from the network traffic saved (say from a
JDBC client query), do you see any other pros/cons
with this approach versus writing 2 different queries
(one for the column and one for the average values)?

I used your "carry forward" trick for this (see below)
but you may have a simpler solution in mind:)

---

scott@ORA10G> column x format 999
scott@ORA10G> column y format 999
scott@ORA10G> column z format 999
scott@ORA10G> column avg_x format 999
scott@ORA10G> column avg_y format 999
scott@ORA10G> column avg_z format 999
scott@ORA10G>
scott@ORA10G> select x, y, z, decode( rn, 1, avg_x, null) avg_x,
2 decode( rn, 1, avg_y, null) avg_y,
3 decode( rn, 1, avg_z, null) avg_z
4 from
5 (
6 select x, y, z, avg_x, avg_y, avg_z,
7 case when avg_x != lag_avg_x or
8 row_num = 1 then
9 row_number() over (order by x, y, z)
10 end rn
11 from
12 (
13 select x, y, z, avg_x, avg_y, avg_z,
14 lag( avg_x) over(order by x, y, z) lag_avg_x,
15 lag( avg_y) over(order by x, y, z) lag_avg_y,
16 lag( avg_z) over(order by x, y, z) lag_avg_z,
17 row_number() over ( order by x, y, z ) row_num
18 from
19 (
20 select x, y, z,
21 avg(x) over() avg_x,
22 avg(y) over() avg_y,
23 avg(z) over() avg_z
24 from t1
25 )
26 ))
27 ;

X Y Z AVG_X AVG_Y AVG_Z
---- ---- ---- ----- ----- -----
1 2 3 2 3 6
2 2 10
3 5 5

ops$tkyte@ORA10G> select avg(x), avg(y), avg(z),
  2         decode( grouping(x), 1, '<<<== avg' )
  3    from t1
  4   group by grouping sets((x,y,z),())
  5  /
 
    AVG(X)     AVG(Y)     AVG(Z) DECODE(GR
---------- ---------- ---------- ---------
         1          2          3
         2          2         10
         3          5          5
         2          3          6 <<<== avg
 

something new to learn!:) yet again!:)

Hi tom
in the above select what does
"group by grouping sets((x,y,z),())"

mean? In particular, what does the "()" imply?
I notice that if you remove it we dont get the
last row (the one with the average)?

Thanx!

I guess "group by grouping sets((x,y,z),())"

menas union the result of
group by x, y, z - with that obtained by no grouping by.
Is that correct?

thanx!

that makes it clear!

hi tom
another question with a suggested solution - let me know
if you have an alternative solution.
same schema as above -this time I want to calculate
the result of a function that takes the minimum of
all x, y, z values and max of all x, y, z values and
then select this result along with all x, y, z values.

Following is my code with the schema and a solution..
thanx!
---
scott@ORA10G> set echo on
scott@ORA10G> set head on
scott@ORA10G> drop table t1;

Table dropped.

scott@ORA10G> create table t1
2 (
3 x number,
4 y number,
5 z number
6 );

Table created.

scott@ORA10G>
scott@ORA10G> insert into t1 values( 1, 2, 3 );

1 row created.

scott@ORA10G> insert into t1 values( 2, 2, 10 );

1 row created.

scott@ORA10G> insert into t1 values( 2, 2, 9 );

1 row created.

scott@ORA10G> insert into t1 values( 3, 5, 5 );

1 row created.

scott@ORA10G> commit;

Commit complete.

scott@ORA10G>
scott@ORA10G> select * from t1;

X Y Z
---- ---- ----
1 2 3
2 2 10
2 2 9
3 5 5

scott@ORA10G>
scott@ORA10G> column x format 999
scott@ORA10G> column y format 999
scott@ORA10G> column z format 999
scott@ORA10G> column max_x format 999.99
scott@ORA10G> column max_y format 999.99
scott@ORA10G> column max_z format 999.99
scott@ORA10G> column max format 999.99
scott@ORA10G> column details format 999.99
scott@ORA10G>
scott@ORA10G> select x, y, z
2 from t1;

X Y Z
---- ---- ----
1 2 3
2 2 10
2 2 9
3 5 5

scott@ORA10G>
scott@ORA10G> /* a function that calculates something based on the
scott@ORA10G> the max and min of all x,y,z each.
scott@ORA10G>
scott@ORA10G> */
scott@ORA10G> create or replace function f( max_x_y_z number, min_x_y_z number )
2 return varchar2
3 is
4 begin
5 -- some computation
6 return 'TESTING';
7 end;
8 /

Function created.

scott@ORA10G> show errors;
No errors.
scott@ORA10G> column f_result format a10
scott@ORA10G> /* we need the result of x, y, z, rows alongwith the
scott@ORA10G> result of the above function f()
scott@ORA10G> */
scott@ORA10G>
scott@ORA10G> select x, y, z, f( max_all, min_all ) f_result
2 from
3 (
4 select x, y, z, greatest( max_x, max_y, max_z) max_all,
5 least ( min_x, min_y, min_z ) min_all
6 from
7 (
8 select x, y, z,
9 max(x) over() max_x,
10 max(y) over() max_y,
11 max(z) over() max_z,
12 min(x) over() min_x,
13 min(y) over() min_y,
14 min(z) over() min_z
15 from t1
16 )
17 );

X Y Z F_RESULT
---- ---- ---- ----------
1 2 3 TESTING
2 2 10 TESTING
2 2 9 TESTING
3 5 5 TESTING

scott@ORA10G> spool off

we can certainly simplify it a bit and given that the f(a,b) is constant for all rows in the query -- optimize it a bit as well:

ops$tkyte@ORA9IR2> create or replace function f(a in number, b in number) return varchar2
  2  as
  3  begin
  4      dbms_application_info.set_client_info( userenv('client_info')+1 );
  5      return 'testing';
  6  end;
  7  /
 
Function created.

that'll let us count how often the function is called:

 
ops$tkyte@ORA9IR2> column f format a10
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, y, z, f( min_xyz, max_xyz ) f
  2    from (
  3  select x, y, z,
  4         min(least(x,y,z)) over () min_xyz,
  5         max(greatest(x,y,z)) over () max_xyz
  6    from t1
  7         )
  8  /
 
         X          Y          Z F
---------- ---------- ---------- ----------
         1          2          3 testing
         2          2         10 testing
         2          2          9 testing
         3          5          5 testing
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
4


<b>that shows the simplification, but also shows that the function was called 4 times.  We can use a scalar subquery here to reduce that to one in this case (since the inputs to the function are invariant)</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, y, z, (select f( min_xyz, max_xyz )  from dual) f
  2    from (
  3  select x, y, z,
  4         min(least(x,y,z)) over () min_xyz,
  5         max(greatest(x,y,z)) over () max_xyz
  6    from t1
  7         )
  8  /
 
         X          Y          Z F
---------- ---------- ---------- ----------
         1          2          3 testing
         2          2         10 testing
         2          2          9 testing
         3          5          5 testing
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1
 
 

Thank you!! I liked the simplification a lot and
also the scalar subquery idea!!

Hi Tom
Could you kindly explain why the scalar subquery
trick reducees the function call to 1 from 4?

Tom,

Thanks, very much useful to me.

I have one doubt, how to limit, the result that you get in analytics for the first record or till 10 records, as we do say row_num <2 or < 10

i tried this ..

SQL> SELECT month_num,year_num,
  2  MIN(month_num) OVER(PARTITION BY year_num ORDER BY month_num) as p_cmin
  3  --RANGE UNBOUNDED PRECEDING) as p_cmin
  4  FROM tb_etr003_geci_etmon_financial a
  5  where a.type_nam='Project Space'
  6                     AND object_nam = 'Development of DEQ coil manuf'
  7                     AND revision_nam='2910885710716221'
  8                     AND relobj_type_nam='Benefit Details';

 MONTH_NUM   YEAR_NUM     P_CMIN
---------- ---------- ----------
        10       2004         10
        10       2004         10
        10       2004         10
        10       2004         10
        11       2004         10
        11       2004         10
        11       2004         10
        11       2004         10
        12       2004         10
        12       2004         10
        12       2004         10

 MONTH_NUM   YEAR_NUM     P_CMIN
---------- ---------- ----------
        12       2004         10
         1       2005          1
         1       2005          1
         1       2005          1
         1       2005          1

i have multiple values for 10 2004 ..(first 4 records), how to get only the first one and how to get the first 4 (here, since they are having same value)

RANGE UNBOUNDED PRECEDING -- means? .. where can i see this explanation, or can you explain in your quick words...

thanks
 

Hi Tom
Please consider the following schema:
---
scott@ORA92I> drop table t;

Table dropped.

scott@ORA92I> create table t
2 ( x number,
3 y date
4 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into t
2 select rownum x, sysdate
3 from all_objects
4 where rownum <= 10;

10 rows created.

scott@ORA92I>
scott@ORA92I> -- want to set the value of y to descending order of
scott@ORA92I> -- date based on the order of x values - e.g. the largest
scott@ORA92I> -- value of x should correspond to today, the second
scott@ORA92I> -- largest to yesterday and so on.
scott@ORA92I> update ( select x, y, rownum from t order by x )
2 set y = sysdate - rownum;
update ( select x, y, rownum from t order by x )
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

----

How can I update the table t so that the largest value of
x has y as sydate, the second largest has y as sysdate-1
and so on...This would be done through rowum but not sure
how..

Thanx for a great site!!

ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
  2  ( x number PRIMARY KEY,
  3    y date
  4  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select rownum x, sysdate
  3  from all_objects
  4  where rownum <= 10;
 
10 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t a
  2  using ( select x, rownum-1 r
  3            from ( select *
  4                     from t
  5                    order by x) ) b
  6  on (a.x = b.x)
  7  when matched then update set y = sysdate-r
  8  when not matched then insert (x) values(null)
  9  /
 
10 rows merged.


the when not matched will never happen of course. 

Consider the following schema..
----
scott@ORA92I> set head on
scott@ORA92I> set echo on
scott@ORA92I> drop table t;

Table dropped.

scott@ORA92I> create table t
2 (
3 x number,
4 y number,
5 dt date
6 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into t ( x, y, dt )
2 values ( 100, 200, to_date( '06/01/2004', 'mm/dd/yyyy') );

1 row created.

scott@ORA92I> insert into t ( x, y, dt )
2 values ( 100, 200, to_date( '06/02/2004', 'mm/dd/yyyy') );

1 row created.

scott@ORA92I> insert into t ( x, y, dt )
2 values ( 100, 200, to_date( '06/03/2004', 'mm/dd/yyyy') );

1 row created.

scott@ORA92I> insert into t ( x, y, dt )
2 values ( 100, 200, to_date( '06/05/2004', 'mm/dd/yyyy') );

1 row created.

scott@ORA92I>
scott@ORA92I> commit;

Commit complete.

scott@ORA92I>
scott@ORA92I> column x format 99999
scott@ORA92I> column y format 99999
scott@ORA92I> alter session set nls_date_format='mm/dd/yyyy';

Session altered.

scott@ORA92I>
scott@ORA92I> select * from t;

X Y DT
------ ------ ----------
100 200 06/01/2004
100 200 06/02/2004
100 200 06/03/2004
100 200 06/05/2004

I want to get an output of
X Y DT
------ ------ ----------
100 200 06/01/2004
100 200 06/02/2004
100 200 06/03/2004
0 0 06/04/2004 <--- extra row for the missing day
100 200 06/05/2004

Is it possible?

Thanx!

Hi tom
Following is the schema:
scott@ORA10G>
scott@ORA10G> drop table t1;

Table dropped.

scott@ORA10G> create table t1
2 (
3 name varchar2(30),
4 type varchar2(30),
5 value number
6 );

Table created.

scott@ORA10G>
scott@ORA10G> insert into t1( name, type, value )
2 values( 'name1', 'type1', 1 );

1 row created.

scott@ORA10G> insert into t1( name, type, value )
2 values( 'name1', 'type1', 2 );

1 row created.

scott@ORA10G> insert into t1( name, type, value )
2 values( 'name2', 'type1', 3 );

1 row created.

scott@ORA10G> insert into t1( name, type, value )
2 values( 'name1', 'type1', 5 );

1 row created.

scott@ORA10G> insert into t1( name, type, value )
2 values( 'name1', 'type2', 5 );

1 row created.

scott@ORA10G> commit;

Commit complete.

scott@ORA10G>
scott@ORA10G> column name format a15
scott@ORA10G> column type format a10
scott@ORA10G> column value format 999
scott@ORA10G> column shared_count format 999
scott@ORA10G> select name, type, value
2 from t1;

NAME TYPE VALUE
--------------- ---------- -----
name1 type1 1
name1 type1 2
name2 type1 3
name1 type1 5
name1 type2 5

----------------------------------
Now the required result is as shown below..
I need to find the count of cases where columns
name and type have same value and then display
them along side the above results as:
scott@ORA10G> NAME TYPE VALUE SHARED_COUNT
scott@ORA10G> --------------- ---------- -----
scott@ORA10G> name1 type1 1 3
scott@ORA10G> name1 type1 2 3
scott@ORA10G> name2 type1 3 1
scott@ORA10G> name1 type1 5 3
scott@ORA10G> name1 type2 5 1

------------

My answer to this is a little convoluted - I thought
you may be able to give a better answer...
Let me know..
Following is the way I did it..
-----
scott@ORA10G> select name, type, value,
2 max( shared_count ) over( partition by name, type order by name, type)
3 shared_count
4 from
5 (
6 select name, type, value,
7 row_number()
8 over( partition by name, type order by name, type) shared_count
9 from t1
10 order by name, type
11 );

NAME TYPE VALUE SHARED_COUNT
--------------- ---------- ----- ------------
name1 type1 1 3
name1 type1 2 3
name1 type1 5 3
name1 type2 5 1
name2 type1 3 1

------------

Thank you!
--

ops$tkyte@ORA9IR2> l
  1  select name, type, value, count(*) over (partition by name, type) cnt
  2    from t1
  3* order by name, type
ops$tkyte@ORA9IR2> /
 
NAME   TYPE        VALUE        CNT
------ ------ ---------- ----------
name1  type1           1          3
name1  type1           2          3
name1  type1           5          3
name1  type2           5          1
name2  type1           3          1
 
 

Hi Tom,

In order to get the nth maximum salary, which approach is good in terms of performance and efficiency?

1. SELECT MIN(sal)
FROM ( SELECT sal
FROM ( SELECT DISTINCT sal
FROM emp
ORDER BY sal
DESC )
WHERE ROWNUM <= &nthmaxsalary );

2. SELECT MAX(sal)
FROM emp
WHERE LEVEL = &nthmaxsalary
CONNECT BY PRIOR sal > sal;

3. SELECT DISTINCT t.sal
FROM ( SELECT sal, DENSE_RANK()
OVER (ORDER BY sal DESC) salrank
FROM emp)t
WHERE t.salrank = &nthmaxsalary ;


Thxs in advance

hi tom
slight change in my requirements:
----
schema:
scott@ORA10G> drop table t1;

Table dropped.

scott@ORA10G> create table t1
2 (
3 name varchar2(30),
4 type varchar2(30),
5 id number,
6 value number
7 );

Table created.

scott@ORA10G>
scott@ORA10G> insert into t1( name, type, id, value )
2 values( 'name1', 'type1', 1, 1 );

1 row created.

scott@ORA10G> insert into t1( name, type, id, value )
2 values( 'name1', 'type1', 2, 2 );

1 row created.

scott@ORA10G> insert into t1( name, type, id, value )
2 values( 'name2', 'type1', 3, 3 );

1 row created.

scott@ORA10G> insert into t1( name, type, id, value )
2 values( 'name1', 'type2', 4, 5 );

1 row created.

scott@ORA10G> insert into t1( name, type, id, value )
2 values( 'name2', 'type1', 1, 5 );

1 row created.

scott@ORA10G> commit;

Commit complete.

scott@ORA10G>
scott@ORA10G> column name format a15
scott@ORA10G> column type format a10
scott@ORA10G> column value format 999
scott@ORA10G> column id format 999
scott@ORA10G> column shared_count format 999
scott@ORA10G> set head on
scott@ORA10G> select name, type, id, value
2 from t1;

NAME TYPE ID VALUE
--------------- ---------- ---- -----
name1 type1 1 1
name1 type1 2 2
name2 type1 3 3
name1 type2 4 5
name2 type1 1 5

----

The required results:
---
cott@ORA10G> NAME TYPE ID VALUE SHARED_COUNT
scott@ORA10G> --------------- ---------- ---- ----- -----------
scott@ORA10G> name1 type1 1 1 1
scott@ORA10G> name1 type1 2 2 2
scott@ORA10G> name2 type1 3 3 1
scott@ORA10G> name3 type2 4 5 1
scott@ORA10G> name2 type1 1 5 2
scott@ORA10G>
scott@ORA10G>

---
Basically
Count rows such that different names of the same type have the same ID. For example, in the above case, count for row # 2 and 5 is 2 because for the same type type1, there are two different names - name1 and name2 that share the same ID of 1.

Thank you!

scott@ORA10G> select name, type, id, value,
2 count(*) over( partition by type, id order by type, id ) shared_count
3 from t1;

NAME TYPE ID VALUE SHARED_COUNT
--------------- ---------- ---- ----- ------------
name1 type1 1 1 2
name2 type1 1 5 2
name1 type1 2 2 1
name2 type1 3 3 1
name1 type2 4 5 1
----
lemme know if this is what you would have suggested..
thanx!

The above solution does not take care of
the case when you have another row
that has name1, type1 and the same global id :(

a verbaitim reading of:

Count rows such that different names of the same type have the same ID. 

results in this query:

ops$tkyte@ORA9IR2> select name, type, id, value, count(distinct name) over (partition by type, id) cnt
  2    from t1;
 
NAME                           TYPE             ID      VALUE        CNT
------------------------------ -------- ---------- ---------- ----------
name1                          type1             1          1          2
name2                          type1             1          5          2
name1                          type1             2          2          1
name2                          type1             3          3          1
name1                          type2             4          5          1
 

that works perfectly!:)

Have a great day!:)

stuck with this query:

create table tt1( a number,b varchar2(10));
create table tt2( a number,c varchar2(10));

insert into tt1 values(100,'abc');
insert into tt1 values(200,'abc');
insert into tt1 values(200,'abc');
insert into tt1 values(200,'bcd');

insert into tt2 values(100,'abc');
insert into tt2 values(100,'bcd');


output reqd: ( combining the values from both tables)

t1.a t1.b
-----------

600 abc
300 bcd

select tt1.b,sum(tt1.a+tt2.a)
from tt1,tt2
where tt1.b=tt2.c
group by tt1.b;

gives

B SUM(TT1.A+TT2.A)
---------- ----------------
abc 800
bcd

ops$tkyte@ORA9IR2> select sum(a), x
  2    from (select sum(a) a, b x from tt1 group by b
  3          union all
  4          select sum(a) a, c x from tt2 group by c)
  5   group by x
  6  /
 
    SUM(A) X
---------- ----------
       600 abc
       300 bcd
 

stuck with this query:

create table tt1(id number,sdate date,edate date,amt number,flag char(1));

create table tt2(id number,sdate date,edate date,amt number);


insert into tt1 values(1,'01-AUG-04','31-AUG-04',100,NULL);
insert into tt1 values(2,'01-AUG-04','31-AUG-04',100,NULL);
insert into tt1 values(3,'02-AUG-04','31-AUG-04',100,'Y');
insert into tt1 values(4,'02-AUG-04','31-AUG-04',100,'Y');
insert into tt1 values(5,'03-AUG-04','31-AUG-04',100,NULL);

insert into tt2 values(3,'02-AUG-04','31-AUG-04',200);
insert into tt2 values(4,'02-AUG-04','31-AUG-04',200);


SHOULD BE GROUPED BY TT1.SDATE,TT1.EDATE AND
(IF TT1.FLAG='Y' THEN TAKE THE CORRESPONDING AMT FROM TT2 WHERE TT1.ID=TT2.ID AND TT1.SDATE=TT2.SDATE
AND TT1.EDATE = TT2.EDATE)

THE OUTPUT TO BE:


TT1.SDATE TT1.EDATE AMT

01-AUG-04 31-AUG-04 200
02-AUG-04 31-AUG-04 400
03-AUG-04 31-AUG-04 100

 1  select sdate, edate,
  2  sum(decode(flag, null, amt, (select amt from tt2 where tt1.id = tt2.id))) amt
  3  from tt1
  4* group by sdate, edate
SQL> /

SDATE     EDATE            AMT
--------- --------- ----------
01-AUG-04 31-AUG-04        200
02-AUG-04 31-AUG-04        400
03-AUG-04 31-AUG-04        100 

  1  select sdate, edate,
  2  sum(decode(flag, null, amt, (select amt from tt2 where tt1.id = tt2.id))) total
  3  from tt1
  4* group by sdate, edate
SQL> /

SDATE     EDATE          TOTAL
--------- --------- ----------
01-AUG-04 31-AUG-04        200
02-AUG-04 31-AUG-04        400
03-AUG-04 31-AUG-04        100 

hi tom
i have scenario where I am generating report based
on a period. The numbers involved are rolled up - so
we have a table where we store hourly data points,
another table where we store daily data points etc.

I am writing a query where if the data is in the daily point I pick data from there - otherwise, I pick
it from the hourly data points table. I am writing
it in two queries. The first selects data from the
daily points table. If there are no records in this
I select data from hourly points table. Is this the
right approach to do it or would you have done it
differently?

Thank you!

unfortunately we are not in a position to use MVs
as the infrastructure is already in place...Given that
is the reality, how would you approach this issue?

btw, how would the MVs know if they have to go to daily
and if not to hourly data - is there a mechanism that
helps you with this in MV?

Thanx!

one reason I believe we dont have MVs is that
we need history of rollup points...

good point :-) - so the way you would have done it is:
store history of points at the lowest granular level
(hourly)

1. The daily, weekly, monthly, quarterly, yearly
etc. would come from materialized views built on
top of the hourly table, correct?
2. I suppose stats like min, max, avg etc would
also come from MVs?

3. Assuming I have an MV based solution,
can you briefly explain how MVs can automatically
go to hourly if data does not exist for daily? Is
this one of the functionalities of MV?

thanx!

Dear Tom,
Is there any other way to put this query?

sql> select * from t where x is not null and y is not null;

Please do reply.

Full outer join: Is this the right way? Looks like
I am missing something here. Please have a look.

create table p1 ( sdate date,ccy char(3),amt number);
create table p2 as select * from p1;
create table p3 (sdate date,ccy char(3),amt_p1 number,amt_p2 number);

insert into p1 values('16-AUG-04','BCD',100);
insert into p1 values('16-AUG-04','CDE',200);
insert into p1 values('16-AUG-04','ABC',300);
insert into p1 values('16-AUG-04','XYZ',300);

insert into p2 values('16-AUG-04','EFG',100);
insert into p2 values('16-AUG-04','CDE',200);
insert into p2 values('16-AUG-04','ABC',300);
insert into p2 values('16-AUG-04','BCD',100);

table p3 to be combined from p1 and p2: (output required)

sdate ccy amt_p1 amt_p2


16-AUG-04 ABC 300 300
16-AUG-04 BCD 100 100
16-AUG-04 CDE 200 200
16-AUG-04 EFG 0 100
16-AUG-04 XYZ 300 0

I used this query but the sdate and ccy are missing for one row ie., 'EFG' for p2.

SELECT P1.SDATE,P1.CCY,nvl(P1.AMT,0),nvl(P2.AMT,0)
FROM P2 FULL OUTER JOIN P1
ON P1.SDATE = P2.SDATE
AND P1.CCY = P2.CCY;

SDATE CCY AMT AMT
--------- --- ---------- ----------
16-AUG-04 ABC 300 300
16-AUG-04 BCD 100 100
16-AUG-04 CDE 200 200
0 100
16-AUG-04 XYZ 100 0

<quote>actuallhy, daily on hourly, weekly on daily, monthly on weekly, and so on</quote>

monthly on weekly? ... hmmm ... weeks do not always fit into months, or years for that matter. The concept of the "week" is quite murky ... use 'IW' or 'WW' format date masks and one gets different definitions ... with 'W' and 'WW' weeks don't always have 7 days ... it is true though that 'W'-weeks roll-up into months (and hence years) and 'WW'-weeks roll-up into years (but not to months).

The hierarchies are:
Hour >-- Day >-- Month >-- Quarter >-- Year
Hour >-- Day >-- Week ('IW' ISO)
Hour >-- Day >-- Week ('W') >-- Month >-- Quarter >-- Year
Hour >-- Day >-- Week ('WW') >-- Year

Thiru,

You need to NVL the first two columns also. 

 1  SELECT nvl(P1.SDATE, p2.sdate), nvl(P1.CCY, p2.ccy), nvl(P1.AMT,0),nvl(P2.AMT,0)
  2  FROM P2 FULL OUTER JOIN P1
  3  ON P1.SDATE  = P2.SDATE
  4* AND P1.CCY = P2.CCY
SQL> /

NVL(P1.SD NVL NVL(P1.AMT,0) NVL(P2.AMT,0)
--------- --- ------------- -------------
16-AUG-04 BCD           100           100
16-AUG-04 CDE           200           200
16-AUG-04 ABC           300           300
16-AUG-04 EFG             0           100
16-AUG-04 XYZ           300             0 

Thanks a lot.

"it was the fact that a mv can roll into an mv can roll into an mv i was trying
to get across. you can aggregate the aggregates as needed. "

In our case the hourly data is kept for 2 weeks, the
daily data for a month and the monthly for a year.
Does this have a bearing on an MV based solution
since a query will depend on the real data underlying
in the tables. In other words, how can we have mvs on
mvs with different "purging" periods?

I guess in cases where you have to do this (due to sheer volume of data) mvs are not possible. So in such a case
if we have, say hourly data to yearly data and if we want
an mv based solution, we will have to keep the hourly
data for a year for it to work, correct?

thank you!!

First the schema:

scott@ORA92I> drop table t1;

Table dropped.

scott@ORA92I> create table t1
2 (
3 x varchar2(10),
4 y number
5 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into t1( x, y ) values ( 'A', 2 );

1 row created.

scott@ORA92I> insert into t1( x, y ) values ( 'A', 3 );

1 row created.

scott@ORA92I> insert into t1( x, y ) values ( 'B', 3 );

1 row created.

scott@ORA92I> --insert into t1( x, y ) values ( 0, 0 );
scott@ORA92I> --insert into t1( x, y ) values ( 0, 0 );
scott@ORA92I>
scott@ORA92I> select * from t1;

X Y
---------- ----------
A 2
A 3
B 3
------------------------------------
requirement is to get a flag indicating if
at least one record exists for x = 'A'
and so on. Possible values of x are
'A', 'B', 'C'; for the above data we
would get

A B C
- - -
Y Y N

my solution is:

scott@ORA92I> select max(decode( x, 'A', 'Y', 'N')) A_count,
2 max(decode( x, 'B', 'Y', 'N')) B_count,
3 max(decode( x, 'C', 'Y', 'N')) C_count
4 from t1;

A B C
- - -
Y Y N

the problem is I dont wanna count beyond the first
record...Do you have a better solution?

thanx!

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select nvl((select 'Y' from dual where exists(select null from t1 where x='A')),'N') A,
  2         nvl((select 'Y' from dual where exists(select null from t1 where x='B')),'N') B,
  3         nvl((select 'Y' from dual where exists(select null from t1 where x='C')),'N') C
  4    from dual;
 
A B C
- - -
Y Y N
 
 

First the schema and data
----
scott@ORA92I> set echo on
scott@ORA92I> set head on
scott@ORA92I> alter session set nls_date_format = 'YYYY-MM-DD HH:MI:SS';

Session altered.

scott@ORA92I> --schema
scott@ORA92I>
scott@ORA92I> drop table curr_t1;

Table dropped.

scott@ORA92I> create table curr_t1
2 (
3 a varchar2(10),
4 value number,
5 time_of_entry date
6 );

Table created.

scott@ORA92I>
scott@ORA92I> drop table daily_rollup_t1;

Table dropped.

scott@ORA92I> create table daily_rollup_t1
2 (
3 a varchar2(10),
4 maximum number,
5 minimum number,
6 average number,
7 sample_count number,
8 time_rolled_up date
9 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into curr_t1( a, value, time_of_entry )
2 values( 'a', 1, sysdate );

1 row created.

scott@ORA92I> insert into curr_t1( a, value, time_of_entry )
2 values( 'b', 7, sysdate );

1 row created.

scott@ORA92I>
scott@ORA92I> commit;

Commit complete.

scott@ORA92I> select * from curr_t1;

A VALUE TIME_OF_ENTRY
---------- ---------- -------------------
a 1 2004-08-30 03:50:09
b 7 2004-08-30 03:50:09

scott@ORA92I>
scott@ORA92I> insert into daily_rollup_t1( a, maximum, minimum, average, sample_count,
2 time_rolled_up )
3 values( 'a', 10, 3, 5, 2, sysdate-1 );

1 row created.

scott@ORA92I> commit;

Commit complete.

scott@ORA92I>
scott@ORA92I> select *
2 from daily_rollup_t1;

A MAXIMUM MINIMUM AVERAGE SAMPLE_COUNT TIME_ROLLED_UP
---------- ---------- ---------- ---------- ------------ -------------------
a 10 3 5 2 2004-08-29 03:50:09

----------------



First table curr_t1 contains the current values corresponding to the column a( one row per unique value of a).Second table daily_rollup_t1 contains the rolled up value of acolumn.

-- Now I want to get the average max, min of all daily
-- rollup values including the value in curr_t1. The degenerate case would be when there are no records in the daily_rollup_t1 in which case I will get the value in curr_t1 as average, max, min etc. There will be only one value of "a" column and the latest
date in the table curr_t1.
In the above data, my output should be:
A AVERAGE MAXIMUM MINIMUM LATEST_VALUE
----------- ------- ------- ------- ------------
a 3.67 10 1 1
b 7 7 7 7

My attempt is given below - do you have any other better
alternatives in mind?

scott@ORA92I> select a,
2 sum_value/sum_sample_count as average,
3 maximum,
4 minimum,
5 last_known_value
6 from
7 (
8 select a,
9 sum( value ) sum_value,
10 sum( sample_count ) sum_sample_count,
11 max( maximum ) maximum,
12 min( minimum ) minimum,
13 max( last_known_value ) as last_known_value
14 from
15 (
16 select a, value as maximum,
17 value as minimum,
18 value as last_known_value,
19 1 as sample_count,
20 value as value
21 from curr_t1
22 union
23 select a, max( maximum), min(minimum),
24 max(0) as last_known_value,
25 sum(sample_count),
26 sum(average*sample_count) as value
27 from daily_rollup_t1
28 group by a
29 )
30 group by a
31 );

A AVERAGE MAXIMUM MINIMUM LAST_KNOWN_VALUE
---------- ---------- ---------- ---------- ----------------
a 3.66666667 10 1 1
b 7 7 7 7

I am posting the same schema with more meaningful
schema names. If you did not intend me to post
this again, please accept my apologies and ignore this
query.
Also, the reason we need to query the current value
is that the daily rollup happens once a day and this value is useful till usch time that the rollup has not happenned.
We dont use MVs because we need history and cant afford
(spacewise) to store all the data for the lowest level.
----
scott@ORA92I> set head on
scott@ORA92I> alter session set nls_date_format = 'YYYY-MM-DD HH:MI:SS';

Session altered.

scott@ORA92I> --schema
scott@ORA92I>
scott@ORA92I> drop table curr_t1;

Table dropped.

scott@ORA92I> create table curr_t1
2 (
3 info_type varchar2(10),
4 value number,
5 time_of_entry date
6 );

Table created.

scott@ORA92I>
scott@ORA92I> drop table daily_rollup_t1;

Table dropped.

scott@ORA92I> create table daily_rollup_t1
2 (
3 info_type varchar2(10),
4 maximum number,
5 minimum number,
6 average number,
7 sample_count number,
8 time_rolled_up date
9 );

Table created.

scott@ORA92I>
scott@ORA92I> insert into curr_t1( info_type, value, time_of_entry )
2 values( 'a', 1, sysdate );

1 row created.

scott@ORA92I> insert into curr_t1( info_type, value, time_of_entry )
2 values( 'b', 7, sysdate );

1 row created.

scott@ORA92I>
scott@ORA92I> commit;

Commit complete.

scott@ORA92I> select * from curr_t1;

INFO_TYPE VALUE TIME_OF_ENTRY
---------- ---------- -------------------
a 1 2004-08-31 08:33:04
b 7 2004-08-31 08:33:04

scott@ORA92I>
scott@ORA92I> insert into daily_rollup_t1( info_type, maximum, minimum, average, sample_count,
2 time_rolled_up )
3 values( 'a', 10, 3, 5, 2, sysdate-1 );

1 row created.

scott@ORA92I> commit;

Commit complete.

scott@ORA92I>
scott@ORA92I> select *
2 from daily_rollup_t1;

INFO_TYPE MAXIMUM MINIMUM AVERAGE SAMPLE_COUNT TIME_ROLLED_UP
---------- ---------- ---------- ---------- ------------ -------------------
a 10 3 5 2 2004-08-30 08:33:04


First table curr_t1 contains the current values corresponding
to the column info_type( one row per unique value of info_type).
Second table daily_rollup_t1 contains the rolled up value of info_type
column.
Now I want to get the average max, min of all daily
rollup values including the value in curr_t1. The degenerate
case would be when there are no records in the daily_rollup_t1
in which case I will get the value in curr_t1 as average, max
min etc. There will be only one value of "info_type" column and the latest
date in the table curr_t1.
In the above data, my output should be:

INFO_TYPE AVERAGE MAXIMUM MINIMUM LATEST_VALUE
----------- ------- ------- ------- ------------
a 3.67 10 1 1
b 7 7 7 7

scott@ORA92I> -- my attempt
scott@ORA92I>
scott@ORA92I> select info_type,
2 sum_value/sum_sample_count as average,
3 maximum,
4 minimum,
5 last_known_value
6 from
7 (
8 select info_type,
9 sum( value ) sum_value,
10 sum( sample_count ) sum_sample_count,
11 max( maximum ) maximum,
12 min( minimum ) minimum,
13 max( last_known_value ) as last_known_value
14 from
15 (
16 select info_type, value as maximum,
17 value as minimum,
18 value as last_known_value,
19 1 as sample_count,
20 value as value
21 from curr_t1
22 union
23 select info_type, max( maximum), min(minimum),
24 max(0) as last_known_value,
25 sum(sample_count),
26 sum(average*sample_count) as value
27 from daily_rollup_t1
28 group by info_type
29 )
30 group by info_type
31 );

INFO_TYPE AVERAGE MAXIMUM MINIMUM LAST_KNOWN_VALUE
---------- ---------- ---------- ---------- ----------------
a 3.66666667 10 1 1
b 7 7 7 7

Hi Tom,

How do we get a cumulative resultset for a column. To be more clear, if I have:

create table t (dt date,id varchar2(6),amt number);
insert into t values('09-SEP-04','ABC',1000);
insert into t values('10-SEP-04','ABC',1000);
insert into t values('13-SEP-04','ABC',1000);

How would we get a result wherein the amt column is summed up for each day and also the days in the month does not have gaps ( assuming we have another table if required for the days of the month)

So the result would be:

dt id cumulative_amt

09-SEP-04 ABC 1000
10-SEP-04 ABC 2000
11-SEP-04 ABC 2000
12-SEP-04 ABC 2000
13-SEP-04 ABC 3000

Do we have to use analytic function for this query?

Thanks a million.

If the query is for 12-SEP-04, then it should return 4 rows with the same cumulative amount of 10th as there are no records for 11th and 12th. If I have a table with all dates for the month/year, then a join with this second cal_days table produce the required result. I am running on 9.2 and your query worked fine.
SQL> select dt, id, sum(amt) over (partition by id order by dt) amt
  2    from t;

DT        ID            AMT
--------- ------ ----------
09-SEP-04 ABC          1000
10-SEP-04 ABC          2000
13-SEP-04 ABC          3000 

If I have a table with calendar days, then can I join this table for getting the gaps in days and the cumulative amount of the previous day carried over?

Thanks

I am sorry but my question did not mention a word about "estimate the size of an IOT".

Hi tom

I am using Oracle 9i. I have a table with column Dept and Empnum. This table will have multiple Empnum for a Dept.

For Example:

Dept Empnum

1 A1
1 A2
1 A3
2 B1
2 B2
3 C1

I need a single query which gives me the output as


Dept Empnum(s)
1 A1,A2,A3
2 B1,B2
3 C1

Can you please help me in this.

Thanks
Jaravam

Hi Tom,

There are three ways to count the number of rows in the table. It seems that there are no performance differences even if it is a million rows table with 400 columns. Is it?

select (*) from t1;
select (1) from t1;
select (primary_key_column) from t1;


Oracle 9204. Solaris 5.9

Thanks so much for your help.

Can you please explain diff between following queries?

SELECT c.* FROM scott.cust c, scott.consult co
WHERE c.customer_no = co.customer_no(+) and co.customer_no is null


SELECT c.* FROM scott.cust c, scott.consult co
WHERE c.customer_no = co.customer_no(+)

I executed following query

select * from cust where customer_no not in
(select customer_no from consult)

No rows returned

SELECT c.* FROM scott.cust c, scott.consult co
WHERE c.customer_no = co.customer_no(+) and co.customer_no is null

100000+ rows returned

Should't they return same number of rows?

Hello Tom,
I need a query which calculates the avg(sal) for every three rows.Is there any straight SQL approach available or we have to go in for Analytic functions?Please do reply.
Bye

Hello Tom,
Thanks for your reply.I used a query like
sql>select avg(sal) from emp
group by mod(rownum,3)
having mod(rownum,3) = 0;
But this results in only one row.Can this query be modified
to achieve the result as you have done?
Please reply.
Bye!

So far good. But If I use a ref cursor in JAVA or Oracle Forms, How would I display the result set? Is it something that the GUI tools has to deal with?

Thanks

Hello Tom,
I have some questions for you. I am planning to delete some records from one of the tables
Table structure is

DESC T

Name Null? Type
---------------- ------- --------
CHANGE_ID NOT NULL VARCHAR2(10)
CLAIM_CLAIM_ID NOT NULL VARCHAR2(10)
TABLE_NAME NOT NULL VARCHAR2(2)
FIELD_NAME NOT NULL VARCHAR2(2)
ACTION NOT NULL VARCHAR2(2)
USER_USER_ID NOT NULL VARCHAR2(10)
CHANGED_DATE NOT NULL DATE
OLD_VALUE VARCHAR2(40)
NEW_VALUE VARCHAR2(40)

I am trying to delete records on the basis of CHANGED_DATE column. Due to some policy we have to keep records for 7 years so for deleting data older than 7 years I wrote following query but I think I am doing something wrong here.
Query which I used for counting rows to be deleted was

select count(*) from t where changed_date<(select (sysdate-(365*7)) from dual);


COUNT(*)
----------
16330

Now when I was crosschecking the result I came to know that I have done something wrong> I selected min(changed_date)and result was as under.

select min(to_date(changed_date,'MM-DD-YYYY')) from t;

MIN(TO_DAT
----------
06-12-0001

So what I guess that my table has data which starts from 12 July 2001 so I cant delete any data as per our company's data retention policy of 7 years.

So please help me in solving my doubt that in which step I did mistake? I mean did I do any mistake while selecting data older than 7 years from the table? If so please give me correct SQL to featch data older than 7 years.

Thanks,
Scott

beware implicit conversions.

changed_date is a date.

your default date format probably has YY or RR in it.

yoou to_date( changed_date , .... ) implicitly converts the changed_date into a string, for to-date takes a STRING.

Consider:

ops$tkyte@ORA9IR2> create table t ( dt date );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( sysdate );
 
1 row created.
 
ops$tkyte@ORA9IR2> select to_char(dt,'dd-mon-yyyy'),
  2         to_date(dt,'dd-mon-yyyy'),
  3         to_CHAR( to_date(dt,'dd-mon-yyyy') , 'dd-mon-yyyy' )
  4    from t;
 
TO_CHAR(DT, TO_DATE(D TO_CHAR(TO_
----------- --------- -----------
17-jan-2005 17-JAN-05 17-jan-0005



the DT is really 2005, to_date( dt, .... ) converted it using my default date mask (dd-mon-yy) and then we applied YYYY to it -- that is the year "5"


So the DT data is OK, your implicit conversions -- NOT..


Try this:

select to_CHAR( min(changed_date), 'MM-DD-YYYY' ) from t;


and also -- there are not 365 days in a year, there are however 12 months.  You might consider:

select count(*) 
  from t 
 where changed_date < ADD_MONTHS( sysdate, -7*12 );


instead. 

Hello TOm,
It's me again. Sorry but I thought I didn't provide much information. I am on 8.1.7 and in my above question

select sysdate from dual;

sysdate
--------
17-JAN-2005


Thanks,
Scott

Hi Reader,

Regarding your question Tom is suggesting this error because of your date format ( not with data ) mask. Ideally you should use "RRRR" format instead of "YY" format. It is better if you put same test case here including table creation script, inserting records sql statement, and your deletion script.

So it will be easy for everybody.


regards & thanks
pjp

Thanks for your help Tom. It was very helpful.

Thanks,
Scott

our reports insert a row into the below table before running and after running.

CREATE TABLE testlog (
REPORTNAME CHAR (50) NOT NULL,
STARTEND CHAR (1) NOT NULL,
PARMSTRING CHAR (255) NOT NULL,
TIMESTAMP DATE NOT NULL,
ID CHAR (50) NOT NULL,
SOURCE CHAR (10)
/

Before the report runs

insert into test log values( 'Repname1', 'S' , 'name=ken, phone=234434', sysdate, user, 'US')

After the report runs

insert into test log values( 'Repname1', 'E' , 'name=ken, phone=234434', sysdate, user, 'US')

We have 100's of reports run by 100's of users.

Now we want to find out those report which are MOST run simultaneously. Our timestamp column

has even time portion inserted into the database.

Now the challenge is , in the existing data, there is no way we can tie a before report run inserted row to a after report inserted row.

A combination of id and timestamp helps in identyfying a before report insert row uniquely, but there is no way we can tie it the after report run row belonging to it.

ID above is not the unique identifier nor primary key but just the user name.

Now what is best possible way to find out those report which are MOST run simultaneously, i.e. those reports which are most often found running simulteneously as multiple users are running it at the same time.

--first the schema

scott@ORA10GR1> drop table child_table;

Table dropped.

scott@ORA10GR1> drop table parent_table;

Table dropped.

scott@ORA10GR1> drop table data_table;

Table dropped.

scott@ORA10GR1> create table parent_table ( parent_name varchar2(10) primary key );

Table created.

scott@ORA10GR1> create table child_table ( parent_name references parent_table,
2 child_name varchar2(10) );

Table created.

scott@ORA10GR1> create table data_table( parent_or_child_name varchar(10),
2 data1 number,
3 data2 number,
4 data3 number
5 );

Table created.

scott@ORA10GR1>
scott@ORA10GR1> insert into parent_table( parent_name) values( 'p1' );

1 row created.

scott@ORA10GR1> insert into parent_table( parent_name) values( 'p2' );

1 row created.

scott@ORA10GR1> insert into child_table( parent_name, child_name ) values( 'p1', 'c11' );

1 row created.

scott@ORA10GR1> insert into child_table( parent_name, child_name ) values( 'p1', 'c12' );

1 row created.

scott@ORA10GR1> insert into child_table( parent_name, child_name ) values( 'p2', 'c21' );

1 row created.

scott@ORA10GR1> insert into child_table( parent_name, child_name ) values( 'p2', 'c22' );

1 row created.

scott@ORA10GR1>
scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'p1', 1, 2, 3);

1 row created.

scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'c11', 1, 2, 3);

1 row created.

scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'c12', 1, 4, 3);

1 row created.

scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'p2', 1, 2, 3);

1 row created.

scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'c21', 1, 2, 3);

1 row created.

scott@ORA10GR1> insert into data_table( parent_or_child_name, data1, data2, data3 )
2 values( 'c22', 2, 2, 3);

1 row created.

scott@ORA10GR1> commit;

Commit complete.

scott@ORA10GR1> select * from parent_table;

PARENT_NAM
----------
p1
p2

scott@ORA10GR1> select * from child_table;

PARENT_NAM CHILD_NAME
---------- ----------
p1 c11
p1 c12
p2 c21
p2 c22

scott@ORA10GR1> select * from data_table;

PARENT_OR_ DATA1 DATA2 DATA3
---------- ---------- ---------- ----------
p1 1 2 3
c11 1 2 3
c12 1 4 3
p2 1 2 3
c21 1 2 3
c22 2 2 3

6 rows selected.

parent_table contain parent records.
child_table contain child records.
data_table contains data values for either parent or
children. Each row either belongs to the parent or
to one of the child...

The data should be such that for each parent and
child, all data columns (data1, data2 etc.) should
contain the same value. The requirement is to create a report that violates this - e.e. in the above case the
report looks like:

Following data columns don't match for parent and child
values:

parent name: p1, child name: c12 mismatch columns: data2
child name: c21, child name: c22 mismatch columns: data1

Formatting the report as above is not important as
long as we get the answer...

I wrote the following to find out those
records in which parents dont match the child
data value...Before realizing each child
also has to be matched with other child records
for the same parent also...

scott@ORA10GR1> select parent_name, child_name,
2 max( decode( parent_data1, child_data1, null, 'data1') ) data1_compare,
3 max( decode( parent_data2, child_data2, null, 'data2')) data2_compare,
4 max( decode( parent_data3, child_data3, null, 'data3')) data3_compare
5 from
6 (
7 select p.parent_name, p.parent_data1, p.parent_data2, p.parent_data3,
8 c.child_name, c.child_data1, c.child_data2, c.child_data3
9 from
10 (
11 select d.parent_or_child_name parent_name,
12 d.data1 parent_data1,
13 d.data2 parent_data2,
14 d.data3 parent_data3
15 from data_table d, parent_table p
16 where d.parent_or_child_name = p.parent_name
17 ) p,
18 (
19 select d.parent_or_child_name child_name,
20 c.parent_name,
21 d.data1 child_data1,
22 d.data2 child_data2,
23 d.data3 child_data3
24 from data_table d, child_table c
25 where d.parent_or_child_name = c.child_name
26 ) c
27 where c.parent_name(+)= p.parent_name
28 )
29 group by parent_name, child_name;

PARENT_NAM CHILD_NAME DATA1 DATA2 DATA3
---------- ---------- ----- ----- -----
p1 c11
p1 c12 data2
p2 c21
p2 c22 data1


Procedural solution should be simple... Is there
a SQL solution you can think of?

Thanx.

thought the original thread was about a query...

Used lag() to solve it.
Posting it just in case someone else is interested...

Added parent_name column to the data_table above..
So the three tables now have following data
---
scott@ORA10GR1.US.ORACLE.COM> select * from parent_table;

PARENT_NAM
----------
p1
p2

scott@ORA10GR1.US.ORACLE.COM> select * from child_table;

PARENT_NAM CHILD_NAME
---------- ----------
p1 c11
p1 c12
p2 c21
p2 c22

scott@ORA10GR1.US.ORACLE.COM> select * from data_table;

PARENT_OR_ PARENT_NAM DATA1 DATA2 DATA3
---------- ---------- ---------- ---------- ----------
p1 p1 1 3 3
c11 p1 1 2 3
c12 p1 1 4 3
p2 p2 1 2 3
c21 p2 1 2 3
c22 p2 2 2 3

6 rows selected.

----

Note the new column parent_name in the data_table.

And the select that seems to work is:
scott@ORA10GR1.US.ORACLE.COM> select d.parent_name, d.data1, d.data2, d.data3,
2 case
3 when parent_name = lag_parent_name then
4 decode( data1, lag_data1, null, 'data1' )
5 end data1_diff,
6 case
7 when parent_name = lag_parent_name then
8 decode( data2, lag_data2, null, 'data2' )
9 end data2_diff,
10 case
11 when parent_name = lag_parent_name then
12 decode( data3, lag_data3, null, 'data3' )
13 end data3_diff
14 from
15 (
16 select d.*,
17 lag( parent_name ) over( order by parent_name) lag_parent_name,
18 lag( data1 ) over( order by parent_name) lag_data1,
19 lag( data2 ) over( order by parent_name) lag_data2,
20 lag( data3 ) over( order by parent_name) lag_data3
21 from
22 (
23 select *
24 from data_table
25 ) d
26 ) d;

PARENT_NAM DATA1 DATA2 DATA3 DATA1_DIFF DATA2_DIFF DATA3_DIFF
---------- ---------- ---------- ---------- ---------- ---------- ----------
p1 1 3 3
p1 1 2 3 data2
p1 1 4 3 data2
p2 1 2 3
p2 1 2 3
p2 2 2 3 data1

----------

Once again - sorry to have posted it on a wrong
thread earlier. Sometimes it can get confusing (e.g. many
of the follow ups in the above thread did not
*seem* related to the original thread when I glanced
through them earlier.)
Of course, in many cases you answer stuff just
because it is easy to do that instead of saying
anything else:) And of course I appreciate
your site regardless anyways...

Hi

I have a data like this

C1 D1
---- ----
1 1
1 23
2 1
3 23

I need to write a query which returns C1 with values D1, 1 and 23, D1 can only return 23 if it also have value 1, so the query run agaisnt above data should return

C1 D1
---- ----
1 1
1 23
3 23

It´s like the opposite of

select * from X b
where D1 = 1
and not exists (select null from X a where a.c1 = b.c1 and a.d1 = 23)

How can you write that in single query?

Thank you

Hi I got it

select * from X b
where D1 in (1, 23)
and exists (select null from X a where a.c1 = b.c1 and a.d1 = 23)

cheers

Tom,

Trying to get to a query for the following using inline views. Shall appreciate your help.

drop table test_a;
drop table test_b;
drop table test_c;
create table test_a (snum number, status varchar2(20));
create table test_b(snum number, value number,details varchar2(20));
create table test_c( snum number);
insert into test_b values(100,1000,'testing100');
insert into test_b values(200,2000,'testing200');
insert into test_b values(300,3000,'testing300');
insert into test_b values(400,4000,'testing400');
insert into test_b values(500,5000,'testing500');

insert into test_c values(100);
insert into test_c values(200);
insert into test_c values(300);
insert into test_a values(100,'err100');
insert into test_a values(999,'err999');


select * from test_a;

SNUM STATUS
---------- --------------------
100 err100
999 err999


select * from test_b;

SNUM VALUE DETAILS
---------- ---------- --------------------
100 1000 testing100
200 2000 testing200
300 3000 testing300
400 4000 testing400
500 5000 testing500

select * from test_c;

SNUM
----------
100
200
300

how to write a query for the following result required with
conditon: snum column equates in all the three tables.
for the above data:

cnt(from test_b) cnt(from test_a) sumtest_b.value)
3 1 6000

Here is what is required:
drop table test_a;
drop table test_b;
drop table test_c;
create table test_a (id number,snum number, status varchar2(20));
create table test_b(id number,snum number, value number,details varchar2(20));
create table test_c( id number,snum number);
insert into test_b values(1,100,1000,'testing100');
insert into test_b values(1,200,2000,'testing200');
insert into test_b values(2,300,3000,'testing300');
insert into test_b values(2,400,4000,'testing400');
insert into test_b values(3,500,5000,'testing500');

insert into test_c values(1,100);
insert into test_c values(2,200);
insert into test_c values(2,300);
insert into test_a values(1,100,'err100');
insert into test_a values(4,999,'err999');

SQL> select * from test_a;

        ID       SNUM STATUS
---------- ---------- --------------------
         1        100 err100
         4        999 err999

SQL> select * from test_b;

        ID       SNUM      VALUE DETAILS
---------- ---------- ---------- --------------------
         1        100       1000 testing100
         1        200       2000 testing200
         2        300       3000 testing300
         2        400       4000 testing400
         3        500       5000 testing500

SQL> select * from test_c;

        ID       SNUM
---------- ----------
         1        100
         2        200
         2        300

how to write a query for the following result required with
conditon: snum and id column equates in all the three tables.
for the above data ( and id and snum found in test_c)

id cnt(from test_b)  cnt(from test_a)      sumtest_b.value) 
 1     1                          1                  1000
 2     2                          0                  7000 

Hi Tom,
Any direct SQL Statement exists to retrieve data from
a table with sys.anydata type??
Also provide useful inputs for using sys.anydataset

SQL> create table t(x sys.anydata)
  2  /

Table created.

SQL> insert into t values(sys.anydata.convertNumber(100))
  2  /

1 row created.

SQL> insert into t values(sys.anydata.convertVarchar2('Hello World'))
  2  /

1 row created.

SQL> insert into t values(sys.anydata.ConvertDate(sysdate))
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t
  2  /

X()                                                                             
--------------------------------------------------------------------------------
ANYDATA()                                                                       
ANYDATA()                                                                       
ANYDATA()                                                                       


 

Database version: 9.2

CREATE TABLE t_history(seq_no number, item_code varchar2(10), doc_date date, doc_no number, qty_bal number, rate number);

INSERT INTO t_history values(101, 'A', to_date('24-DEC-04'), 90, 5, 10.25);
INSERT INTO t_history values(201, 'A', to_date('04-JAN-05'), 101, 10, 10.25);
INSERT INTO t_history values(202, 'A', to_date('14-JAN-05'), 102, 12, 11.5);
INSERT INTO t_history values(203, 'A', to_date('24-JAN-05'), 103, 13, 11.25);
INSERT INTO t_history values(204, 'A', to_date('24-JAN-05'), 104, 11, 11.25);

INSERT INTO t_history values(111, 'B', to_date('30-DEC-04'), 97, 12, 1.75);

INSERT INTO t_history values(221, 'C', to_date('25-JAN-05'), 111, 1, 127);
INSERT INTO t_history values(222, 'C', to_date('27-JAN-05'), 112, 2, 130);
INSERT INTO t_history values(223, 'C', to_date('28-JAN-05'), 113, 1, 130);


DESIRED OUTPUT (start date = 01-Nov-04, end date = current date)
========================================================================
ITEM DATE QTY RATE
-------------------------------------
A 24-DEC-04 5 10.25
A 25-DEC-04 5 10.25
.....................................
.....................................
A 04-JAN-05 10 10.25
A 05-JAN-05 10 10.25
.....................................
.....................................
A 24-JAN-05 11 11.25
.....................................
.....................................
A 29-JAN-05 11 11.25

B 30-DEC-04 12 1.75
B 31-DEC-04 12 1.75
.....................................
.....................................
B 29-JAN-05 12 1.75

C 25-JAN-05 1 127
.....................................
.....................................
C 29-JAN-05 1 130


Thanks for your time.

You need a result set with all of the dates...

You need a result set with all of the item_codes....

We need to cartesian product them together.... (get all dt/item codes)

then we can outer join with that to pick up the observed qty/rates...


then we can carry down.  In 9iR2 this would look like this:


ops$tkyte@ORA9IR2> with dates
  2  as
  3  (
  4  select to_date('01-nov-2004','dd-mon-yyyy')+rownum-1 dt
  5    from all_objects
  6   where rownum <= (sysdate-to_date('01-nov-2004','dd-mon-yyyy'))+1
  7  ),
  8  items
  9  as
 10  (
 11  select distinct item_code
 12    from t_history
 13  ),
 14  items_dates
 15  as
 16  (select * from dates, items )
 17  select *
 18    from (
 19  select dt, item_code,
 20         to_number( substr( max( rn || qty_bal ) over 
               (partition by item_code order by dt),11)) qty_bal,
 21         to_number( substr( max( rn || rate    ) 
               over (partition by item_code order by dt),11)) rate
 22    from (
 23  select a.*, b.qty_bal, b.rate,
 24         case when b.qty_bal is not null
 25                  then to_char(row_number() 
                  over (partition by a.item_code order by a.dt),'fm0000000009')
 26                  end rn
 27    from items_dates a left join t_history b 
                 on (a.dt = b.doc_date and a.item_code = b.item_code)
 28         )
 29         )
 30   where qty_bal is not null or rate is not null
 31   order by item_code, dt
 32  /
 
DT        ITEM_CODE     QTY_BAL       RATE
--------- ---------- ---------- ----------
24-DEC-04 A                   5      10.25
25-DEC-04 A                   5      10.25
26-DEC-04 A                   5      10.25
27-DEC-04 A                   5      10.25
28-DEC-04 A                   5      10.25
29-DEC-04 A                   5      10.25
30-DEC-04 A                   5      10.25
31-DEC-04 A                   5      10.25
01-JAN-05 A                   5      10.25
.......


In 10g, this would be simplified to:

ops$tkyte@ORA10G> with dates
  2  as
  3  (
  4  select to_date('01-nov-2004','dd-mon-yyyy')+rownum-1 dt
  5    from all_objects
  6   where rownum <= (sysdate-to_date('01-nov-2004','dd-mon-yyyy'))+1
  7  )
  8  select *
  9    from (
 10  select b.dt, a.item_code,
 11         last_value( a.qty_bal ignore nulls) over (partition by item_code order by dt) qty,
 12         last_value( a.rate    ignore nulls) over (partition by item_code order by dt) rate
 13    from t_history a partition by (item_code) right join dates b on (b.dt = a.doc_date)
 14         )
 15   where qty is not null or rate is not null
 16   order by item_code, dt
 17  /
 
DT        ITEM_CODE         QTY       RATE
--------- ---------- ---------- ----------
24-DEC-04 A                   5      10.25
25-DEC-04 A                   5      10.25
26-DEC-04 A                   5      10.25
27-DEC-04 A                   5      10.25
28-DEC-04 A                   5      10.25
29-DEC-04 A                   5      10.25
30-DEC-04 A                   5      10.25
31-DEC-04 A                   5      10.25
01-JAN-05 A                   5      10.25
02-JAN-05 A                   5      10.25
03-JAN-05 A                   5      10.25
04-JAN-05 A                  10      10.25
05-JAN-05 A                  10      10.25
06-JAN-05 A                  10      10.25
07-JAN-05 A                  10      10.25
08-JAN-05 A                  10      10.25
....

due to the addition of

a) partitioned outer joins
b) ignore nulls in analytics.

 

If more than 1 record exists for a day then instead of using:
....
from items_dates a left join t_history b
on (a.dt = b.doc_date and a.item_code = b.item_code)
....

I have to use:
......
from items_dates a left join (
SELECT * FROM (
SELECT seq_no, item_code, doc_date, qty_bal, rate,
row_number() over (partition by item_code, doc_date order by seq_no desc) rr
FROM t_history
) WHERE rr = 1
)
b
on (a.dt = b.doc_date and a.item_code = b.item_code)
......

right? Or is there a better way?

Ali

Tom,

With the help of the inline view example, I wrote this query.
select b.id,b.cnt,b.sum,a.cnt from
(select id,count(*) cnt, sum(value) sum
from test_b where id||snum in (select id||snum from test_c) group by id)b
left outer join
(select id,count(*) cnt from test_a where id||snum in (select id||snum from test_c) group by id)a on( a.id=b.id );

ID CNT SUM CNT
---------- ---------- ---------- ----------
1 1 1000 1
2 2 7000

The result looks good. Will the id||snum an appropriate way of using in the where clause or is it good to join the two columns separately? Is there any other way of writing this query? Thanks for the time.

Tom,

The undernoted query has a defect ( actually related to the queries I thought had solved the issue). The second inline view actually returns only count of 1 for the condition. But in the cartesian product, it shows up in all the rows. How to avoid this?

select a.id,a.cnt,a.sum,b.cnt from
(select id, count(*) cnt ,sum(value) sum from test_b where(id,snum) in
(select id,snum from test_c) group by id) a,
(select count(*) cnt from test_a where(id,snum) in
(select id,snum from test_c)) b;

ID CNT SUM CNT
---------- ---------- ---------- ----------
1 1 1000 1
2 2 7000 1

Thanks

So with the given conditions, the following query was written to get the result. Will this turn out very complicated (provided it is the only way to get the result) if the number of rows in test_c are hugh ( in millions) ?


select c.id,sum(c.cnt1),sum(c.sum),sum(c.cnt) from
(
select a.id id,a.cnt cnt1 ,a.sum sum ,b.cnt cnt from
(select id, count(*) cnt ,sum(value) sum from test_b where(id,snum) in
(select id,snum from test_c) group by id) a
left outer join
(select id,count(*) cnt from test_a where(id,snum) in
(select id,snum from test_c) group by id) b
on b.id=a.id) c
group by c.id ;

ID SUM(C.CNT1) SUM(C.SUM) SUM(C.CNT)
--------- ----------- ---------- ----------
1 1 1000 1
2 2 7000

Hi Tom

Regarding the cursor query you mentioned in the thread  

 select mgr, cursor( select ename from emp emp2 where emp2.mgr = emp.mgr )
  2* from ( select distinct mgr from emp ) emp


I thouhgt this query would be good to fetch records from a master and its child records when execute from a middle tier beacuse in terms of Total number of bytes tranasfered and number of round trips when compared to a normal join 


But the result was exactly opposit.


@NGDEV1-SQL> select a.atp_id,AC_TYP_INHOUSE,AC_TYP_DESC,AC_TYP,
  2  cursor(select deck,deck_desc from MST_AC_TYP_DECK b where b.atp_id=a.atp_id) Deck_Info,
  3  cursor(select period_frm,period_to,brd_pnt,off_pnt from MST_AC_TYP_EXCP c where c.atp_id=a.atp_id) Exception
  4  from mst_ac_typ a  where a.atp_id=3
  5  /

    ATP_ID AC_TY AC_TYP_DESC                                        AC_TYP     DECK_INFO            EXCEPTION
---------- ----- -------------------------------------------------- ---------- -------------------- --------------------
         3 33H   AIRBUS                                             A330-300   CURSOR STATEMENT : 5 CURSOR STATEMENT : 6

CURSOR STATEMENT : 5

DECK       DECK_DESC
---------- --------------------------------------------------
LOWERDECK
LD         TEST
MD         MIDDLE DECK


CURSOR STATEMENT : 6

PERIOD_FR PERIOD_TO BRD_P OFF_P
--------- --------- ----- -----
26-JAN-05 27-JAN-05



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=17)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP_DECK' (TABLE) (Cost=1 Card=2 Bytes=36)
   2    1     INDEX (RANGE SCAN) OF 'ATD_ATP_FK_I' (INDEX) (Cost=1 Card=2)
   3    0   TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP_EXCP' (TABLE) (Cost=1 Card=1 Bytes=26)
   4    3     INDEX (RANGE SCAN) OF 'ATE_UK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   5    0   TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP' (TABLE) (Cost=1 Card=1 Bytes=17)
   6    5     INDEX (UNIQUE SCAN) OF 'ATP_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)




Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1826  bytes sent via SQL*Net to client
       1211  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

NGCS_DEV@NGDEV1-SQL> select a.atp_id,AC_TYP_INHOUSE,AC_TYP_DESC,AC_TYP,
 deck,deck_desc,period_frm,period_to,brd_pnt,off_pnt from MST_AC_TYP_DECK b, MST_AC_TYP_EXCP c,mst_ac_typ a
where b.atp_id=a.atp_id
and  c.atp_id=a.atp_id
and  a.atp_id=3;  2    3    4    5

    ATP_ID AC_TY AC_TYP_DESC                                        AC_TYP     DECK
---------- ----- -------------------------------------------------- ---------- ----------
DECK_DESC                                          PERIOD_FR PERIOD_TO BRD_P OFF_P
-------------------------------------------------- --------- --------- ----- -----
         3 33H   AIRBUS                                             A330-300   LOWERDECK
                                                   26-JAN-05 27-JAN-05

         3 33H   AIRBUS                                             A330-300   LD
TEST                                               26-JAN-05 27-JAN-05

         3 33H   AIRBUS                                             A330-300   MD
MIDDLE DECK                                        26-JAN-05 27-JAN-05



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=122)
   1    0   MERGE JOIN (CARTESIAN) (Cost=3 Card=2 Bytes=122)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=43)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP' (TABLE) (Cost=1 Card=1 Bytes=17)
   4    3         INDEX (UNIQUE SCAN) OF 'ATP_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP_EXCP' (TABLE) (Cost=1 Card=1 Bytes=26)
   6    5         INDEX (RANGE SCAN) OF 'ATE_UK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   7    1     BUFFER (SORT) (Cost=2 Card=2 Bytes=36)
   8    7       TABLE ACCESS (BY INDEX ROWID) OF 'MST_AC_TYP_DECK' (TABLE) (Cost=1 Card=2 Bytes=36)
   9    8         INDEX (RANGE SCAN) OF 'ATD_ATP_FK_I' (INDEX) (Cost=0 Card=2)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1093  bytes sent via SQL*Net to client
        511  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

NGCS_DEV@NGDEV1-SQL> /


In the above example my expectation was less byes transfered since master data is not repeating. Additional recursive call is understandable since Oracle has to do something to make the out put in object form. 

Is this not a good way to execute queries..

Would you please explain

Rgds
Anil  
 

Hi Tom,

I had submitted this earlier, but looks like it did not go through ( some prob with my browser)..So again submitting.

What is an efficient way to get the counts from a table (around 10 million rows) based on varied conditions?

The actual table has around 50 columns.
drop table temp_del;
create table temp_del (c1 varchar2(3),c2 varchar2(3),c3 varchar2(3),c4 varchar2(3),flag number);
insert into temp_del values('abc','bcd','cde','def',0);
insert into temp_del values('abc','bcd','cde','def',1);
insert into temp_del values('abc','bcd','cde','def',2);

insert into temp_del values('bcd','cde','def','efg',0);
insert into temp_del values('bcd','cde','def','efg',1);
insert into temp_del values('bcd','cde','def','efg',2);

insert into temp_del values('cde','def','efg','fgh',0);
insert into temp_del values('cde','def','efg','fgh',1);
insert into temp_del values('cde','def','efg','fgh',2);
commit;

select count(*) from temp_del where c1='abc' and c2='bcd' and flag=0;
select count(*) from temp_del where c1='abc' and c2='bcd' and flag=1;
select count(*) from temp_del where c1='abc' and c2='bcd' and c3='efg' and flag=0;
select count(*) from temp_del where c1='abc' and c2='bcd' and c3='efg' and flag=1;
select count(*) from temp_del where c1='bcd' and c2='cde' and c3='def' and flag=2;
and so many other combinations similar to this..

Is there a way the table can be accessed once and get the varied counts like above?

Hi

Will this query do that job,



select sum(case when carr_code= 'PP' and vol_unit = 'LTRE' then 1 else 0 end) LTRCOUNT,
sum(case when carr_code= 'PP' and vol_unit is null then 1 else 0 end) NONLTRCOUNT,
sum(case vol_unit when 'LTRE' then 1 else 0 end) C2,
count(*) TOt from TEST_TABLE

LTRCOUNT NONLTRCOUNT C2 TOT
---------- ---------- ---------- ----------
3 4 3 7


Rgds
Anil

I have table x and y.
Rows in x:
x y
----------- -----------
8 3
45 2
10 3
43 1

Row in y:
x y
----------- -----------
8 3
10 2
43 3


I want query return records from x table where each table's x column values are different but y column values are same:

select x.* from x, y
where x.x <> y.x and x.y = y.y

It returns:
X Y
---------- ----------
45 2
10 3
8 3
10 3

I expected query return thesecond row from x table.
Thanks in advance.

Hi Tom,

Is there any difference between two statements:

Where A is NOT NULL Vs NOT A is NULL, from performance point of view.

Regards,

easy enough to test (so i wonder why after seeing lots of tests here, people don't set them up?)

short answer, no.  

test below....

ops$tkyte@ORA9IR2> create table t as select decode(rownum,0,0,null) x, rownum y, decode(mod(rownum,2), 1, rownum,  null ) z from big_table.big_table;
 
Table created.
 
ops$tkyte@ORA9IR2> @trace
 
Session altered.
 
ops$tkyte@ORA9IR2> select count(*) from t where x is not null;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA9IR2> select count(*) from t where NOT x is null;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA9IR2> select count(*) from t where y is not null;
 
  COUNT(*)
----------
   9000000
 
ops$tkyte@ORA9IR2> select count(*) from t where NOT y is null;
 
  COUNT(*)
----------
   9000000
 
ops$tkyte@ORA9IR2> select count(*) from t where z is not null;
 
  COUNT(*)
----------
   4500000
 
ops$tkyte@ORA9IR2> select count(*) from t where NOT z is null;
 
  COUNT(*)
----------
   4500000
 
ops$tkyte@ORA9IR2>


select count(*) from t where x is not null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.14       1.14      18466      18477          0           1
                                                                                                                                                                            
select count(*) from t where NOT x is null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.13       1.16      18466      18477          0           1
                                                                                                                                                                            
select count(*) from t where y is not null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     13.99      13.69      18466      18477          0           1
                                                                                                                                                                            
select count(*) from t where NOT y is null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     13.89      13.60      18466      18477          0           1
                                                                                                                                                                            
select count(*) from t where z is not null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.65       7.51      18466      18477          0           1
                                                                                                                                                                            
select count(*) from t where NOT z is null
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.63       7.68      18466      18477          0           1
 

Those results are what I would expect.

Change your query to:

select x.*,y.*
from x, y
where x.y = y.y
order by 1,3

and you will see how the joins are being done with x.y = y.y, then it will be quite obvious with the added condition of x.x <> y.x that you get the results you did.

I think you are overlooking the fact that you are joining multiple rows in X to multiple rows in Y.

Hi Tom


<<first JOINS ARE NOT EVIL, in a rdbms in
fact, JOINS ARE WHAT we do!
>>

I am always convinced that Joints are not evil . Here I was thinking of the amount of data tranasfer to the java client. I thought this will reduce since master table data is not repeating. Also it reduces the coding at java since they get it as objects. But now I am convinced that they can do exta coding to get the performance. So we are going for a plain join.

Rgds
Anil

Hi Tom,

In my earlier issue with the same Title, I had given a case for varied count conditons from a single table and a response was given by Anil. My question is:
a) Is each row read once for each case statement or will all case statements be read for each row and grab the count? So if there are say 10 case statements, then hopefully oracle goes through all these cases for every row of the table for a FTS (or according to the conditions). Thanks for clarifying.

b)Is the query the only way to write with one single access to the table data?

Thanks again.

I used the package and got the output i wanted, thanks for that., I tried to alter the column heading to just Dd-Mon but my version of the altered pkg does compile with errors, also can i extend the where clause to filter come data here.

15-JUL 16-JUL 17-JUL 18-JUL
Ajay 500 0 100 0
Bhavani 700 0 0 0
Chakri 0 0 200 0

Also can we get data like for example ( all tuesdays of the two months)
thanks

u r rt tom,
i should have give the code for you to see the full picture
the output i expected is something like

nameid cfg cabtype 01-Jan 02-Jan 03-Jan
---------------------------------------
abc 2/2 h1 20 19.56 22
xbc 1/2 h2 21 17.46 12
ybc 4/2 h1 22 14.66 22
zbc 2/1 h1 16 17.76 23

1 create or replace package crstb_tch
2 as
3 type rc is ref cursor;
4 procedure get_query( p_cursor in out rc, p_start date, p_end date );
5 end;
6 /
7 create or replace package body crstb_tch
8 as
9 procedure get_query( p_cursor in out rc, p_start date, p_end date )
10 is
11 l_query long := 'select Nameid, cfg, cabtype ';
12 begin
13 for i in 1 .. trunc(p_end)-trunc(p_start)+1
14 loop
15 l_query := l_query || ', min( decode( trunc(rd_dt), ' ||
16 'to_date( ''' || to_char(p_start+i-1,'dd-mon') ||
17 ''', ''dd-mon'' ), tch, 0 )) "' || to_char(p_start+i-1,'dd-mon') || '"';
18 end loop;
19 l_query := l_query || ' from jttbl ' ||
20 'where tch > 10 and bsc like '%Bangalore%' ||
21 'group by nameid, cfg, cabtype ' ||
22 'order by nameid' ;
23 open p_cursor for l_query;
24 end;
25* end;
26 /

Warning: Package created with compilation errors.

Also as a further extension how to modify the code to as for only tuesdays (or wednesdays) of a month (or two months.)?

thanks in advance

Hi TOM,

what the heck is wrong with this

"u r rt tom,"

you are having a complex. period so you cannot read it.,

if some thing was a red flag or green or whatever color i would not be visiting this page.

your answer did not leave me any wiser in oracle or in english

I won't be technically what I am without this site. And I won't mind paying even if Tom were to charge me by page. It is that valuable. But it is all free.

Tom spends most of his week-ends answering our questions. Let's try to be decent and polite even if not thankful.

Hi.. Tom
I am a big fan of you :)

I was reading the followup of one of question related to get the maximum salary in each department.

I can see that every query you suggested to get this includes partition by clause, What about this one..

select ename,deptno,sal
from emp where (deptno,sal) in(select deptno,max(sal)
from emp
group by deptno)
and will it be slower than using partition by.

Thanks & Regards
Ravi

I learnt a lot from this site.This is by far the best Oracle Forum I have ever been to.I learn something new everyday.We should really appreciate the fact that Tom is taking time to answer so many questions ( For godsake he is a Vice President).I hope you continue your service to the Oracle World
Tom..You Rock

Hi Tom,

I read the follow up and got very disappointed from some people. But please just ignore them and continue helping Oracle World. I learned a lot from your great site.

I am using one of your magic SQL for parsing one column and split it to different parts. The query works fine. The problem that I have is that, I have to do it 1000 records at each time. It means if the query selects more than that e.g 10,000 records to process, the session will stay active forever and I have to kill the session and start it again with the limit of 1000. Could you please tell me what can be wrong?

SQL> desc nitstoryn
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 SRL                                       NOT NULL NUMBER(12)
 STORYFILE_NME                             NOT NULL VARCHAR2(1000)
 AUTHOR                                             VARCHAR2(42)
 STORY_ORIGIN                                       VARCHAR2(10)
 CATEGORY                                           VARCHAR2(30)
 HEADLINE                                           VARCHAR2(200)
 SUBHEADLINE                                        VARCHAR2(200)
 KEYWORDS                                           VARCHAR2(1000)
 HISTORY                                            VARCHAR2(200)
 DATELINE                                           VARCHAR2(1000)
 HIGHLIGHT                                          VARCHAR2(4000)
 CREATION_TIME                                      VARCHAR2(100)
 LAST_MODIFIED_TIME                                 VARCHAR2(100)
 TIMEZONE                                           VARCHAR2(25)
 FOOTNOTES                                          CLOB
 BODY                                               CLOB
 CREATION_TIME_DATE                                 DATE

SQL> desc nitielinkn
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 SRL                                       NOT NULL NUMBER(12)
 NITSTORY_SRL                                       NUMBER(12)
 LINK_TYP                                           VARCHAR2(1)
 LINK                                               VARCHAR2(4000)
 VALID_FLG                                          VARCHAR2(1)
 LAST_VALIDATED_DATE                                DATE


INSERT INTO nitielinkn(srl,nitstory_srl,link_typ,link)
SELECT nitsielink.nextval, srl story_srl, 'E',
       SUBSTR(to_char(body),INSTR(lower(to_char(body)),'href',1,rr.r),
       INSTR(body,'"',INSTR(body,'href',1,rr.r),2)-INSTR(body,'href',1,rr.r)+1) link
FROM
 (SELECT srl, ','||body||',' body, (LENGTH(body)-LENGTH(replace(body,'href','')))/4 cnt
  FROM nitstoryn WHERE INSTR(body,'href',1,1) > 0 ) nitstory,
 (SELECT rownum r
  FROM all_objects
  WHERE rownum <= 100) rr
WHERE rr.r <= nitstory.cnt
and srl >  11000
and srl <= 12000 "This is the part that I have to use to limit it to 1000"
and INSTR(lower(to_char(body)),'href',1,1) > 0
/

Thanks,
- Arash
 

Hi Tom,
Any other better way to put this query?

SQL> select deptno,sal,count(*) from emp
  2  group by deptno,sal
  3  having sal = any(select max(sal) from emp group by deptno)
  4  /

    DEPTNO        SAL   COUNT(*)
---------- ---------- ----------
        10       5000          1
        20       3000          2
        30       2850          1

Please do reply.
Bye!
 

Hi Tom,
Please help to write SQL query to list all records that was inserted between today 8am and previous day 8am.
example
Between 8am 2-Mar-2005 and 8am 1-Mar-2005.

Thanks a lot in advance.

-Ratnam

Hi tom,

While replying to one of the questions you have used
ops$tkyte@ORA9IR2> @trace ( trace.sql ), Can you tell us what is the content of trace.sql ?

regards & thanks
pjp

Hello Tom,
I use Oracle 9i Release 2.I would like to insert a single space 

between each character of the string "Hello World"

SQL> select 'Hello World' from dual
  2  /

'HELLOWORLD
-----------
Hello World

I would like to get the output as
 
  H e l l o W o r l d

Is it possible in Oracle 8i or 9i?


   

it would be hard using the builtins.

in 10g, easy


  1* select regexp_replace( 'hello world', '(.)', '\1 ' ) from dual
ops$tkyte@ORA10G> /
 
REGEXP_REPLACE('HELLOW
----------------------
h e l l o   w o r l d


in 8i/9i, either have the client do it or write a small plsql function to do it. 

Tom,

I need to write a query in the following form:
select * from table where table.field IN f(:bind-variable)
or perhaps
select * from table where table.field IN (select field from f(:bind-variable))
where f(:bind-variable) is a function or stored procedure that returns a rowset, ref cursor, table or whatever.

Regards,
Dawar

if you're on 9i, pull the string apart then use sys_connect_by_path to put it together again:

SQL> select trim(replace(sys_connect_by_path(c,'_ '),'_')) msg
  2    from (
  3  select iter.pos, msg, substr(d.msg,iter.pos,1) as c
  4    from (select 'hello world' as msg from dual) d,
  5         (select rownum pos from emp) iter
  6   where iter.pos <= length(d.msg)
  7         )
  8   where level = length(msg)
  9   start with pos=1
 10   connect by prior pos=pos-1;
 
MSG
----------------------
h e l l o   w o r l d

still might be better of writing some plsql, but this works in a pinch.  amazing how much nicer regex is for doing things like this - 10 lines of sql code compared to 1 using regex. 

Hi Tom,

I have a table like this :
s_name varchar2,
s_type varchar2,
s_num1 number,
s_num2 number

Values :
'A','X',100,200
'A','Y',,100
'B','X',50,
'B','Z',,75

Output i need:
X1 X2 Y1 Y2 Z1 Z2
A 100 200 0 100 0 0
B 50 0 0 0 0 75

Can you please help by suggesting a simpler way
to achieve this?

Thanks and regards,
RS

Tom,

I know we can do this: COLUMN column_name FORMAT model
Is there a way to do that for all columns in the select statement?

Thanks.

Thanks. I was hoping that you had some tricks.

Hi Tom,

I have table like

create table t ( id number,
col1 varchar2(1),
col2 varchar2(1),
col3 varchar2(1)
);

insert into t values(1, 'a', 'b', null);
insert into t values(2, null, 'b', 'c');
insert into t values(3, 'a', null, 'c');
insert into t values(4, 'd', 'e', null);
insert into t values(5, 'd', 'f', null);

What I want to do is if any columns ( col1, 2, 3 ) have same value, then the records will be in one group, so records 1,2,3 will be in one group, records 4, 5 will be in other group.

How to do this with sql?

Thanks

Hi Tom,

Maybe I didn't describe it clearly, actually, I want the result looks like

ID C C C grp
---------- - - -
1 a b 1
2 b c 1
3 a c 1
4 d e 2
5 d f 2

Because record 1 and 3 have same value of col1('a'), so 1 and 3 are in same group, record 1 and 2 have same value of col2('b'), so 1 and 2 are in same group, record 2 and 3 have same value of col3('c'), so 2 and 3 are in same group, so 1,2,3 should be in same group. same thing to record 4 and 5.

grp number doesn't need to be continuous, gap between grp number is OK.

How can I do this?

Thanks

Hi Tom,

I was pretty impressed the way you answer sql queries. I would appreciate, if you can also look into this.

I have to write a sql query where it has to retrieve all the columns of the table TEST1 with the given sorting order.

TABLE : TEST1

The raw data is as follows

SQL> Select dispnum, airline, org, des, mtype from TEST1;

DISPNUM        AIRLINE     ORG    DES    MTYPE
-------        -------     ---    ---    -----
    113          CX        HKG    DXB    CP
     13          AH        ALG    JED    EMS
   4146          CX        CAN    DXB    EMS
     50          BI        BWN    DXB    LC
      8          AZ        MXP    AKL    CP
     74          BG        DAC    DXB    LC
      1          LH        FRA    DXB    LC
    BAH          EK        BAH    KWI    CP
     SN                    DXB    LON    CP
     SN          SV        RUH    EWR    CP
   7193                    DXB    CMN    EMS

Sorting to be done on the columns (airline, dispnum, org, des, mtype) in ascending order. Ensure that (dispnum) starting with a character should come first in the list.

Your assistance will be highly appreciated.

Rgrds 

If record 1 was

a b f

it should belong to group 1, the grouping criteria is if any column ( col1, 2, 3 ) has same value, then the records should belong to same group.

F doesn't make the first record to group 2, because col3 in group 2 is null.

I have a table called a which has field like (rid,bname,city,rdate,share)
I have another table called b which has (bname,city,rdate,amount)

I want to find out average share(of the robbers participating in robberies) during the most recent year with any robberies and the year in which the total amount robbed was the highest.

i have installed oracle database sercer 9i. but when i had opend sql it require user name and password.in the user name i wrote SYSTEM/SCOTT and in the password field i wrote MANAGER/TIGER. none of them functioning and producing a error message tns protocol error.
how can i enter into sql.pls send user name and password into my email.
with thanks
md.oli ullah.

Here you go
create table robbery(bankname varchar(100) not null,city varchar(50) not null,rdate date not null,amount integer not null,primary key (bankname,city,rdate));

This table stores information about robberies of banks that the gang has already performed,including how much was stolen.

create table accomplices(robberid integer ,bankname varchar(100) not null,city varchar(50) not null,rdate date not null,share integer not null,foreign key(bankname,city,rdate) references robbery(bankname,city,rdate) on update cascade,primary key(robberid,bankname,city,rdate));

This table stores information about which gang members participated in each bank robbery and what share of the money they got.

select * from robbery;

bankname city rdate amount
N Bank | xxx | 1999-01-08 | 32
Lk Bank | yyy | 1995-02-28 | 19
Lk Bank | xxx | 1995-03-30 | 21


select * from accomplices;
robberid bankname city rdate share

1 | LK Bank | xxx | 1995-03-30 | 42
1 | LK Bank | yyy | 1995-02-28 | 49
1 | N Bank | xxx | 1999-01-08 | 64

And the question remains same
write a sql that shows the average robbers share(of the robbers participating in robberries) during the most recent year with any robberies and the year in which the total amount robbed was the highest.In order to write this query I don't know if both these tables has to be used.


Hope this time it's clear.

Cheers

Hi Tom,

I have a table T

create table test
(
id number,
t number,
t1 number,
t2 number
);

This table has values like the following :

insert into test values (1,1,0,0);
insert into test values (2,0,1,0);
insert into test values (3,0,1,0);
insert into test values (4,0,0,1);
commit;

I want to get a result as follows:

t t1 t2
1 2 1

That is, I want to see the count of all 1s for each of the columns t, t1 and t2 as a single row.

Thanks.

"select count(decode(t,1,1)), count(decode(t1,1,1)), count(decode(t2,1,1)) from
t; "

should be "from test" instead of "from t" :)

Dear Tom,
i have a OLTP environment database with following setup

hash_join_enabled=true
optimizer_feature_enable=9.2.0
optimizer_index_caching=90
optimizer_index_cost_adj=25
optimizer_max_permutations=2000
query_rewrite_enabled=TRUE
query_rewrite_integrity=TRUSTED

and all the tables are having almost updated statistics aside with hitstograms on all of their columns.


SELECT
DISTINCT A.COMP_APPL_ID AS "App No",
A.APPDATE AS "App Date",
B.CUSTOMERNAME AS "Customer Name",
D.DESCRIPTION AS "Product",
D.SUBTYPEDESCRIPTION AS "Sub-Product",
A.FILELOGINNO AS "Physical File No",
C.DESCRIPTION AS "Location",
E.BPNAME AS "Sourced By"
FROM
LOT_CUSTOMER_T B,
LOT_GLOBALLOAN_T A,
COMPANY_GENERIC C,
PRODUCT D,
BP E,
LOT_WORKFLOWSTAGE_DTL F,
WORKFLOWSTAGE ws,
COMPANY_EMPLOYEE_ROLE er,
COMP_EMP_AREA ea
WHERE
A.COMP_APPL_ID = B.COMP_APPL_ID(+) AND
A.SRC_BPID=E.BPID AND
C.GENERICID = A.CG_LOCATIONCODE AND
B.GUAR_COAP_FLAG(+)='P' AND
D.PRODUCTCODE = A.PRODUCTCODE AND
NVL(A.PRODUCTSUBTYPE,'NULL') = NVL(D.PRODSUBTYPECODE,'NULL') AND
A.COMPANYID=1000 AND
A.COMP_APPL_ID = F.comp_appl_id AND
ws.workflowstageid = f.workflowstageid AND
ws.rolecode = er.cg_rolecode AND
er.productcode = a.productcode AND
er.employeecode = ea.employeecode AND
ea.cg_locationcode = A.CG_LOCATIONCODE and F.STAGESTATUS='P' AND F.STAGE='STYMREPT' order by 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 2.46 2.59 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 1265 94.02 324.06 2 7393514 0 12649
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1269 96.49 326.65 2 7393514 0 12649

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30 (SCB)

Rows Row Source Operation
------- ---------------------------------------------------
20 SORT UNIQUE
2901425 NESTED LOOPS
3631139 HASH JOIN
4866 INDEX FAST FULL SCAN CM_EMP_AR_INDX3 (object id 24087)
12657 NESTED LOOPS OUTER
12628 HASH JOIN
12628 HASH JOIN
12628 HASH JOIN
465 TABLE ACCESS FULL WORKFLOWSTAGE
12628 HASH JOIN
12628 TABLE ACCESS BY INDEX ROWID LOT_WORKFLOWSTAGE_DTL
12628 INDEX RANGE SCAN SREE_LTWORKFLOWDTL_IND6 (object id 31139)
264057 HASH JOIN
42 TABLE ACCESS FULL PRODUCT
264057 TABLE ACCESS FULL LOT_GLOBALLOAN_T
4944 TABLE ACCESS FULL BP
5718 TABLE ACCESS FULL COMPANY_GENERIC
12594 TABLE ACCESS BY INDEX ROWID LOT_CUSTOMER_T
18455 INDEX RANGE SCAN LOT_CUSTOMER_T_INDX1 (object id 24473)
2901425 INDEX RANGE SCAN CM_EMP_RL_INDX7 (object id 29462)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
20 SORT (UNIQUE)
2901425 NESTED LOOPS
3631139 HASH JOIN
4866 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'CM_EMP_AR_INDX3' (NON-UNIQUE)
12657 NESTED LOOPS (OUTER)
12628 HASH JOIN
12628 HASH JOIN
12628 HASH JOIN
465 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'WORKFLOWSTAGE'
12628 HASH JOIN
12628 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'LOT_WORKFLOWSTAGE_DTL'
12628 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'SREE_LTWORKFLOWDTL_IND6' (NON-UNIQUE)
264057 HASH JOIN
42 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PRODUCT'
264057 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'LOT_GLOBALLOAN_T'
4944 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BP'
5718 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'COMPANY_GENERIC'
12594 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'LOT_CUSTOMER_T'
18455 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'LOT_CUSTOMER_T_INDX1' (NON-UNIQUE)
2901425 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CM_EMP_RL_INDX7'
(NON-UNIQUE)

could you give me suggested indexes?
i dont bother even if the optimizer does not use those indexes,but i want to know where the exactly indexes need to be created.
thanks in advance.

Thanks for your quick response.
see the explain plan and tkprof(with statisitcs='all')



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3589 Card=33550 Byte
          s=7381000)

   1    0   SORT (UNIQUE) (Cost=2498 Card=33550 Bytes=7381000)
   2    1     NESTED LOOPS (Cost=1407 Card=33550 Bytes=7381000)
   3    2       HASH JOIN (Cost=1406 Card=362966 Bytes=74045064)
   4    3         INDEX (FAST FULL SCAN) OF 'CM_EMP_AR_INDX3' (NON-UNI
          QUE) (Cost=3 Card=4881 Bytes=53691)

   5    3         NESTED LOOPS (OUTER) (Cost=1401 Card=1213 Bytes=2341
          09)

   6    5           HASH JOIN (Cost=1097 Card=1213 Bytes=201358)
   7    6             HASH JOIN (Cost=1086 Card=1213 Bytes=167394)
   8    7               HASH JOIN (Cost=1072 Card=1213 Bytes=133430)
   9    8                 TABLE ACCESS (FULL) OF 'WORKFLOWSTAGE' (Cost
          =3 Card=465 Bytes=5580)

  10    8                 HASH JOIN (Cost=1068 Card=1213 Bytes=118874)
  11   10                   TABLE ACCESS (BY INDEX ROWID) OF 'LOT_WORK
          FLOWSTAGE_DTL' (Cost=390 Card=10157 Bytes=213297)

  12   11                     INDEX (RANGE SCAN) OF 'SREE_LTWORKFLOWDT
          L_IND6' (NON-UNIQUE) (Cost=39 Card=1)

  13   10                   HASH JOIN (Cost=665 Card=32163 Bytes=24765
          51)

  14   13                     TABLE ACCESS (FULL) OF 'PRODUCT' (Cost=2
           Card=42 Bytes=1554)

  15   13                     TABLE ACCESS (FULL) OF 'LOT_GLOBALLOAN_T
          ' (Cost=661 Card=263946 Bytes=10557840)

  16    7               TABLE ACCESS (FULL) OF 'BP' (Cost=12 Card=4944
           Bytes=138432)

  17    6             TABLE ACCESS (FULL) OF 'COMPANY_GENERIC' (Cost=9
           Card=5727 Bytes=160356)

  18    5           TABLE ACCESS (BY INDEX ROWID) OF 'LOT_CUSTOMER_T'
          (Cost=2 Card=1 Bytes=27)

  19   18             INDEX (RANGE SCAN) OF 'LOT_CUSTOMER_T_INDX1' (NO
          N-UNIQUE)

  20    2       INDEX (RANGE SCAN) OF 'CM_EMP_RL_INDX7' (NON-UNIQUE)

SQL> alter session set statistics_level='all';

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.58       2.77          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      900     65.66     107.06       5887    3977886          0       13482
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      902     68.24     109.84       5887    3977886          0       13482

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30  (SCB)

Rows     Row Source Operation
-------  ---------------------------------------------------
  13482  SORT UNIQUE (cr=3977886 r=5887 w=0 time=107007846 us)
3097218   NESTED LOOPS  (cr=3977886 r=5887 w=0 time=93139700 us)
3906063    HASH JOIN  (cr=51085 r=5887 w=0 time=18144270 us)
   4882     INDEX FAST FULL SCAN CM_EMP_AR_INDX3 (cr=24 r=18 w=0 time=23439 us)(object id 24087)
  13507     NESTED LOOPS OUTER (cr=51061 r=5869 w=0 time=11004008 us)
  13478      HASH JOIN  (cr=9527 r=5678 w=0 time=10308218 us)
  13478       HASH JOIN  (cr=9451 r=5676 w=0 time=10104237 us)
  13478        HASH JOIN  (cr=9344 r=5676 w=0 time=10001398 us)
    465         TABLE ACCESS FULL WORKFLOWSTAGE (cr=16 r=0 w=0 time=918 us)
  13478         HASH JOIN  (cr=9328 r=5676 w=0 time=9945039 us)
  13478          TABLE ACCESS BY INDEX ROWID LOT_WORKFLOWSTAGE_DTL (cr=2390 r=0 w=0 time=120576 us)
  13478           INDEX RANGE SCAN SREE_LTWORKFLOWDTL_IND6 (cr=145 r=0 w=0 time=23638 us)(object id 31139)
 264973          HASH JOIN  (cr=6938 r=5676 w=0 time=8599523 us)
     42           TABLE ACCESS FULL PRODUCT (cr=7 r=0 w=0 time=245 us)
 264973           TABLE ACCESS FULL LOT_GLOBALLOAN_T (cr=6931 r=5676 w=0 time=7296217 us)
   4944        TABLE ACCESS FULL BP (cr=107 r=0 w=0 time=6384 us)
   5727       TABLE ACCESS FULL COMPANY_GENERIC (cr=76 r=2 w=0 time=86230 us)
  13442      TABLE ACCESS BY INDEX ROWID LOT_CUSTOMER_T (cr=41534 r=191 w=0 time=585595 us)
  19495       INDEX RANGE SCAN LOT_CUSTOMER_T_INDX1 (cr=27019 r=0 w=0 time=322852 us)(object id 24473)
3097218    INDEX RANGE SCAN CM_EMP_RL_INDX7 (cr=3926801 r=0 w=0 time=52575212 us)(object id 29462)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
  13482   SORT (UNIQUE)
3097218    NESTED LOOPS
3906063     HASH JOIN
   4882      INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
                 'CM_EMP_AR_INDX3' (NON-UNIQUE)
  13507      NESTED LOOPS (OUTER)
  13478       HASH JOIN
  13478        HASH JOIN
  13478         HASH JOIN
    465          TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                     'WORKFLOWSTAGE'
  13478          HASH JOIN
  13478           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID)
                      OF 'LOT_WORKFLOWSTAGE_DTL'
  13478            INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                       'SREE_LTWORKFLOWDTL_IND6' (NON-UNIQUE)
 264973           HASH JOIN
     42            TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                       'PRODUCT'
 264973            TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                       'LOT_GLOBALLOAN_T'
   4944         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'BP'
   5727        TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                   'COMPANY_GENERIC'
  13442       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'LOT_CUSTOMER_T'
  19495        INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                   'LOT_CUSTOMER_T_INDX1' (NON-UNIQUE)
3097218     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CM_EMP_RL_INDX7'
                (NON-UNIQUE)

********************************************************************************

alter session set sql_trace=false


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30  (SCB)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      2.58       2.77          0          0          0           0
Execute      3      0.00       0.01          0          0          0           0
Fetch      900     65.66     107.06       5887    3977886          0       13482
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      905     68.24     109.85       5887    3977886          0       13482

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.00       0.00          0          0          0           0
Execute     11      0.00       0.00          0          0          0           0
Fetch       11      0.00       0.00          0         24          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.00       0.00          0         24          0          11

Misses in library cache during parse: 1

    3  user  SQL statements in session.
   11  internal SQL statements in session.
   14  SQL statements in session.
    1  statement EXPLAINed in this session.
********************************************************************************
Trace file: rfes_ora_11054.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
      11  internal SQL statements in trace file.
      14  SQL statements in trace file.
       4  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           SCB.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
    1078  lines in trace file.

Best regards
sreenivas 

 

The index CM_EMP_AR_INDX3 contains below columns
EMPLOYEECODE
PRODUCTCODE
CG_LOCATIONCODE
AND tablename is COMP_EMP_AREA "ea"
and joins are as follows

er.employeecode = ea.employeecode AND
ea.cg_locationcode = A.CG_LOCATIONCODE



Thanks in advance
regards
sreenivas

Hi tom

i was trying to write a query that will return all the tables in a schema and total number of column in that table and number of index in that table and constrains if any and total number of records in each table. but i beleve it is not possible with a normal sql.

the output is some thing like....

TABLE_NAME COL INDEX CONSTRAIN ROW
---------------------------------------------------
TABLE1 10 0 0 250
TABLE2 15 2 1 10000

i was planing to have this as a base query and planing to have drill down queries which will show the column detail,
index detail,constrain detail. etc.

can you help me with this.

thank you.

Thank you for your answer.

can you please tell me where could i get the informatin about all the table that oracle uses to manage the database.

thank you.

P.Karthick.

Dear Mr. Kyte,

I would like to know that, What sorting & seraching (Quick sort / Bubble sort / Binary search etc) technique/s does Oracle applies in the background while sorting or searching databases?

Secodly, whenever we query a Table/View etc (select... from Tab1), we always see only the names of the columns along with records present therein. But, why dont we see the name of the table itself by default? Whats the logic behind this? Is there any way using which the name of Table/View can also be displayed automatically when querried (other than using Reports)?

Hi Tom,

select id from a
where tt = (select max(tt) from a
where id1 in (select id from b
where res = 10)
and col1 = 'D');

Is there a better way to rewrite this query?

Hi Tom,

TT is a column of date datatype and it is not unique. Is it possible for you to demonstrate how to rewrite that query?

Thanks.

Hello Tom,
Could you please provide the documentation link
for TKPROF??

Hi Tom,

I need to get results as follows:

I have a table t which has about 9 mil rows

create table t
(
lname varchar2(10),
mname varchar2(10),
fname varchar2(10),
empid number,
id2 varchar2(10)
);

I need to get the following results:

I need a query which will return rows which have same mname, fname, empid, id2 but different lname. What is the easiest way to do this?

Thanks.

Hi Tom,

I required your expertise help for writing a SQL statement in Oracle 8.1.7 and onwards

create table c ( c1 number(3), c2 varchar2(10));

create table a ( a1 number(3), a2 number(3), a3 number, a4 number(1))


insert into c values (1, 'parag');
insert into c values (2, 'tom');
insert into c values (3, 'jonathan');
insert into c values (4, 'neville');
insert into c values (5, 'sam');

commit;


insert into a values (1, 1, 100, 1);
insert into a values (1, 2, 100, 1);
insert into a values (1, 3, 400, 1);
insert into a values (2, 1, 500, 3);
insert into a values (2, 2, 600, 1);
insert into a values (2, 3, 7000,1);
insert into a values (3, 1, 500, 3);
insert into a values (5, 1, 300, 3);
insert into a values (5, 1, 400, 3);
insert into a values (5, 1, 700, 3);


I have two tables c and a. C is client master while A is accounts master. A -> C is many to one relationship. These two tables linked to each other by using column c1 and a1. Column a4 in table is account modification indicator. if value of a4 is > 2 then account is closed.

I want to report all clients who have no accounts or whose ALL accounts are closed.

I want output in following way

3 jonathan ALL ACCOUNTS CLOSED
4 neville NO ACCOUNTS
5 sam ALL ACCOUNTS CLOSED

Can you show me how to write this SQL ? ( In acutal system I have huge no of records in client and accounts master )

regards & thanks
pjp