Excellent as usual

Refer to ur Query just compied from above:

select item_no, sum(qty)
2 from (
3 select distinct item_no, bin,
4 first_value(qty) over(partition by item_no, bin
5 order by effective_date desc) qty
6 from t
7 )
8 group by item_no
9 /

I have been reading ur Book and been trying these Analytic Functions myself..but sometimes i had to use Distinct to get correct results ---otherwise , it doesn't work..

Can u please Clarify a bit more why r u using Distinct and then first_value here..
Wouldn't first_value be sufficient ?

Thanks and Best Regards,

pascal

Tom:

I have this query:

ISD> select a.request_id,a.shipment_id,b.item_number,b.stock_number,
2 max(a.shipment_date) from shipment a, shipped_item b
3 where a.shipment_id = b.shipment_id and b.disposition='B'
4 group by a.request_id,a.shipment_id,stock_number,item_number;

REQUEST_ID SHIPMENT_ID ITEM_NUMBER STOCK_NUMB MAX(A.SHIPMENT_DATE)
---------- ----------- ----------- ---------- --------------------
593 2074 2 AC010 24-jun-2003 00:00:00
593 2074 4 BB005 24-jun-2003 00:00:00
593 2075 2 AC010 25-jun-2003 00:00:00
593 2075 4 BB005 25-jun-2003 00:00:00
594 2076 1 AC010 24-jun-2003 00:00:00
594 2076 2 BB005 24-jun-2003 00:00:00
594 2076 3 MS193 24-jun-2003 00:00:00

7 rows selected.

What i am tring to get is the records of the shipment of the max date. so here it would exclude the first two. how would you do this.

Tom:

Please disregard previous query. here is the actual one. I have two tables: shipment (PK is shipment_id) and shipped_item (PK is shipment_id,stock_number and item_number). Every shipment record that has a backorder flag, I want to read the quantity. However if it is the same item and request id I am only intersted in the last shipped one. SO here the result should be so that I skip
the first and third records.

1 select a.request_id,b.stock_number,b.backorder_quantity,
2 a.shipment_date from shipment a, shipped_item b
3 where a.shipment_id = b.shipment_id and b.disposition='B'
5* order by 1,2
ISD> /

REQUEST_ID STOCK_NUMB BACKORDER_QUANTITY SHIPMENT_DATE
---------- ---------- ------------------ --------------------
593 AC010 190 24-jun-2003 00:00:00
593 AC010 145 25-jun-2003 00:00:00
593 BB005 380 24-jun-2003 00:00:00
593 BB005 300 25-jun-2003 00:00:00
594 AC010 90 24-jun-2003 00:00:00
594 BB005 50 24-jun-2003 00:00:00
594 MS193 50 24-jun-2003 00:00:00

7 rows selected.

Tom:

Thanks a lot. Is it any way possible using analytical functions too to get the min(date) in the result set. Your answer gave me what I need. Now is it possible to add the min(shipment_date) from the first record displayed in the initial result set to the final result set which displays the record with maximum(shipment_date)?

Tom:

I think you misunderstood me or maybe I misunderstood your last comment.

The max( ) over gave me the result set however I am trying to grab the lowest ship date from the first record. For example, for this data set, I need to grab the record (quantity) from record highest shipment_date but then I want to grab lowest shipment date (initial date).

REQUEST_ID STOCK_NUMB BACKORDER_QUANTITY SHIPMENT_DATE
---------- ---------- ------------------ --------------------
593 AC010 190 24-jun-2003 00:00:00
593 AC010 145 25-jun-2003 00:00:00
593 BB005 380 24-jun-2003 00:00:00
593 BB005 300 25-jun-2003 00:00:00
594 AC010 90 24-jun-2003 00:00:00
594 BB005 50 24-jun-2003 00:00:00
594 MS193 50 24-jun-2003 00:00:00

i WOULD GET FOR AC010:


593 AC010 145 24-jun-2003
or


593 AC010 145 25-jun-2003 24-jun-2003
(here the min(date) is added as another column.

Thank you Tom.

Tom:

I have this query that gives me all storage codes for each stock item for "WASHDC" warehouse. Problem is that if a stock item is not stored anywhere in "WASHDC" it will be excluded from the result set. I want a master report of all stock items that shows blank entries if a stock item does not have an entry. ALl stock items are listed in stock_item table.

What can i do to get this "resultset" to union with all other stock items that are not in the result set just to be displayed on the report.

select stock_number,description,
max(decode(seq,1,storage_code,null)) Loc#1,
max(decode(seq,1,qty_Available,null)) qty#1,
max(decode(seq,2,storage_code,null)) Loc#2,
max(decode(seq,2,qty_Available,null)) qty#2,
max(decode(seq,3,storage_code,null)) loc#3,
max(decode(seq,3,qty_Available,null)) qty#1,
max(decode(seq,4,storage_code,null)) loc#4,
max(decode(seq,4,qty_Available,null)) qty#4
from (
select stock_number, storage_code,description,qty_available,
row_number() over(partition by stock_number order by stock_number nulls last) seq
from (select a.stock_number, b.storage_code,a.description,
compute_qty_stored(b.warehouse_id,a.stock_number,b.storage_Code) qty_available
from stock_item a, physical_inventory b
where a.stock_number = b.stock_number(+)
and b.warehouse_id in (''WASHDC'')
and a.stock_number <> ''99999''
union all
select a.stock_number, b.storage_code,a.description,
compute_qty_stored(b.warehouse_id,a.stock_number,b.storage_Code) qty_available
from stock_item a, storage_receipt b
where a.stock_number = b.stock_number(+)
and b.warehouse_id in (''WASHDC'')
)
group by stock_number,storage_Code,description,qty_available )
where seq <=4

THanks,

TOm:

1. DO you mean doing like this :

select * from (query) a, stock_item b where a.stock_number=b.stock_number(+);

2. or do the join in the innermost (warehouse column) query like :

select a.stock_number, b.storage_code,a.description,

compute_qty_stored(b.warehouse_id,a.stock_number,b.storage_Code) qty_available
from stock_item a, physical_inventory b
where a.stock_number = b.stock_number(+)
and b.warehouse_id(+) in (''WASHDC'')
and a.stock_number <> ''99999''

thanks

Hello Tom,
I have a question for you regarding a query.It deals with
selecting min and max values in a query.For example:
select ename,sal from emp where sal = (select min(sal) from
emp ) or sal = (select max(sal) from emp);
It returns me the employees getting min and max sal values
in the table emp.My question is
"Can this query be put using the In operator like
select ename,sal from emp where sal in('query1','query2');" query1 deals with min(sal) and query2
deals with max(sal).Is this possible?I tried but it throws errors.Could you please help?
Thanks in advance.Please do reply.
P.S:) Please specify the other formats of the same query.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename
  2   from emp
  3   where sal in ( (select min(sal) from emp), (select max(sal) from emp) );
 
ENAME
----------
SMITH
KING
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename
  2   from emp
  3   where sal in (select min(sal) from emp union select max(sal) from emp);
 
ENAME
----------
SMITH
KING
 


both work 

SQL> sELECT
  2           employees.USID,
  3           review_status,
  4           jobs.ID,
  5           jobs.STATUS,
  6           JOB_JAVA_PKG.GET_CHEMIST(jobs.ID) ASSIGNED_TO,
  7           jobs.TEST ,
  8           JOB_JAVA_PKG.get_review_status(jobs.ID) REVIEW_STATUS,
  9           jobs.CREATED_BY ,
 10           jobs.REASON_FOR_CHANGE,
 11           DECODE(STATUS,'CANCELLED',jobs.MODIFIED_BY,NULL) MODIFIED_BY
 12      FROM JOBS, job_chemists, employees, enotebook_reviews
 13          -- where jobs.id is not null AND jobs.RECORD_STATUS='CURRENT'
 14           where jobs.RECORD_STATUS='CURRENT'
 15           and jobs.id = job_fk_id
 16           and job_chemists.RECORD_STATUS='CURRENT'
 17           and enotebook_reviews.RECORD_STATUS='CURRENT'
 18           and job_chemists.employee_fk_id = employee_id
 19           and e_notebook_entity_id = jobs.id
 20           and job_fk_id = e_notebook_entity_id
 21  /

611 rows selected.

Elapsed: 00:00:06.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1 Bytes=172)
   1    0   NESTED LOOPS (Cost=30 Card=1 Bytes=172)
   2    1     HASH JOIN (Cost=29 Card=1 Bytes=155)
   3    2       TABLE ACCESS (FULL) OF 'JOBS' (Cost=11 Card=615 Bytes=
          47970)

   4    2       HASH JOIN (Cost=17 Card=709 Bytes=54593)
   5    4         TABLE ACCESS (FULL) OF 'JOB_CHEMISTS' (Cost=11 Card=
          611 Bytes=25051)

   6    4         INDEX (FAST FULL SCAN) OF 'LOU1' (NON-UNIQUE) (Cost=
          5 Card=2156 Bytes=77616)

   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=1 Car
          d=485 Bytes=8245)

   8    7       INDEX (UNIQUE SCAN) OF 'EMPLOYEES_PK' (UNIQUE)




Statistics
----------------------------------------------------------
       1833  recursive calls
          0  db block gets
      42323  consistent gets
          0  physical reads
          0  redo size
      43153  bytes sent via SQL*Net to client
        498  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        611  rows processed 

Thanks Tom,

do you think is a good idea to call a fuction like this
in side the select statement. The reason why I have it
througth my code is because I don't want to do a lot joins.
Please tell me if is a good idea to call it. Anyway,
here is the function I called get chemist from the previous
select. Any idea will be great to make it better!!!

FUNCTION get_chemist (p_job IN VARCHAR2)
RETURN VARCHAR2
IS
v_emp_id VARCHAR2 (35);
v_emp VARCHAR2 (50);
BEGIN
SELECT employee_fk_id
INTO v_emp_id
FROM job_chemists
WHERE job_fk_id = TRIM (p_job) AND record_status = 'CURRENT';

SELECT usid
INTO v_emp
FROM employees
WHERE TRIM (employee_id) = TRIM (v_emp_id);

RETURN v_emp;
END

Hi Tom,

I have been trying hard to think of a way to do this with just one easy SQL statement, as opposed to many PL/SQL code blocks.

The main issue is that we loaded data from a networked database to an Oracle 9i database without a hitch. During development, we discovered that in designing the new tables prior to the load, we missed the need to add a key column in one of the tables to establish a relationship with another. This was a big mistake. Unfortunately, we do not have time now to unload and reload the data into re-designed tables. The population of the owner key can only be done during the unload process, and there is simply no time left. The only option we have is to come up with a workable solution. I prefer, if possible, a single SQL statement that can retrieve the correct results.

So for example, in Table A:

SEQNO EFFECTIVE_DATE SERVICE_STATUS
2 20020426 ND
2 20020130 AB
2 20020129 AB
2 20011009 AB
2 20011002 AB
2 20010912 AB
2 20001028 AB
2 20001026 NP
1 20030328 AB
1 20020722 AB
1 20020720 AB
1 20020719 AB
1 20020717 NP

And in Table B:

SERIAL_NUMBER SEQNO EFFECTIVE_DATE
21814290378 2 20020426
21814290378 2 20020130
21814290378 2 20020129
21814290378 2 20011009
21814290378 2 20011002
21814317918 2 20010912
21814317918 2 20001028
9300134799 1 20030328
9300134799 1 20020722
9300134799 1 20020720
9300134799 1 20020719

There should be a SERIAL_NUMBER field in Table A so that we can pin-point the EFFECTIVE_DATE. This is the column that is missing. Table A should look like:

SEQNO EFFECTIVE_DATE SERVICE_STATUS SERIAL_NUMBER
2 20020426 ND 21814290378
2 20020130 AB 21814290378
2 20020129 AB 21814290378
2 20011009 AB 21814290378
2 20011002 AB 21814290378
2 20010912 AB 21814317918
2 20001028 AB 21814317918
2 20001026 NP
1 20030328 AB 9300134799
1 20020722 AB 9300134799
1 20020720 AB 9300134799
1 20020719 AB 9300134799
1 20020717 NP


So the way this works is that for a SEQNO, you can have multiple serial numbers. We need to produce a report that looks like:

SERIAL_NUMBER SRV_BEGIN
------------------------------------
9300134799 20020717
21814290378 20011002
21814317918 20001026

The logic is this: For each sequence number, list the effective dates of each serial number. The effective date should be the earliest date for that serial number. If the SERVICE_STATUS column in Table A is 'NP', then use the effective date for that serial number. If it isn't, then use the date that is in Table B. This seems simple, but is quite complicated. I am hoping that you can come up with a single SQL statement to do this.

Thanks.

David

Do you mean "where service_status = NP"? How would you do it with one SQL statement?

Thanks for your response.Sorry to disturb you again.I need a
query which returns deptno wise highest and lowest salary
getting employees.For ex. I expect the resultset to be as
follows:
deptno Min(sal) Minsal_ename Max(sal) Maxsal_ename
10 800 Smith 5000 King
20 1500 ... ...
30 ... .... .... ...
I need a query like this?Could you please Provide a query?
Please specify different formats of the same query.

please specify different formats?  do we get bonus points on homework for that :)


tell me, what happens when two people (or thirty people, whatever) make the most or least -- are we to pick on at random?  

well, here are three ways -- note that the last query returns a different -- yet equally valid -- answer due to this duplicity

ops$tkyte@ORA920> create table emp as select * from scott.emp order by ename;
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select deptno,
  2         to_number(substr(xmax,1,14)) max_sal,
  3         substr(xmax,15) max_name,
  4         to_number(substr(xmin,1,14)) min_sal,
  5         substr(xmin,15) min_name
  6    from (
  7  select deptno,
  8         max( to_char(sal,'0000000000.00') || ename ) xmax,
  9         min( to_char(sal,'0000000000.00') || ename ) xmin
 10    from emp
 11   group by deptno
 12         )
 13  /
 
    DEPTNO    MAX_SAL MAX_NAME      MIN_SAL MIN_NAME
---------- ---------- ---------- ---------- ----------
        10       5000 KING             1300 MILLER
        20       3000 SCOTT             800 SMITH
        30       2850 BLAKE             950 JAMES
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select deptno,
  2         max(case when rn<>1 then sal else null end) max_sal,
  3         max(case when rn<>1 then ename else null end) max_ename,
  4         max(decode(rn,1,sal)) min_sal,
  5         max(decode(rn,1,ename)) min_ename
  6    from (
  7  select *
  8    from (
  9  select deptno,
 10         row_number() over ( partition by deptno order by sal ) rn,
 11         max(sal) over (partition by deptno) max_sal,
 12         ename,
 13         sal
 14    from emp
 15         )
 16   where rn = 1 or sal = max_sal
 17         )
 18   group by deptno
 19  /
 
    DEPTNO    MAX_SAL MAX_ENAME     MIN_SAL MIN_ENAME
---------- ---------- ---------- ---------- ----------
        10       5000 KING             1300 MILLER
        20       3000 SCOTT             800 SMITH
        30       2850 BLAKE             950 JAMES
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select deptno,
  2         max_sal,
  3         (select ename from emp where sal = max_sal and rownum = 1 ) max_ename,
  4         min_sal,
  5         (select ename from emp where sal = min_sal and rownum = 1 ) min_ename
  6    from (
  7  select deptno, min(sal) min_sal, max(sal) max_sal
  8    from emp
  9   group by deptno
 10         )
 11  /
 
    DEPTNO    MAX_SAL MAX_ENAME     MIN_SAL MIN_ENAME
---------- ---------- ---------- ---------- ----------
        10       5000 KING             1300 MILLER
        20       3000 FORD              800 SMITH
        30       2850 BLAKE             950 JAMES
 

How to reverse the rows of a table?i.e.starting from the last row and proceeding upwards.Can this be done in sql?I
tried in pl/sql using index by tables with attributes last
and prior.It works fine.Please show me a way how to do it
sql.
Thanks in advance.
Bye!

Dear Tom,
Read your response.One of my Oracle University Tutors said
the following information."A row in the table can be the last row if it has the maximum rowid and a row can be the
first if it has the minimum rowid".Is this correct information or misleading one?I have a question also for you
and kindly bear with us.It is
select * from emp where &N = (select ....);
Keep the data as it is (i.e keeping data in the order of row insertion ) and suppose if I enter "1" for N it should return the first row and if I enter "5" it should return the fifth row.Is this possible in
sql?Please do reply.
Thanks in advance.

it is incorrect.  

first, what does it mean to be "first" or "last"??????? nothing, nothing at all - not in SQL, not in relational databases.  But, anyway, let me demonstrate:

borrowing the example from:

I'll reuse it here:

ops$tkyte@ORA920> create table t ( x int primary key, a char(2000), b char(2000), c char(2000), d char(2000), e char(2000) );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t(x) values ( 1 );
1 row created.
 
ops$tkyte@ORA920> insert into t(x) values ( 2 );
1 row created.
 
ops$tkyte@ORA920> insert into t(x) values ( 3 );
1 row created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> commit;
Commit complete.
 
ops$tkyte@ORA920> column a noprint
ops$tkyte@ORA920> column b noprint
ops$tkyte@ORA920> column c noprint
ops$tkyte@ORA920> column d noprint
ops$tkyte@ORA920> column e noprint
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t;
 
         X
----------
         1
         2
         3

<b>so, data comes out in the order it is inserted -- or not???  the "last row" is 3, first row is "1" right?  or is it??</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> update t set a = 'x', b = 'x', c = 'x' where x = 3;
 
1 row updated.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> commit;
 
Commit complete.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> update t set a = 'x', b = 'x', c = 'x' where x = 2;
 
1 row updated.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> commit;
 
Commit complete.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> update t set a = 'x', b = 'x', c = 'x' where x = 1;
 
1 row updated.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> commit;
 
Commit complete.

<b>just some updates right -- but carefully planned ones:</b>

 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t;
 
         X
----------
         3
         2
         1

<b>now 3 is the "first" row and 1 is the "last row" but look at the rowids</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select x, rowid, min(rowid) over (), max(rowid) over (),
  2         decode( rowid, min(rowid) over (), 'min rowid' ),
  3         decode( rowid, max(rowid) over (), 'max rowid' )
  4    from t;
 
  X ROWID              MIN(ROWID)OVER()   MAX(ROWID)OVER()   DECODE(RO DECODE(RO
--- ------------------ ------------------ ------------------ --------- ---------
  3 AAAJ49AALAAAQaiAAC AAAJ49AALAAAQaiAAA AAAJ49AALAAAQaiAAC           max rowid
  2 AAAJ49AALAAAQaiAAB AAAJ49AALAAAQaiAAA AAAJ49AALAAAQaiAAC
  1 AAAJ49AALAAAQaiAAA AAAJ49AALAAAQaiAAA AAAJ49AALAAAQaiAAC min rowid
 

<b>3 has max rowid, 1 has min rowid but -- 3 is first and 1 is last!!!</b>

so, that shows the "maximum rowid theory" is false.....





As for the last question -- hopefully you see now that there is NO SUCH THING as the n'th row in a relation database (unless you yourself number them!)


You can get the n'th row from a result set -- but the n'th row can and will change over time.  Only by numbering the rows yourself and using order by can you get the same n'th row time after time after time 


select *
  from ( select a.*, rownum r
           from ( query-to-define-your-result-set ) a
          where rownum <= &n )
 where r = &n;


gets the n'th row from a result set (NOT a table, a result set) 

Hi TOm,

I don't understand the reason behind this behaviour. 

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

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select * from dept
  2  where dname in
  3  (select dname from emp);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

DNAME column is missing in the emp table but still it does not give any error. 

Thanks Tom,

do you think is a good idea to call a fuction like this
in side the select statement. The reason why I have it
througth my code is because I don't want to do a lot joins.
Please tell me if is a good idea to call it. Anyway,
here is the function I called get chemist from the previous
select. Any idea will be great to make it better!!!

FUNCTION get_chemist (p_job IN VARCHAR2)
RETURN VARCHAR2
IS
v_emp_id VARCHAR2 (35);
v_emp VARCHAR2 (50);
BEGIN
SELECT employee_fk_id
INTO v_emp_id
FROM job_chemists
WHERE job_fk_id = TRIM (p_job) AND record_status = 'CURRENT';

SELECT usid
INTO v_emp
FROM employees
WHERE TRIM (employee_id) = TRIM (v_emp_id);

RETURN v_emp;
END

Hi Tom
Here is the test case:
9:25:11 test@ORA901> @test1
09:25:14 test@ORA901> drop table t1;

Table dropped.

09:25:14 test@ORA901> drop table t2;

Table dropped.

09:25:14 test@ORA901> create table t1 ( x int , y varchar2(30) );

Table created.

09:25:14 test@ORA901> create table t2 ( x int , y varchar2(30) );

Table created.

09:25:14 test@ORA901> insert into t1 values ( 1, 'xx' );

1 row created.

09:25:14 test@ORA901> insert into t1 values ( 2, 'xx' );

1 row created.

09:25:14 test@ORA901> select t1.x , t1.y from t1, t2
09:25:14 2 where not exists ( select null from t2 where t2.x = t1.x );

no rows selected

09:25:14 test@ORA901>
09:25:14 test@ORA901> insert into t2 values ( 1, 'xx' );

1 row created.

09:25:14 test@ORA901> select t1.x, t1.y from t1, t2
09:25:14 2 where not exists ( select null from t2 where t2.x = t1.x );

2 xx


My question is:
Why in the first case we get "no rows selected" - I would
have expected all the rows of t1. When I have at least one row in t2 (matching or not), then I get the desired result.
Basically, I want to select t1's values that don't exist
in t2 - . If t2 is empty I want to get all rows of t1.


Thank you!!!!

why are you cartesian product'ing the tables together?

the query should be:


ops$tkyte@ORA920> select t1.x , t1.y from t1
  2  where not exists ( select null from t2 where t2.x = t1.x );
 
         X Y
---------- ------------------------------
         1 xx
         2 xx


that is what the query should be -- when you join to an empty table -- you get no rows. 

It was a stupid mistake - thanx for correcting me!

Dear Sir,
Suppose if I have a string like "Metalink " ,How can I insert a blank space between each character so that string
appears as "M e t a l i n k"?Is a query possible here?
Please do reply.
Thanks
Maer

Thanks for your reply.Please provide some hints to do the
coding.That's where we need your help.
Bye!
with regards,
Maer

really?  seems pretty straight forward...

ops$tkyte@ORA920> create or replace function spacer( p_string in varchar2 ) return varchar2
  2  as
  3          l_string varchar2(4000);
  4  begin
  5          for i in 1 .. length(p_string)
  6          loop
  7                  l_string := l_string || substr(p_string,i,1) || ' ';
  8          end loop;
  9          return l_string;
 10  end;
 11  /
 
Function created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> column ename format a20
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename, spacer(ename) ename from emp;
 
ENAME                ENAME
-------------------- --------------------
ADAMS                A D A M S
ALLEN                A L L E N
BLAKE                B L A K E
CLARK                C L A R K
FORD                 F O R D
JAMES                J A M E S
JONES                J O N E S
KING                 K I N G
MARTIN               M A R T I N
MILLER               M I L L E R
SCOTT                S C O T T
SMITH                S M I T H
TURNER               T U R N E R
WARD                 W A R D
 
14 rows selected.
 
 

Tom:

I would like to ask you about the best way to accomplish this query. It is used to generate a report.

I have 5 tables: Library table stores all libraries. Request table stores all requests submitted by libraries. Requested_Items stores all individual items requested. Shipment table stores all shipments sent to libraries. Shipped_Item stores all shipped items sent to libraries.

Library (LibCode,Name)
Request(Request_ID,LibCode)
Requested_Items(Request_ID,Item_no,Material_ID)
Shipment(Shipment_ID,Request_ID,LibCode)
Shipped_Item(Shipment_ID,Item_no,material_id)

Physical relationships are as follows:
Request table is child table to parent table Library.
Requested_Items table is a child table to parent table Request.
Shipment table is child table to parent table Request.
Shipped_Item is child table to parent table Shipment.

I want to generate a columnar report as follows:

Library Total Total Total Total
Request Request Items Shipments Shipped Items

Here is how I want to do it. Simple way but may be inefficient due to the several table scans.

For x in (select libcode from library)
Loop
Select count(*) into tot_request from request where libcode = x.libcode;
Select count(*) into tot_request_item from requested_items where request_id in (select request_id from request where libcode = x.libcode);
Select count(*) into tot_shipment from shipment where libcode = x.libcode;
Select count(*) into tot_shipped_item from shipped_items where shipment_id in (select shipment_id from shipment where libcode = x.libcode);
Htp.p(tot_request);
Htp.p(tot_request_item);
Htp.p(tot_shipment);
Htp.p(tot_shipped_item);
END LOOP;

Is this an efficient way of doing it or not. If not how would you do it?

Tom,

I have a query pls. have a look at that

Query :
Select * From <Tab> Where Session_ID = <Session_Number>
point_of_sale, origin,destination,decode(category,'Y',1,'F',2,'J',3),
fare_id,rid,mfid,mrid.

Result Set:

MRBD BKGFRM BKGTO DEPFRM DEPTO FID RID MFID MRID
M 3/5/03 6/15/03 4/1/03 6/15/03 149 154 146 5427
M 3/5/03 6/19/03 6/16/03 6/19/03 149 154 147 5430
H 3/5/03 6/19/03 4/1/03 6/19/03 149 154 12364 16149

M 3/5/03 7/15/03 6/20/03 7/15/03 150 155 147 5430
M 3/5/03 7/31/03 7/16/03 7/31/03 150 155 3492 5432
H 3/5/03 7/31/03 6/20/03 7/31/03 150 155 12364 16149

M 3/5/03 8/31/03 8/16/03 8/31/03 151 8351 3491 5429
M 3/5/03 8/15/03 8/1/03 8/15/03 151 8351 3492 5432
H 3/5/03 8/31/03 8/1/03 8/31/03 151 8351 12364 16149

M 3/5/03 12/9/03 12/1/03 12/9/03 152 8353 149 160
M 3/5/03 12/25/0312/10/0312/25/03152 8353 149 160
M 3/5/03 3/31/04 1/1/04 3/31/04 152 8353 150 161
M 3/5/03 12/31/0312/26/0312/31/03152 8353 150 161
M 3/5/03 11/19/039/1/03 11/19/03152 8353 3491 5429
M 3/5/03 11/30/0311/20/0311/30/03152 8353 3491 5429
H 3/5/03 12/9/03 12/1/03 12/9/03 152 8353 12364 16149
H 3/5/03 11/19/039/1/03 11/19/03152 8353 12364 16149
H 3/5/03 11/30/0311/20/0311/30/03152 8353 12364 16149
H 3/5/03 3/31/04 1/1/04 3/31/04 152 8353 12364 16149
H 3/5/03 12/31/0312/10/0312/31/03152 8353 12364 16149
M 3/5/03 8/31/03 8/16/03 8/31/03 4888 8394 3491 5429
M 3/5/03 8/15/03 8/1/03 8/15/03 4888 8394 3492 5432
H 3/5/03 8/31/03 8/1/03 8/31/03 4888 8394 12364 16149
M 3/5/03 12/9/03 12/1/03 12/9/03 4889 8395 149 160
M 3/5/03 12/25/0312/10/0312/25/034889 8395 149 160
M 3/5/03 3/31/04 1/1/04 3/31/04 4889 8395 150 161
M 3/5/03 12/31/0312/26/0312/31/03 4889 8395 150 161
M 3/5/03 11/19/039/1/03 11/19/03 4889 8395 3491 5429
M 3/5/03 11/30/0311/20/0311/30/03 4889 8395 3491 5429
H 3/5/03 12/9/03 12/1/03 12/9/03 4889 8395 12364 16149
H 3/5/03 11/19/039/1/03 11/19/03 4889 8395 12364 16149
H 3/5/03 11/30/0311/20/0311/30/03 4889 8395 12364 16149
H 3/5/03 3/31/04 1/1/04 3/31/04 4889 8395 12364 16149
H 3/5/03 12/31/0312/10/0312/31/03 4889 8395 12364 16149
M 3/5/03 6/15/03 4/1/03 6/15/03 15682 23867 146 5427
M 3/5/03 7/15/03 6/16/03 7/15/03 15682 23867 147 5430
M 3/5/03 12/25/0312/1/03 12/25/03 15682 23867 149 160
M 3/5/03 3/31/04 12/26/03 3/31/04 15682 23867 150 161
M 3/5/03 11/30/038/16/03 11/30/03 15682 23867 3491 5429
M 3/5/03 8/15/03 7/16/03 8/15/03 15682 23867 3492 5432
H 3/1/03 3/31/04 3/1/03 3/31/04 15682 23867 12364 16149
H 4/1/07 3/31/08 4/1/07 3/31/08 15756 23990 12454 16232




What I am looking for :

Tom, If you check FID, and their Departure From & Departure To dates...there should be continuity. I mean the sequence of date breaks.

For Ex :-

FID DEPFRM DEPTO MRBD
149 01/04/2003 15/06/2003 M
149 16/06/2003 19/06/2003 M
149 01/04/2003 19/06/2003 H

This is the perfect output according to the date break sequence. But When you check FID '151', the date breaks are improper.

For Ex:-

FID DEPFRM DEPTO MRBD
151 16/08/2003 31/08/2003 M
151 01/08/2003 15/08/2003 M
151 01/08/2003 31/08/2003 M

So the output should come as

FID DEPFRM DEPTO MRBD
151 01/08/2003 15/08/2003 M
151 16/08/2003 31/08/2003 M
151 01/08/2003 31/08/2003 M


Pls. ensure that the date sequence should come for all the FID's.

I would appreciate, if you can answer my query.

thxs in advance.

Tom,

I need a query to filter the records according to the above examples I had enclosed.

thxs in advance.

<quote>
For Ex :-

FID DEPFRM DEPTO MRBD
149 01/04/2003 15/06/2003 M
149 16/06/2003 19/06/2003 M
149 01/04/2003 19/06/2003 H

This is the perfect output according to the date break sequence. But When you
check FID '151', the date breaks are improper.

For Ex:-

FID DEPFRM DEPTO MRBD
151 16/08/2003 31/08/2003 M
151 01/08/2003 15/08/2003 M
151 01/08/2003 31/08/2003 M

So the output should come as

FID DEPFRM DEPTO MRBD
151 01/08/2003 15/08/2003 M
151 16/08/2003 31/08/2003 M
151 01/08/2003 31/08/2003 M
</quote>
PMJI, but you don't show the query, like Tom said -- I used just order by depto and figured it out.
BTW: you have a 'H' on MRBD for 151, 01/08/2003, 31/08/2003.

ops$marcio@MARCI9I1> select fid, depfrm, depto, mrbd
2 from t
3 where fid=151
4* order by depto
ops$marcio@MARCI9I1> /

FID DEPFRM DEPTO M
---------- ---------- ---------- -
151 01/08/2003 15/08/2003 M
151 16/08/2003 31/08/2003 M
151 01/08/2003 31/08/2003 H

regards,

Tom:

1. If I have two tables one for Requests (Orders) and one for shipments linked by request_id and each table has a child table for items. Is it possible to run a sql statement to get count of items in each order (requested and shipped).

Requests
(request_id number(10),
request_date date);

Requested_items
(request_id number(10),
item_number number(5),
stock_number varcahr2(10),
quantity number(8))

Shipment
(shipment_id number(10),
request_id number(10),
shipment_date date)

Shipped_items
(shipment_id number(10),
item_number number(10),
stock_number varchar2(10),
quantity number )

What i want is count the items requested versus items shipped and the quantities for each request. One request can have multiple shipments.

Request ID Tot req Items Tot req Qty
Tot ship Items Tot Qty shipped

100 5 3 1000 800

Thank you



Thank you,

You give 3 way to get result. For performance, which one is better if we have a big table?

Hi Tom,
We have oracle 9.2.0.4 installed on solaris 9.
I have 2000 oracle connections to the database at a
given point of time. Means user may be doing something
or they are idle.

My question is how do I find out the number of
concurrent users at the database level at a given second
always doing something. We just want to find out the
number of concurrent users on the database.
Any help is appreciated.
Thanks,
PRS

Tom:

How would you do this query:

Requests
(request_id number(10),
request_date date);

Requested_items
(request_id number(10),
item_number number(5),
stock_number varcahr2(10),
quantity number(8))

Shipment
(shipment_id number(10),
request_id number(10),
shipment_date date)

Shipped_items
(shipment_id number(10),
stock_number varchar2(10),
quantity number,
code varchar2(1) )

I want to get a count of the backorder items that have been fullfilled in later shipments.

Initially when items shipped they get a code of "A" if shipped in full or "B" if it is on backorder. Later, if the backorder item was shipped it will get a code of "A".

Let us say I have this in shipments(shipment_id,request_id,date)

100,1,04/01/04
105,2,04/05/04

and in shipped_items(shipment_id,stock_no,qty,code)

100,AC002,10,A
100,AC006,5,B
101,AC006,5,A (here backorder for request 1(item2) gets filled)
105,BB005,6,A
105,BB006,10,B (here backorder for request 2(item2) stays open).

When I run the query i should get a count of 1 since only 1 item has been marked with A and then (same item) with B.

No I mean 101 however I forgot to include it in the shipment table. One Request can have many shipments.

Request and shipments are linked ussing Request_ID. Shipment and Shipped_items are linked using shipment_id.

You gave me the idea though?

select count(*)
from (
select a.request_id,b.stock_number,b.code
from shipment a, shipped_items b
where a.shipment_id = b.shipment_id
group by request_id, stock_number
having count(distinct code) = 2
)


Thank you,

Tom:

In the query above I should have told you that are about 8 shipment codes and not only "A" or "B". However I am only interested in couting the item that has a "B" ijnitially and then they shipped it and got a code of "A". In this case what would you add to this?

having count(distinct code) = 2 and code in ('A','B')

Is this right?

Tom:

Can you check why this query is not giving the right answer. In the first set of data the count should be 0 because not one item has had two codes "A" and "B". In the second set of data I shipped one backorder item, and the count should change to 1 but it gave me 2 instead.

I am trying to get a total count of all items that have been shipped by request that get two codes "A" and "B".


SELECT e.request_id,disposition,f.stock_number
from shipment e, shipped_item f
where e.shipment_id = f.shipment_id(+) and
e.ship_to_org = 'DC1A' and
f.disposition in ('A','B')
group by request_id, disposition,stock_number;

REQUEST_ID D STOCK_NUMB
---------- - ----------
795 A AC008
795 A BB010
795 A CA294
795 B MA187
796 A AC008
796 A BB010
796 B CA294


select count(*) from
(
SELECT e.request_id,disposition,f.stock_number
from shipment e, shipped_item f
where e.shipment_id = f.shipment_id(+) and
e.ship_to_org = 'DC1A' and
f.disposition in ('A','B')
group by request_id, disposition,stock_number
)
group by request_id,stock_number
having count(distinct disposition)=2


no rows selected


Now when I ship one of the backorder items the count changes to 2 instead of 1.

REQUEST_ID D STOCK_NUMB
---------- - ----------
795 A AC008
795 A BB010
795 A CA294
795 A MA187
795 B MA187
796 A AC008
796 A BB010
796 B CA294

Thank you,

hi tom,
how should i change this query so it will return only one row, where hct.batch_num=max(hct.batch_num)...that is the first row
here is my query and the resultset

SELECT hct.batch_num,hct.batch_seq_num,hct.ap_order_num,hct.svc_num,sm.svc_num
FROM hist_cust_trans hct,service_mstr sm
WHERE hct.svc_num=sm.svc_num
AND hct.svc_type_cd=sm.svc_type_cd
AND hct.trans_status_cd not in ('NP', 'AN')
AND decode(hct.gcc_dest_num,NULL,'0','1')=sm.rstrctd_ind
AND substr(hct.ap_order_num,1,1)='T'
AND hct.svc_num=4042091777

45474 49472 TOFHNBQ2 4042091777 4042091777
45474 49473 TOFHNBQ2 4042091777 4042091777
45474 49474 TOFHNBQ2 4042091777 4042091777
43775 39582 TO803XY6 4042091777 4042091777
43775 39583 TO803XY6 4042091777 4042091777
43574 52701 TO8D5JX0 4042091777 4042091777
43574 52700 TO8D5JX0 4042091777 4042091777


Thank You

there are many ways to accomplish this.  assume this is your current query and you want the row(s) with the max(sal) reported:

ops$tkyte@ORA9IR2> select sal, empno, ename
  2    from emp
  3   where deptno = 20;
 
       SAL      EMPNO ENAME
---------- ---------- ----------
       800       7369 SMITH
      2975       7566 JONES
      3000       7788 SCOTT
      1100       7876 ADAMS
      3000       7902 FORD
 
<b>analytics get that easily:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sal, max(sal) over () max_sal, empno, ename
  2    from emp
  3   where deptno = 20;
 
       SAL    MAX_SAL      EMPNO ENAME
---------- ---------- ---------- ----------
       800       3000       7369 SMITH
      2975       3000       7566 JONES
      3000       3000       7788 SCOTT
      1100       3000       7876 ADAMS
      3000       3000       7902 FORD
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (
  3  select sal, max(sal) over () max_sal, empno, ename
  4    from emp
  5   where deptno = 20
  6         )
  7   where sal = max_sal;
 
       SAL    MAX_SAL      EMPNO ENAME
---------- ---------- ---------- ----------
      3000       3000       7788 SCOTT
      3000       3000       7902 FORD

<b>but that shows top-n queries can be tricky -- there are two highest salaries -- we can "solve" that by just getting "one" if that is an issue</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (
  3  select sal, row_number() over (order by sal DESC) rn, empno, ename
  4    from emp
  5   where deptno = 20
  6         )
  7   where rn = 1;
 
       SAL         RN      EMPNO ENAME
---------- ---------- ---------- ----------
      3000          1       7788 SCOTT

<b>alternatively, we can use order by in a subquery:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (
  3  select sal, empno, ename
  4    from emp
  5   where deptno = 20
  6   order by sal desc
  7         )
  8   where rownum = 1;
 
       SAL      EMPNO ENAME
---------- ---------- ----------
      3000       7788 SCOTT
 
<b>as well -- there are other ways (if interested, i have a list of them in my book Effective Oracle by Design) but those are the two "easiest"</b>
 

Hi tom
That’s not what I am looking for, may be you did not understand my question (I guess I did not give all the information)
First 2 columns make the primary key for the table and I want to return only 1 row where batch_num is greatest. I need to pass this to an update statement meaning I am going to use it as a sub query in an update statement (I am updating service_mstr table) so I need only one row to be returned. In the update statement I would join svc_num to update table. So instead of hct.svc_num=4042091777 I would use join column from update table which is service_mstr. And I cannot use rownum as I am using it in an update statement.
so from the resultset i want to return only "TOFHNBQ2" which is from the last last group.


SELECT hct.batch_num,hct.batch_seq_num,hct.ap_order_num,hct.svc_num,sm.svc_num
FROM hist_cust_trans hct,service_mstr sm
WHERE hct.svc_num=sm.svc_num
AND hct.svc_type_cd=sm.svc_type_cd
AND hct.trans_status_cd not in ('NP', 'AN')
AND decode(hct.gcc_dest_num,NULL,'0','1')=sm.rstrctd_ind
AND substr(hct.ap_order_num,1,1)='T'
AND hct.svc_num=4042091777

43574 52701 TO8D5JX0 4042091777 4042091777
43574 52700 TO8D5JX0 4042091777 4042091777

43775 39582 TO803XY6 4042091777 4042091777
43775 39583 TO803XY6 4042091777 4042091777

45474 49472 TOFHNBQ2 4042091777 4042091777
45474 49473 TOFHNBQ2 4042091777 4042091777
45474 49474 TOFHNBQ2 4042091777 4042091777

Tom,
   I have a following view defined on oracle 9.2.0.4 box.
CREATE OR REPLACE VIEW test_view (
   opportunity_id,
   bo_id_cust,
   wsi_account_id,
   wsi_account_no,
   first_name,
   last_name,
   opportunity_status,
   long1,
   td_opp_flw_up_act,
   long2,
   person_id,
   name,
   ra_campaign_id,
   ra_cmpgn_name,
   row_added_dttm,
   row_lastmant_dttm,
   next_activity_dt,
   act_close_dt,
   act_revenue,
   est_revenue,
   phone,
   dummy
  ,note )
AS
SELECT B.opportunity_id 
,B.BO_ID_CUST 
,A.WSI_ACCOUNT_ID 
,E.WSI_ACCOUNT_NO 
,B.FIRST_NAME 
,B.LAST_NAME 
,B.OPPORTUNITY_STATUS 
,f.xlatlongname long1
,A.TD_OPP_FLW_UP_ACT 
,g.xlatlongname long2 
,B.PERSON_ID 
,D.NAME 
,A.RA_CAMPAIGN_ID 
,C.RA_CMPGN_NAME 
,B.ROW_ADDED_DTTM 
,B.ROW_LASTMANT_DTTM 
,B.NEXT_ACTIVITY_DT
,B.ACT_CLOSE_DT 
,B.ACT_REVENUE 
,B.EST_REVENUE 
,B.PHONE 
,'' dummy
,crm_get_values_ref_cursor_pkg.get_opp_note(B.opportunity_id) note
FROM PS_WSI_RSF_OPP_FLD A 
, PS_RSF_OPPORTUNITY B 
, PS_RA_CAMPAIGN C 
, PS_RD_PERSON_NAME D 
, PS_WSI_ACCOUNT E 
,PSXLATITEM F
,PSXLATITEM G
WHERE A.OPPORTUNITY_ID = B.OPPORTUNITY_ID 
AND A.RA_CAMPAIGN_ID = C.RA_CAMPAIGN_ID(+) 
AND B.PERSON_ID = D.PERSON_ID(+) 
AND A.WSI_ACCOUNT_ID = E.WSI_ACCOUNT_ID(+)
AND f.fieldvalue(+) = b.opportunity_status
AND f.fieldname(+) = 'OPPORTUNITY_STATUS'
AND g.fieldvalue(+) = A.TD_OPP_FLW_UP_ACT 
AND g.fieldname(+) = 'TD_OPP_FLW_UP_ACT'
/

   My query is as shown below.
select /*+ FIRST_ROWS */ * from test_view where person_id = '50004100' and ra_campaign_id  = '4'
   This query comes back in .7 second. Following is the explain plan.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=6444 Card= 3300 Bytes=745800)
   1    0   MERGE JOIN (OUTER) (Cost=6444 Card=3300 Bytes=745800)
   2    1     MERGE JOIN (OUTER) (Cost=5619 Card=3300 Bytes=666600)
   3    2       NESTED LOOPS (OUTER) (Cost=4794 Card=3300 Bytes=587400  )
   4    3         NESTED LOOPS (OUTER) (Cost=3144 Card=3300 Bytes=537900)
   5    4           NESTED LOOPS (OUTER) (Cost=2319 Card=3300 Bytes=435600)
   6    5             NESTED LOOPS (Cost=1494 Card=3300 Bytes=333300)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'PS_RSF_OPPORTUNITY' (Cost=669 Card=3300 Bytes=264000)
   8    7                 INDEX (RANGE SCAN) OF 'PSJRSF_OPPORTUNITY' (NON-UNIQUE) (Cost=12 Card=3300)
   9    6               TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_RSF_OPP_FLD' (Cost=2 Card=1 Bytes=21)
  10    9                 INDEX (UNIQUE SCAN) OF 'PS_WSI_RSF_OPP_FLD' (UNIQUE)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'PSXLATITEM' (Cost=2 Card=1 Bytes=31)
  12   11               INDEX (RANGE SCAN) OF 'PSAPSXLATITEM' (NON-UNIQUE)
  13    4           TABLE ACCESS (BY INDEX ROWID) OF 'PSXLATITEM' (Cost=2 Card=1 Bytes=31)
  14   13             INDEX (RANGE SCAN) OF 'PSAPSXLATITEM' (NON-UNIQUE)
  15    3         TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_ACCOUNT' (Cost=2 Card=1 Bytes=15)
  16   15           INDEX (RANGE SCAN) OF 'PSAWSI_ACCOUNT' (NON-UNIQUE ) (Cost=1 Card=1)
  17    2       BUFFER (SORT) (Cost=5617 Card=1 Bytes=24)
  18   17         TABLE ACCESS (BY INDEX ROWID) OF 'PS_RD_PERSON_NAME' (Cost=2 Card=1 Bytes=24)
  19   18           INDEX (RANGE SCAN) OF 'PSDRD_PERSON_NAME' (NON-UNIQUE)
  20    1     BUFFER (SORT) (Cost=6442 Card=1 Bytes=24)
  21   20       INDEX (FULL SCAN) OF 'PS0RA_CAMPAIGN' (NON-UNIQUE) (Cost=1 Card=1 Bytes=24)
Statistics
----------------------------------------------------------
        287  recursive calls
          0  db block gets
      22191  consistent gets
          0  physical reads
       3420  redo size
      58875  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        264  rows processed

As soon as I add order by clause query take 30 seconds to run. Please see below.

SQL> 
  1  select /*+ FIRST_ROWS */ * from test_view where person_id = '50004100' and ra_campaign_id  = '4'
  2* Order by person_id,row_added_dttm

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=12866 Card =3300 Bytes=745800)
   1    0   MERGE JOIN (OUTER) (Cost=12866 Card=3300 Bytes=745800)
   2    1     MERGE JOIN (OUTER) (Cost=12041 Card=3300 Bytes=666600)
   3    2       NESTED LOOPS (OUTER) (Cost=11216 Card=3300 Bytes=587400)
   4    3         NESTED LOOPS (OUTER) (Cost=10391 Card=3300 Bytes=485100)
   5    4           NESTED LOOPS (Cost=8741 Card=3300 Bytes=435600)
   6    5             NESTED LOOPS (OUTER) (Cost=7916 Card=3300 Bytes=366300)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'PS_RSF_OPPORTUNITY' (Cost=7091 Card=3300 Bytes=264000)
   8    7                 INDEX (FULL SCAN) OF 'PSGRSF_OPPORTUNITY' (NON-UNIQUE) (Cost=26928 Card=3300)
   9    6               TABLE ACCESS (BY INDEX ROWID) OF 'PSXLATITEM'(Cost=2 Card=1 Bytes=31)
  10    9                 INDEX (RANGE SCAN) OF 'PSAPSXLATITEM' (NON-UNIQUE)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_RSF_OPP_FLD' (Cost=2 Card=1 Bytes=21)
  12   11               INDEX (UNIQUE SCAN) OF 'PS_WSI_RSF_OPP_FLD' (UNIQUE)
  13    4           TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_ACCOUNT' (Cost=2 Card=1 Bytes=15)
  14   13             INDEX (RANGE SCAN) OF 'PSAWSI_ACCOUNT' (NON-UNIQUE) (Cost=1 Card=1)
  15    3         TABLE ACCESS (BY INDEX ROWID) OF 'PSXLATITEM' (Cost= 2 Card=1 Bytes=31)
  16   15           INDEX (RANGE SCAN) OF 'PSAPSXLATITEM' (NON-UNIQUE)
  17    2       BUFFER (SORT) (Cost=12039 Card=1 Bytes=24)
  18   17         TABLE ACCESS (BY INDEX ROWID) OF 'PS_RD_PERSON_NAME'(Cost=2 Card=1 Bytes=24)
  19   18           INDEX (RANGE SCAN) OF 'PSDRD_PERSON_NAME' (NON-UNIQUE)
  20    1     BUFFER (SORT) (Cost=12864 Card=1 Bytes=24)
  21   20       INDEX (FULL SCAN) OF 'PS0RA_CAMPAIGN' (NON-UNIQUE) (Cost=1 Card=1 Bytes=24)





Statistics
----------------------------------------------------------
        294  recursive calls
          0  db block gets
      57940  consistent gets
          0  physical reads
       1140  redo size
      60741  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        264  rows processed

SQL>

    It uses a different index. This query comes from peoplesoft application, so that puts the hint. We do not put hint. Our tables are 35% analyzed and indexes are 100% analyzed daily in the night.

    Indexes are as under on PS_RSF_OPPORTUNITY table.
psarsf_opportunity
    next_activity_dt                ASC,
    person_id                       ASC
psbrsf_opportunity
    bo_id_cust                      ASC,
    bo_id_contact                   ASC
psdrsf_opportunity
    row_lastmant_dttm               ASC,
    person_id                       ASC
psersf_opportunity
    last_name                       ASC,
    first_name                      ASC
psgrsf_opportunity
    row_added_dttm                  ASC,
    person_id                       ASC
pshrsf_opportunity
    phone                           ASC
psjrsf_opportunity
    person_id                       ASC,
    opportunity_status              ASC
pskrsf_opportunity
    first_name                      ASC
psmrsf_opportunity
    act_close_dt                    ASC,
    person_id                       ASC
ps_rsf_opportunity
    opportunity_id                  ASC

    Indexes on PS_WSI_RSF_OPP_FLD tables are as under.
psawsi_rsf_opp_fld
    wsi_account_id                  ASC
psbwsi_rsf_opp_fld
    ra_campaign_id                  ASC
pscwsi_rsf_opp_fld
    td_opp_flw_up_act               ASC,
    opportunity_id                  ASC
ps_wsi_rsf_opp_fld
    opportunity_id                  ASC

    Why order by is causing problem? Any insight is appreciated?

Thanks,
PRS 

Tom:

Do you know why I am not getting the answer of 5 here.

SQL> select course_no from course;

 COURSE_NO
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL> select distinct course_no from certificate;

 COURSE_NO
----------
         1
         2
         3
         4


5 rows selected.

SQL> select course_no from course where course_no not in (select distinct course_no
  2  from certificate);

no rows selected
 

Tom:

This is really strange. When i replicate the tables/data in another instance I get correct resutls. When I run the query in TEST instance it gives me corect results. Only when I run the query in DEV instance I get the "no rows found".

Here are the DMLs:

create table course
(course_no number(2) );


create table certificate
(certificate_no number(2),
course_no number(2) );

insert into course values (1);
insert into course values (2);
insert into course values (3);
insert into course values (4);
insert into course values (5);

insert into certificate(10,1);
insert into certificate(10,2);
insert into certificate(10,3);
insert into certificate(10,4);

It looks like you have NULL values in certificate.course_no as...

SQL> select distinct course_no from certificate;

 COURSE_NO
----------
         1
         2
         3
         4


5 rows selected.

...suggests (since there were 5 rows selected instead of 4).  This would cause the NOT IN operator to return FALSE and explain the behavior you are observing. 

that is what I thought -- but he said the output was:


SQL> select course_no from course;

 COURSE_NO
----------
         1
         2
         3
         4
         5

5 rows selected.

no nulls apparent there. 

Hello Sir,
Whenever we issue a Select(query) statement,whether Oracle fetches a
copy of data to be operated upon or the entire original data?
I would like to know this.
Please do reply.

Yes, but it's the certificate table that's being selected from in the subquery.

Tom:

yes it was the null value. however i thought it should run like this:

select course_no from course where course_no not in (select distinct
course_no
from certificate);

select (1,2,3,4,5) where this not in (1,2,3,4,null)

5 is still is not in the set?

Tom:

For the tables listed above, I am trying to get a report that lists several computations per material item in the MATERIAL table as Total requests that include each item, total shipments that include that item etc.

I got two queries working separately and I could not combine them correctly. Can and how do you do it:

select material_id,
count(case when request_id is not null and (request_status = 'Open' or request_status = 'Partial') and request_date < to_date('01-APR-2004') then 1 end) pending_req_cnt,
count(case when request_id is not null and request_date between to_date('01-APR-2004') and to_date ('01-JUL-2004') then 1 end) new_req_cnt,
count(case when request_id is not null and (request_status = 'Open' or request_status = 'Partial') and request_date < to_date ('01-JUL-2004') then 1 end) remain_req_cnt
from
(
select a.request_id,a.request_date,a.request_status,priority,b.item_number,
substr(c.description,1,15) description,c.material_id
from request a,requested_item b,material c
where a.request_id(+)=b.request_id and
b.material_id(+) = c.material_id
order by a.request_id
)
group by material_id


select material_id,
count(case when shipment_id is not null then 1 end) tot_shipments
from
(
select a.shipment_id,c.material_id
from shipment a, shipped_item b, material c
where a.shipment_id(+) = b.shipment_id and
b.material_id(+) = c.material_id
order by a.shipment_id
)
group by material_id

Tom:
Your way was truly brilliant. However it worked with this format:

select X.*,Y.* from
(
QUERY1
) X,
(
QUERY2
) Y

where x.material_id = y.material_id

Is your format better/faster or they both the same since they involve two table scans.

Tom,

Kindly assist me. I do pray i'll get your response.
I have table :
create table mkt_daily
(ddate date,
price number(5,2),
coy varchar2(9),
constraints pk_md primary key (ddate,coy));
with these data:
insert into mkt_daily values(to_date('24-05-2004','dd-mm-yyyy'),'6.2','ASKTOM');
insert into mkt_daily values(to_date('25-05-2004','dd-mm-yyyy'),'6.1','ASKTOM');
insert into mkt_daily values(to_date('27-05-2004','dd-mm-yyyy'),'5.2','ASKTOM');
insert into mkt_daily values(to_date('26-05-2004','dd-mm-yyyy'),'5.2','ASKTOM');
insert into mkt_daily values(to_date('27-05-2004','dd-mm-yyyy'),'5.5','ASKTOM');
insert into mkt_daily values(to_date('28-05-2004','dd-mm-yyyy'),'6.0','ASKTOM');
insert into mkt_daily values(to_date('01-06-2004','dd-mm-yyyy'),'6.8','ASKTOM');
insert into mkt_daily values(to_date('02-06-2004','dd-mm-yyyy'),'5.5','ASKTOM');
insert into mkt_daily values(to_date('03-06-2004','dd-mm-yyyy'),'5.5','ASKTOM');
insert into mkt_daily values(to_date('07-06-2004','dd-mm-yyyy'),'5.6','ASKTOM');
insert into mkt_daily values(to_date('08-06-2004','dd-mm-yyyy'),'5.8','ASKTOM');
insert into mkt_daily values(to_date('09-06-2004','dd-mm-yyyy'),'5.9','ASKTOM');
insert into mkt_daily values(to_date('10-06-2004','dd-mm-yyyy'),'6.0','ASKTOM');
insert into mkt_daily values(to_date('11-06-2004','dd-mm-yyyy'),'6.1','ASKTOM');

My challenge is this: i want to fetch the max(price)High, min(price)Low and last(price)"Closing_Price" for every week.
i.e
week high low Closing_Price
22 6.2 5.2 6.0
23 6.8 5.5 5.5
24 6.1 5.6 6.1

I have tried:
a) select max(price)high,to_char(ddate,'IW') from mkt_daily GROUP BY to_char(ddate,'IW');
b) select min(price),to_char(ddate,'IW') from mkt_daily GROUP BY to_char(ddate,'IW');

Thanks for your anticipated positive response.

ops$tkyte@ORA10G> select to_char(ddate,'iw'), max(price), min(price),
  2         to_number( substr( max(to_char(ddate,'yyyymmdd')||price), 9)) mp
  3    from mkt_daily
  4   group by to_char(ddate,'iw')
  5  /
 
TO MAX(PRICE) MIN(PRICE)         MP
-- ---------- ---------- ----------
22        6.2        5.2          6
23        6.8        5.5        5.5
24        6.1        5.6        6.1
 

Tom,

Many thanks. I am impressed.Your response was timely and fantastic.

Do you have any book that I can read to understand some of the sql tricks, especially combining the sql functions to get desired results like you just did.

PLAYING WITH AGGREGATION

and demonstrated a couple of techniques.  Since we were already aggregating (the by week told me that), i used the technique of encoding the thing we needed to max (the date) in a string and putting the related fields on the end of the string - so we'd get the data for the max(date) plus the other stuff - to substring back out.


if you are interested:

somewhere on your site I saw a way of changing
the text of a query using a pl/sql package ( I think.) This is
useful for queries that you can not directly change
(3rd party application) and so want to intercept
it and silently modify it before executing...
Can you please point me to the thread or give the pl/sql
package name that does it?

thanx!

Tom:

Is there a way to do sql query on a number value(use between) for a varchar2 field that has values as either numbers or text. I want to search only the number values for field between 5000 and 6000.

is it possible?

How can I return multiple rows as a single row using sql queries?

From SQL*Plus, how can I return a single row when the result set contains multiple rows.

For example:

select tablespace_name from dba_segments

returns

SYSTEM
USER
TEMP

but I want this to return as 'SYSTEM','USER','TEMP'.

How can I achieve this from SQL*plus using sql queries?

i am trying to write a sql query which will publish single row info from multiple rows.


create table t(
id number,
cusid number,
cat varchar2(3))
/

INSERT INTO t VALUES (1,1,'crd')
/
INSERT INTO t VALUES (2,2,'ins')
/
INSERT INTO t VALUES (3,2,'crd')
/
INSERT INTO t VALUES (4,2,'lns')
/
INSERT INTO t VALUES (5,3,'ins')
/
INSERT INTO t VALUES (6,3,'crd')
/
INSERT INTO t VALUES (7,4,'lns')
/
INSERT INTO t VALUES (8,4,'ins')
/
INSERT INTO t VALUES (9,4,'crd')
/
INSERT INTO t VALUES (10,5,'ins')
/
INSERT INTO t VALUES (11,6,'ins')
/
INSERT INTO t VALUES (12,6,'ins')
/
INSERT INTO t VALUES (13,7,'lns')
/
INSERT INTO t VALUES (14,7,'lns')
/

from the above data i want ID, CUSID = CUSID where cat = 'crd' OR CUSID should be the first value from the group

the output should be
id cusid cat
1 1 crd
3 2 crd
6 3 crd
9 4 crd
10 5 ins
11 6 ins
13 7 lns

Thanks

CUSID = CUSID where cat = 'crd' OR CUSID should 
be the first value from the group

doesn't make sense to me.  "cusid = cusid"??  "first value from the group"??  what group?

near as I can tell, you might mean:

ops$tkyte@ORA9IR2> select distinct
  2         first_value(id) over (partition by cusid order by decode(cat,'crd',1,2), cat ) fid,
  3         cusid,
  4         first_value(cat) over (partition by cusid order by decode(cat,'crd',1,2), cat ) fcat
  5    from t
  6  /
 
       FID      CUSID FCA
---------- ---------- ---
         1          1 crd
         3          2 crd
         6          3 crd
         9          4 crd
        10          5 ins
        11          6 ins
        13          7 lns
 
7 rows selected.
 
 

Hi Tom

could you please help with the following queries.
I would like to combine them into one massive(relatively
speaking) query. I have them all working correctly
independently. I now have analytics so that would be
even better.

Finds first gift given by donor:
select i.giftamount,i.giftdate
from gift i,acga_dr a where i.giftdate in(select min(g.giftdate)
from gift g
where g.idnumber=i.idnumber
and g.giftamount>0)
and i.idnumber=a.idnumber

Finds last gift given by donor:
select i.giftamount,i.giftdate
from gift i,acga_dr a where i.giftdate in(select max(g.giftdate)
from gift g
where g.idnumber=i.idnumber
and g.giftamount>0)
and i.idnumber=a.idnumber

Performs aggregates on individual donor history and
inserts into new table:
begin
for i in(select g.idnumber,sum(g.giftamount)LTGifts,count(g.giftamount)LTCount,max(g.giftamount)LargestGift, min(g.giftdate)FirstGiftDate,max(g.giftdate)LastGiftDate
from gift g,acga_drL a
where g.idnumber=a.idnumber
group by g.idnumber
)loop
insert into acga_drL(ltgifts,ltcount,largestgift,firstgiftdate,lastgiftdate)
values(i.ltgifts,i.ltcount,i.largestgift,i.firstgiftdate,i.lastgiftdate);
end loop;
end;

thanks!..(sorry about the formatting here)

I haven't done anything with them yet. I just ran those to
test and see if the correct results would get produced(which
they did).

Now I want to include them as part of the "insert" query
so that the results(will create columns) of the first 2 queries can be combined with the columns of the insert query...and that's where I'm kind of stuck.

hope this makes more sense... :~)

ps: analytics is really cool and man does it speed things up

The acga_drL table is the actual table the acga_dr table
is a copy for testing purposes.

Upon testing the 2 queries for correctness using the
test table(acga_dr) I would then combine the two queries
with the insert query to insert the results to the
acga_drL table.

thanks

sorry for the obvious confusion(I'm still developing the
plan as I go along).

I was just given this assignment yesterday with a friday
deadline.

This is a donor research project and what I'm looking at is this:

a) first find all the lapsed donors with 2+ years on file
with a lastgiftamount > 0 between 18-36 months.
I already have that data and inserted that into acga_dr

b) of those donors give us:
Lifetime giftamounts
Lifetime count of gifts
Largest gift amount
First gift date
Last giftdate
First giftamount
Last giftamount
(I am at this stage)

c)then give us an annual cumulative giving history based
on the date of the lastgiftamount>0,-12 months.
This sum will fall into buckets(example):
$10-74 bucket
$75-149 "
$150-499 "
...and so on

d) then give a report on how many of the donors fall
into the above buckets...along with the column results
from step b.

my plan is to use one table to get the results from step
a, use that table to gather the data for steps b & c and
put those results into a separate table. Then create a
view to process the results from both tables for output to a .txt or .csv file.

If you have a better solution I'm all eyes and "ears"

hth

your query did the trick as as far as getting the
First Giftamount and Last Giftamount.

one last question(so I understand your case statement and
can learn from it).

why did you use to_number,substr and to_char to achieve
the results and what does '15' represent?

one last THANKS!

Hi Tom

your ability to do things with SQL is so impressive Like a previous reply, i'm sure many of us would love a small book / list of top 20 SQL tricks etc.

Anyway, I am sure the following cursor / loop could be combined inot a single SQL statements (following your mantra of do it in a single statement if possible), but am not sure how to go about it ?


cursor c1 is

select
y.rowid get_rowid
from
tab1 y,
(select col1, max(col2) collection from tab1 group by col1) x
where
x.col1 = y.col1
and
x.collection = y.collection;

for REC in c1 loop

insert into tab2 (a, b)
select
tab1.clid,
tab2.blid
from
tab1, tab2
where
tab1.rowid = REC.get_rowid
and
tab1.clid = tab2.blid(+)
end loop;


Thanks

Syed

Hi tom

Can you show the final query from above with the analytical function in it ?

Thanks

Tom

Hi Tom,
I am executing the following query:
select
c.cmpname Company,
to_char(t.trnsaledate,'Day, DD-fmMonth-YYYY') Fecha,
TO_CHAR(sum(r.recvalue),'9,999,999,999.00') Total
from
stkrecord r,
stktransaction t,
stkcompany c
where
t.trnsaledate > to_date('30-Jul-2004','DD-Mon-YYYY')
and t.trnsaledate < to_date('23-Aug-2004','DD-Mon-YYYY')
and to_char(t.trnsaledate,'Day') = 'Friday'
and c.cmpid = t.trncmpid
and r.rectrnID = t.trnID
group by c.cmpname, to_char(t.trnsaledate,'Day, DD-fmMonth-YYYY')

It returns me 0 rows.
Why does not the sentence and to_char(t.trnsaledate,'Day') = 'Friday' work.
Am I doing anything wrong? I need only the data for Friday.
Is the approach wrong?
Thanks as always......

ops$tkyte@ORA9IR2> select '"' || to_char(d,'Day') || '"'
  2  from ( select sysdate+rownum d from all_users where rownum <= 7);
 
'"'||TO_CHA
-----------
"Tuesday  "
"Wednesday"
"Thursday "
"Friday   "
"Saturday "
"Sunday   "
"Monday   "
 
7 rows selected.
 
ops$tkyte@ORA9IR2> 1
  1* select '"' || to_char(d,'Day') || '"'
ops$tkyte@ORA9IR2> c/Day/fmDay/
  1* select '"' || to_char(d,'fmDay') || '"'
ops$tkyte@ORA9IR2> /
 
'"'||TO_CHA
-----------
"Tuesday"
"Wednesday"
"Thursday"
"Friday"
"Saturday"
"Sunday"
"Monday"
 
7 rows selected.



trailing blanks -- fm, the format modifier, trims them.

probably better to use:

where to_char(t.trnsaledate,'d') = 
         to_char(to_date('31-dec-1965','dd-mon-yyyy'),'d')


that 31-dec-1965 is a friday, it'll tell us the day of the week a friday is using your NLS setting....

It is safe (no whitespace)
It is international (works in places that spell friday differently!)

 

hai tom,

i have one function fun

this returns 1

if i use this function in the following query

> select * from table_name order by fun;

fun in order by clause

it's not giving any syntactic error but it simply ignoring the order by clause

??? is it correct to use functions in order by clause

??? can we use functions after " from clause" insted of table name, by returing that table name from function

if the function fun returns the constant one, how can you possibly say "it is ignoring it"

??????


select * from table_name order by 'X';

think about that -- what does that do - it orders by the constant 'X'


perhaps you are thinking that:

select * from table_name order by f()

where f() returns the number 1 is the same as:

select * from table_name order by 1;


-- but it cannot be (think about this:

ops$tkyte@ORA9IR2> create table t as select 1 xxx from all_users;
 
Table created.
 
ops$tkyte@ORA9IR2> select * from t order by xxx;
 
       XXX
----------
         1
         1
         1
....


here xxx is not ANY DIFFERENT than your function f())


You are simply ordering by a constant, you are NOT specifying to "order by column 1"
 

Hi,Tom ,Your sql is very userful to me,thankyou

Tom:
I have a table
TABLE A
(src varchar2(30)
Dest varchar2(30),
Start_date DATE,
End_date DATE,
Sent_bytes NUMBER(30)
Received_Bytes NUMBER(30)
);

This is what I am doing now:

We obtain the first connection to the database
We send the initial SQL statement “select * from A” using the first connection. The first connection returns a ResultSet, which contains metadata about the columns of table A and a pointer to the first record We obtain a second connection to the database

For each column defined by the metadata (repeat):
We send a new SQL statement “select distinct(src) from (select * from A) order by src desc” using the second connection.
The second connection returns a ResultSet, which contains all the distinct values of src in descending order
We pre-initialize dimension(src) using the records in this second ResultSet
Close the second connection to the database
Allocate the number of records defined by the first connection’s ResultSet
Now extract all the records returned by the first connection’s ResultSet populating our data structure with “data”
Once data is made available, the application can now define a visualization for this data

We extract out and define each dimension and their unique values in order to optimize user interactions and queries.


The problem is performance is really bad because we are doing column by column and doing distinct on top and sorting on top.

Is there a best way so that I can get the result that I want. If I can get the whole thing in one fetch that will be great - again this is just a sample table - our original table has 20+ columns and 300 million records - this table grows as well.

Any help is greatly appreciated

R

Tom:
Having multiple connection - When I saw the code I felt the same way. I have already reommended them to use a single connection.
Dinstinct Values -
We are developing a visualization package. To chart the data properly the application requires to get all the distinct values from each column.

RIght now for each column one query is being executed. I would like to eliminate that and get all the columns in one query if possible.

R

ops$tkyte@ORA9IR2> create or replace procedure all_distinct_values( p_tname in varchar2, p_cursor in out sys_refcursor )
  2  as
  3      l_stmt  long := 'select distinct ';
  4      l_decode_1 long := 'decode( r';
  5      l_decode_2 long := 'decode( r';
  6      l_decode_3 long := 'decode( r';
  7  begin
  8
  9      for x in (select column_id, data_type, column_name,
 10                       decode( data_type, 'DATE', 'to_char(' || column_name || ', ''yyyymmddhh24miss'' )',
 11                                          'NUMBER', 'to_char(' || column_name || ' )',
 12                                          column_name) fcname
 13                       from user_tab_columns where table_name = p_tname)
 14      loop
 15          l_decode_1 := l_decode_1 || ', ' || x.column_id || ', ''' || x.column_name || '''';
 16          l_decode_2 := l_decode_2 || ', ' || x.column_id || ', ''' || x.data_type || '''';
 17          l_decode_3 := l_decode_3 || ', ' || x.column_id || ', ' || x.fcname ;
 18      end loop;
 19      l_decode_1 := l_decode_1 || ') cname, ';
 20      l_decode_2 := l_decode_2 || ') dtype, ';
 21      l_decode_3 := l_decode_3 || ') value';
 22
 23      l_stmt := l_stmt || l_decode_1 || l_decode_2 || l_decode_3 || ' from ' || p_tname ||
 24      ', (select rownum r from user_tab_columns where table_name = :x )';
 25      open p_cursor for l_stmt using p_tname;
 26  end;
 27  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> exec all_distinct_values( 'EMP', :x );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
CNAME    DTYPE    VALUE
-------- -------- ----------------------------------------
COMM     NUMBER   0
COMM     NUMBER   1400
COMM     NUMBER   300
COMM     NUMBER   500
COMM     NUMBER
DEPTNO   NUMBER   10
DEPTNO   NUMBER   20
......
 

This is so cool - Would you please explain this stored procedure line by line so that those who are not that familiar with some of these techniques will be benefited?

I read close to 50+ queries and responses today - I never so this kind of technical resources anywhere else. Thanks Tom.

Vipul

ops$tkyte@ORA9IR2> create or replace procedure prt ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     loop
  6        exit when l_str is null;
  7        dbms_output.put_line( substr( l_str, 1, 250 ) );
  8        l_str := substr( l_str, 251 );
  9     end loop;
 10  end;
 11  /
 
Procedure created.


if we add a prt call to the procedure, you see what it does:

ops$tkyte@ORA9IR2> exec all_distinct_values( 'EMP', :x );
select distinct decode( r, 1, 'EMPNO', 2, 'ENAME', 3, 'JOB', 4, 'MGR', 5,
'HIREDATE', 6, 'SAL', 7, 'COMM', 8, 'DEPTNO') cname, decode( r, 1, 'NUMBER', 2,
'VARCHAR2', 3, 'VARCHAR2', 4, 'NUMBER', 5, 'DATE', 6, 'NUMBER', 7, 'NUMBER', 8,
'NUMBER') dtype,
decode( r, 1, to_char(EMPNO ), 2, ENAME, 3, JOB, 4, to_char(MGR ), 5,
to_char(HIREDATE, 'yyyymmddhh24miss' ), 6, to_char(SAL ), 7, to_char(COMM ), 8,
to_char(DEPTNO )) value from EMP, (select rownum r from user_tab_columns where
table_name = :x )


it (the procedure) doesn't do much, the query does all of the work.

All we do is build a 3 column query.  We'll start at the end though -- the cartesian product.

Basically, we want to take each row in EMP and output it once for each column in that row.  (eg: emp has 8 columns -- the first row in emp should be output 8 times -- once for each column).  We use rownum R from user_tab_columns for that -- we KNOW user_tab_columns will have *at least* enough rows since it has a row/column.

Then, we output three columns:

column 1 -- is going to be the column name.  The first time we output row 1, we'll pump out "EMPNO", the second time we output row 1 -- ENAME and so on.

column 2 -- the datatype, I'm assuming the application would want this

column 3 -- the value -- taking care to convert everything to a "string" and taking extra care with dates. 

Tom:
Thanks a lot for the solution. This is exactly what I was looking for.

If the table has several million records, do u think the variable can hold that many records or is there a limit?

R

Tom - How can get the result set in a sorted order - asc or desc order?

Tom - would you please point me in the stored procedure where I can put that order by <column_name> clause? so that the data will be in a sorted order. somehow I couldnt figure this out and I am getting compilation error.

Tom:
Here is what I have done and still its giving me error when I execute the query. I have made the changes where we create the statement. Please help to resolve this issue.

What I am looking for is that the value field sorted and grouped by column name


create or replace procedure all_distinct_values( p_tname in
varchar2, p_cursor in out sys_refcursor )
as
l_stmt long := 'select distinct ';
l_decode_1 long := 'decode( r';
l_decode_2 long := 'decode( r';
l_decode_3 long := 'decode( r';
begin
for x in (select column_id, data_type, column_name,
decode( data_type, 'DATE', 'to_char(' || column_name
|| ', ''yyyymmddhh24miss'' )',
'NUMBER', 'to_char(' || column_name
|| ' )',
column_name) fcname
from user_tab_columns where table_name = p_tname)
loop
l_decode_1 := l_decode_1 || ', ' || x.column_id || ', ''' ||
x.column_name || '''';
l_decode_2 := l_decode_2 || ', ' || x.column_id || ', ''' ||
x.data_type || '''';
l_decode_3 := l_decode_3 || ', ' || x.column_id || ', ' || x.fcname
;
end loop;
l_decode_1 := l_decode_1 || ') cname, ';
l_decode_2 := l_decode_2 || ') dtype, ';
l_decode_3 := l_decode_3 || ') value';

l_stmt := l_stmt || l_decode_1 || l_decode_2 || l_decode_3 || ' from '
|| p_tname || ' order by 1, 3 ' ||
', (select rownum r from user_tab_columns where table_name = :x)';
open p_cursor for l_stmt using p_tname;
end;

How do you make a prt call to the procedure? Or how did you get the detailed view of the proecedure that you wrote when you explained the procedure?

Tom - Attached is the updated procedure. I have tried with the SCOTT.EMP table and I got the result correctly except the last column "SAL". There two values out of order and the rest looks good. This is the results that I got with the SAL COLUMN - The first two rows in this result set is not sorted in the right way.

SAL NUMBER 950
SAL NUMBER 800
SAL NUMBER 5000
SAL NUMBER 3000
SAL NUMBER 2975

CNAME DTYPE VALUE
-------- -------- ---------
SAL NUMBER 2850
SAL NUMBER 2450
SAL NUMBER 1600
SAL NUMBER 1500
SAL NUMBER 1300
SAL NUMBER 1250
SAL NUMBER 1100

Modified procedure - sorry for my mistakes in the prev query
create or replace procedure all_distinct_values( p_tname in
varchar2, p_cursor in out sys_refcursor )
as
l_stmt long := 'select distinct ';
l_decode_1 long := 'decode( r';
l_decode_2 long := 'decode( r';
l_decode_3 long := 'decode( r';
begin
for x in (select column_id, data_type, column_name,
decode( data_type, 'DATE', 'to_char(' || column_name
|| ', ''yyyymmddhh24miss'' )',
'NUMBER', 'to_char(' || column_name
|| ' )',
column_name) fcname
from user_tab_columns where table_name = p_tname)
loop
l_decode_1 := l_decode_1 || ', ' || x.column_id || ', ''' ||
x.column_name || '''';
l_decode_2 := l_decode_2 || ', ' || x.column_id || ', ''' ||
x.data_type || '''';
l_decode_3 := l_decode_3 || ', ' || x.column_id || ', ' || x.fcname
;
end loop;
l_decode_1 := l_decode_1 || ') cname, ';
l_decode_2 := l_decode_2 || ') dtype, ';
l_decode_3 := l_decode_3 || ') value';

l_stmt := l_stmt || l_decode_1 || l_decode_2 || l_decode_3 || ' from '
|| p_tname ||
', (select rownum r from user_tab_columns where table_name = :x ) order by 1,3 desc ';
open p_cursor for l_stmt using p_tname;
end;

i have two tables sl_master and br_master
both have a field sub_code.

sl_master --> sub_code -- type is char(6)

br_master --> sub_code -- type is char(10)

i gave the join condition,

"where sl_master.sub_code = br_master.sub_code"

but it is not equating properly.

but when i gave the conditin as

"where trim(sl_master.sub_code) = trim(br_master.sub_code)"

it works fine.

the data in both the tables sub_code field are "CLNT01".

Why is it so.

Because of the datatypes..........

> sl_master --> sub_code -- type is char(6)
> br_master --> sub_code -- type is char(10)

.....the join condition

"where sl_master.sub_code = br_master.sub_code"

equates to 'CLNT01' = 'CLNT01 '
which is obviously not equal.
If the datatypes would have been VARchar(n)

"where sl_master.sub_code = br_master.sub_code"

would return a result, UNLESS the value inserted into br_master.sub_code had been 'CLNT01 '

is there any specific reason for having that difference between char and varchar2.....

Dear Tom,
     I am executing the query "select * from scott.emp where (1 = 0 or empno = 7369) and 1 = 0;" in Oracle "Oracle9i Enterprise Edition Release 9.2.0.1.0". But it's faching one row where as it should fetch none as "1 = 0" shouls always be evaluated to FALSE.

SQL> select * from scott.emp where (1 = 0 or empno = 7369) and 1 = 0;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> 


Kindly Advice.....

Regards...
Sujit 

Table t1(c1 NUMBER)
C1
1
2

Table t2 (c1, number, c2 char(1), c3 varchar2(5))

C1 C2 C3
1 Y M1
1 Y M2
1 N M3
2 Y A1
2 N A2

The relationship t1 and t2 is one to many through t1.c1 = t2.c1

The purpose is to query all rows in table t1 when t2.c2 = ‘Y’

Select DISTINCT t1.c1
From t1, t2
Where t1.c1 = t2.c1
And t2.c3 = ‘Y’

If I have million of row in table t1, this query runs very slow. So, “distinct” is very expensive performance.

Select t1.c1
From t1,
(select distinct c1, c2 from t2
where c2= ‘Y’) a
where t1.c1 = a.c1

By using embedded SQL, the elapsed time has been decreased by 50%.

Do you have any better way to accomplish the same result?

Thanks a lot.

You used 92010, Tom used 92015. Perhaps this is a bug
fixed in a patchset?

Yes i Also think it's some kind of Bug with 9.2.0.1.0 vertion.



SQL*Plus: Release 9.0.1.0.1 - Production on Tue Sep 14 14:09:24 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set lin 2000
SQL> select * from scott.emp where (1 = 0 or empno = 7369) and 1 = 0;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> 


    Anyways thanks TOM for your reply.

Regards...
Sujit
 

Hi Tom,


I have a table supplier which has supplier ID and Supplier Name. I have another table Order_formats which has Order_ID - which is unique for every order placed and Order_XML which holds all the information regarding the order placed. When I run the following query 

SELECT T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal(),
--s.supplier_name,
--formats.order_id,
COUNT(DISTINCT(formats.order_id)) OrderCount,
COUNT(formats.order_id) ItemCount,
--T.extract('/ItemOut/Components/ExtendedPriceFloat/text()').getNumberVal() ItemPrice,
SUM(T.extract('/ItemOut/Components/ExtendedPriceFloat/text()').getNumberVal()) ItemTotal
FROM ORDER_FORMATS1 formats,
TABLE(XMLSEQUENCE(EXTRACT(order_xml,'//ItemOut'))) T,
SUPPLIERS s
WHERE T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()
= s.supplier_id (+)
GROUP BY T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()
ORDER BY
T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()

which is correct answer.

SUP_ID
-----------------------------------------
ORDERCOUNT  ITEMCOUNT  ITEMTOTAL
---------- ---------- ----------
000072
         2          2        1.1

054054
         1          1        200

30000072
         4          4      73.15

30054054
         1          1      -77.5

30068737
         1          1       89.9

30361119
         1          1      65.25

But when I try to add supplier_name, order_id,

  1  SELECT T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal() Sup_ID,
  2  s.supplier_name,
  3  formats.order_id,
  4  COUNT(DISTINCT(formats.order_id)) OrderCount,
  5  COUNT(formats.order_id) ItemCount,
  6  T.extract('/ItemOut/Components/ExtendedPriceFloat/text()').getNumberVal() ItemPrice,
  7  SUM(T.extract('/ItemOut/Components/ExtendedPriceFloat/text()').getNumberVal()) ItemTotal
  8  FROM ORDER_FORMATS1 formats,
  9  TABLE(XMLSEQUENCE(EXTRACT(order_xml,'//ItemOut'))) T,
 10  SUPPLIERS s
 11  WHERE T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()
 12  = s.supplier_id (+)
 13  GROUP BY T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()
 14  ORDER BY
 15* T.extract('/ItemOut/ItemID/SupplierPartID/text()').getStringVal()
SQL> /
s.supplier_name,
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression


Where I am going wrong ... plz do let me know. 

I have heard that doing a what I call "loop join" is not a good practice in sql. What I am referring to here is having three tables x,y,z. x is joined to y, y is joined to z and x is joined to z. I know that the design should change but in this case, I can't change the design. However, in terms of data I need to join one of the columns in table x with one of the columns in z. Is it a bad idea to have this type of "loop join".
I have done tests on my personal oracle and the result sets that I am getting using this so called "loop join" are correct. I just wanted to get your opinion on this one.



SELECT DISTINCT
HDR.HDR_NUM,
ITEM.INVC_NO,
ITEM.INVC_DT,
ITEM.PROD_CD,
HDR.DM_NO,
TRCG.DM_NO,
DISTRNO
FROM HDR, --this is x
ITEM, --this is y
TRCG --this is z
WHERE HDR.HDR_NUM = ITEM.HDR_NUM
AND ITEM.INVC_NO = TRCG.INVC_NO
AND ITEM.INVC_DT = TRCG.INVC_DT
AND ITEM.PROD_CD = TRCG.PROD_CD
AND HDR.DM_NO = TRCG.DM_NO


Thanks,

Ishaque

I have actually ran the query and it is efficient(I actually have parameters which I didn't include here) and you are correct in that I don't need the distinct. I like normalization and I like to use it as well. My concern was having what I call the "loop" (I made this terminology up so I can have a name for what I was trying to describe). "Normally" in a situation like this x would be joined to y, and y would be joined to z. However, in this situation, I am joing z back to x because of that dm_no column which would "close the circuit". I didn't see anything wrong with this. I have done a report and I didn't do the hdr to trcg (x to z join) because somebody mentioned it wasn't a good idea. Lesson learned, don't always listen to others instead try it for yourself first.


Thanks,

Ishaque

CREATE TABLE DTAB( DKEY NUMBER(10))
/

CREATE TABLE IDTAB(DKEY NUMBER(10),
AAFLAG VARCHAR2(1))
/



INSERT INTO DTAB VALUES(1)
/
INSERT INTO DTAB VALUES(2)
/
INSERT INTO DTAB VALUES(3)
/
INSERT INTO DTAB VALUES(4)
/
INSERT INTO DTAB VALUES(5)
/


INSERT INTO IDTAB VALUES(1,'Y')
/
INSERT INTO IDTAB VALUES(2, 'Y')
/
INSERT INTO IDTAB VALUES(2,'N')
/
INSERT INTO IDTAB VALUES(3, 'Y')
/
INSERT INTO IDTAB VALUES(3,'N')
/
INSERT INTO IDTAB VALUES(4,'N')
/


My requirement is to

1.select those dkey's in idtab table where for a given dkey the aaflag is 'Y' or both 'Y' and 'N'
2.do not select those dkeys where aaflag is just 'N' for a given dkey
3.select all those dkeys which are in dtab table and are not in idtab table.

Please help

Well, Tom how can you combine the above two ?

Hi Tom,

Welcome back!

I have the following queries:

SELECT Z.location_nme,A.Candidate_Name, A.IVR
FROM IVR as A , gctlocation as Z
WHERE A.gctlocation_srl = Z.srl;

location_nme | candidate_name | ivr
--------------+----------------+-----
NT | Bob Benger | 1
NS | Bob Benger | 1
BC | Bob Benger | 1
NS | Bob Eisen | 1
ON | Bob Langer | 1
NT | Bob McNamara | 1
(6 rows)


SELECT Z.location_nme,B.Candidate_Name, B.WEB
FROM web as B , gctlocation as Z
WHERE B.gctlocation_srl = Z.srl;
location_nme | candidate_name | web
--------------+----------------+-----
NB | Bob Redmond | 2
NS | Bob Benger | 2
ON | Bob Langer | 4
YK | Bob Burke | 1
NT | Bob Cobban | 1
(5 rows)

I want to merge them and have a result set as follow:

location_nme | candidate_name | ivr |web
--------------+----------------+-----+-----
NT | Bob Benger | 1 |
NS | Bob Benger | 1 |2
BC | Bob Benger | 1 |
NS | Bob Eisen | 1 |
ON | Bob Langer | 1 |4
NT | Bob McNamara | 1 |
NB | Bob Redmond | |2
YK | Bob Burke | 1
NT | Bob Cobban | 1
..............
.............
The order, doesn't mattar. Just showing in one line for the same 'location_nme' and 'candidate_nme' is important. (e.g NS , Bob Benger )

How can I do this? Could you please help me on this?

Thank you so much for your help.
- Arash

P.S. I tried to use in-line views or outer join, but everytime I missed some records!

Hi Tom,

I don't know why this doesn't work:

select location_nme, candidate_name, max(ivr), max(web), max(web)+max(ivr) as total
from ( SELECT Z.location_nme,A.Candidate_Name, A.IVR, to_number(null) web
FROM IVR as A , gctlocation as Z
WHERE A.gctlocation_srl = Z.srl
union all
SELECT Z.location_nme,B.Candidate_Name, to_number(null), B.WEB
FROM web as B , gctlocation as Z
WHERE B.gctlocation_srl = Z.srl )
group by location_name, candidate_name;


It may be because of 'NULL' values in each subqueries.

Please let me know how I can do this?

Thank you again,
- Arash

Tom,

I used NVL and it fixed the problem.

Thanks

Tom,

Here I am alone in the office at 10:30pm, missing the ALCS Game 7, trying to correct some truly horrible code and save a project. I'm working day and night so that maybe I won't have to be away from my wife again next week. Once again, I've found the answer to my problem here at asktom. It is in these moments, in the clutch, that your site is most indispensible and that you are most appreciated. Thank you.

hello sir
i need to know how to print character mode reports on windows xp please send the solution as soon as posible
thanking you
siddartha

hai tom,

I have a front end form that downloads data form a excel file. the data in the file has a number of client code.
and i have used a oracle stored procedure that does some process with those client code. each client code is of 15 characters. at every download i get a minimum of 500 client code.

is there any way that i can send all the client code to that procedure as an arguement at one shot. i dont want to call the procedure in a loop as we are using a three tire consept.

i tryed to pad the client code into a string and send it as an arguement. but most of the time it exceeds the size of varchar2 and i get error.

can you please help me with this.

Thank you

karthick.

ok let me put it in this way,

the fornt end language can store x characters in a vairable. i need y such variables to store.this y is unknown and determined only at run time. so is it possible to pass that y variables to my stored procedure as arguement.y can be 1,2,3....

Hi Tom

How can I use order by in procedure to return ref cursor.
This one has no effect. I used string also as cloumn name.

scott@ORA9I> CREATE OR REPLACE PROCEDURE P(p_list NUMBER, p_rc OUT sys_refcursor)
2 AS
3 BEGIN
4 OPEN p_rc FOR SELECT EMPNO, ENAME, JOB MGR FROM Emp ORDER BY p_list;
5 END;
6 /

Procedure created.

scott@ORA9I> var cur refcursor
scott@ORA9I> set autoprint on
scott@ORA9I> exec p(2, :cur)

PL/SQL procedure successfully completed.


EMPNO ENAME JOB MGR
---------- ---------- --------- ----------
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7566 JONES MANAGER 7839
7654 MARTIN SALESMAN 7698
7698 BLAKE MANAGER 7839
7782 CLARK MANAGER 7839
7788 SCOTT ANALYST 7566
7839 KING PRESIDENT
7844 TURNER SALESMAN 7698
7876 ADAMS CLERK 7788
7900 JAMES CLERK 7698
7902 FORD ANALYST 7566
7934 MILLER CLERK 7782

14 rows selected.


scott@ORA9I>

Is there any way this proc can accept order by as IN parameter and do the order by.

Thanks and regards.

Hi Tom

If I use:

OPEN p_rc FOR SELECT empno, ename, job FROM Emp ORDER BY DECODE(p_list, 2, ename, 3, job);

then it works:

scott@ORA9I> exec p(2, :rc)

PL/SQL procedure successfully completed.


EMPNO ENAME JOB
---------- ---------- ---------
7876 ADAMS CLERK
7499 ALLEN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7902 FORD ANALYST
7900 JAMES CLERK
7566 JONES MANAGER
7839 KING PRESIDENT
7654 MARTIN SALESMAN
7934 MILLER CLERK
7788 SCOTT ANALYST
7369 SMITH CLERK
7844 TURNER SALESMAN
7521 WARD SALESMAN

14 rows selected.

scott@ORA9I> exec p(3, :rc)

PL/SQL procedure successfully completed.


EMPNO ENAME JOB
---------- ---------- ---------
7788 SCOTT ANALYST
7902 FORD ANALYST
7369 SMITH CLERK
7876 ADAMS CLERK
7934 MILLER CLERK
7900 JAMES CLERK
7566 JONES MANAGER
7782 CLARK MANAGER
7698 BLAKE MANAGER
7839 KING PRESIDENT
7499 ALLEN SALESMAN
7654 MARTIN SALESMAN
7844 TURNER SALESMAN
7521 WARD SALESMAN

14 rows selected.

scott@ORA9I>

IS there any better way and can take more then one order by in one parameter?

Thanks for help.
Regards

Hi Tom,

How to get data Asc or Desc dynamically? (Order by column is fixed)

I tried this query:

select * from t order by qty || (select decode(:x, 'A', 'Asc', 'Desc' from dual);

But its not giving the data as desired or not throwing any error.

Can I do this?

Sincerely
Vaishnavi

I am facing some problem, while testing my code on development database. After some code enahncement, I want to match the time with earlier run, but it is unmatchable because of SGA. Running the same SQL 2nd or 3rd time takes, small time. So I am unable to perform real testing. How can I do that.

Hi Tom
I am currently developing a report in Crystal connected to Maximo Database.
This report is suppose to generate all those items that where neither been issued nor received in another word no "transactions"

The problem is that I do have "last issue date" in my inventory master table. So I can say if isnull (inventory. last issuedate) but I can not follow that with and isnull (inventory. last receiveddate) then "non moving"
Because the last received date is not available in the inventory master table

How can I write an sql statement or formula to get the "last received date" from the inventory transactions table

no des bal cos tot lstissudte lstrecdte moving status

1 Baring 100 100 1,0000 ------- ----- non moving

Like I said the last issue date will show blank but last received date is not available in the master table .. if I can get the last received date to show NULL then the item is "a non moving item"



Thanks

Hi Tom,

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?

Thanks a million.

create table test5(
begdate date,
enddate date,
location varchar2(10),
status varchar2(10),
price varchar2(50) )
/

insert into test5 values('05-jan-05' , '31-jan-05' ,   'Expedite', 40000,   'A price of $6.89 flat rate')
/
insert into test5 values('01-feb-05' , '28-feb-05',     'Expedite', 1000 ,   'A price of $7.87 flat rate' )
/
insert into test5 values('05-mar-05' , '30-mar-05' ,   'Expedite', 40000,   'A price of $6.89 flat rate')
/

insert into test5 values ('01-dec-04', '31-dec-04', 'expedite',40000,'A price of $6.89 flat 

rate')

SQL> SELECT * FROM TEST5;

BEGDATE   ENDDATE   LOCATION   STATUS     PRICE
--------- --------- ---------- ---------- --------------------------------------------------
05-JAN-05 31-JAN-05 EXPEDITE   40000      A price of $6.89 flat rate
01-FEB-05 28-FEB-05 EXPEDITE   1000       A price of $7.87 flat rate
05-MAR-05 30-MAR-05 EXPEDITE   40000      A price of $6.89 flat rate
01-DEC-04 31-DEC-04 EXPEDITE   40000      A price of $6.89 flat rate

SQL> select min(begdate), max(enddate) , location, status, price
  2  from test5
  3  group by LOCATION ,STATUS,PRICE
  4  /

MIN(BEGDA MAX(ENDDA LOCATION   STATUS     PRICE
--------- --------- ---------- ---------- --------------------------------------------------
01-FEB-05 28-FEB-05 EXPEDITE   1000       A price of $7.87 flat rate
01-DEC-04 30-MAR-05 EXPEDITE   40000      A price of $6.89 flat rate

01-DEC-04 30-MAR-05 EXPEDITE   40000      A price of $6.89 flat rate row of out put is 

conveying wrong message, it is as if febraury is also for the price of 'A price of $6.89 flat 

rate' which is not the case

The output I'm looking for is

01-DEC-04 31-jan-05 EXPEDITE   40000      A price of $6.89 flat rate
01-FEB-05 28-FEB-05 EXPEDITE   1000       A price of $7.87 flat rate
05-MAR-05 30-MAR-05 EXPEDITE   40000      A price of $6.89 flat rate


Please help me write this query. Is this possible at all.
 

same concept as outlined here:

you might have to tweak for nulls of location/status/price is NULLABLE.


ops$tkyte@ORA9IR2> select min(begdate), max(enddate), location, status, price
  2    from (
  3  select begdate, enddate,
  4         location, status, price,
  5         max(grp) over (order by begdate) grp2
  6    from (
  7  select t.*,
  8         case when lag(location) over (order by begdate) <> location
  9                or lag(status) over(order by begdate) <> status
 10                or lag(price) over (order by begdate) <> price
 11                or row_number() over (order by begdate) = 1
 12              then row_number() over (order by begdate)
 13          end grp
 14    from t
 15         )
 16         )
 17   group by location, status, price, grp2
 18   order by 1;
 
MIN(BEGDA MAX(ENDDA LOCATION   STATUS     PRICE
--------- --------- ---------- ---------- ---------------------------
01-DEC-04 31-JAN-05 Expedite   40000      A price of $6.89 flat rate
01-FEB-05 28-FEB-05 Expedite   1000       A price of $7.87 flat rate
05-MAR-05 30-MAR-05 Expedite   40000      A price of $6.89 flat rate
 

Hi Tom,

Sorry for my cause of confusion. This time I tried to make my question more understandable and readable. And I hope that I posted my question to the right thread this time.



CREATE TABLE DW_TBL_DIM_PRODUCT
(
SRL NUMBER(10) NOT NULL,
BRAND_DESC VARCHAR2(50)
)
/

Insert into DW_TBL_DIM_PRODUCT values (1, 'prd1');
Insert into DW_TBL_DIM_PRODUCT values (2, 'prd2');

CREATE TABLE DW_TBL_DIM_MARKET
(
SRL NUMBER(10) NOT NULL,
MARKET_DESC VARCHAR2(50),
MARKET_PAR_SRL VARCHAR2(30),
MARKET_PAR_DESC VARCHAR2(50),
MARKET_CLASS NUMBER(1),
MARKET_LEVEL NUMBER(1)
);




Insert into DW_TBL_DIM_MARKET values (1, 'PPPAB', null, null, 1, 0);
Insert into DW_TBL_DIM_MARKET values (6, 'PPA', 1, 'PPPAB', 1, 1);
Insert into DW_TBL_DIM_MARKET values (7, 'PPB', 1, 'PPPAB', 1, 1);
Insert into DW_TBL_DIM_MARKET values (12, 'PA', 6, 'PPA', 1, 2);
Insert into DW_TBL_DIM_MARKET values (13, 'PB', 7, 'PPB', 1, 2);
Insert into DW_TBL_DIM_MARKET values (20, 'A', 12, 'PA', 1, 3);
Insert into DW_TBL_DIM_MARKET values (21, 'B', 13, 'PB', 1, 3);


Insert into DW_TBL_DIM_MARKET values (2, 'PPEF', null, null, 2, 0);
Insert into DW_TBL_DIM_MARKET values (8, 'PEF', 2, 'PPEF', 1, 1);
Insert into DW_TBL_DIM_MARKET values (14, 'E', 8, 'PEF', 1, 2);
Insert into DW_TBL_DIM_MARKET values (15, 'F', 8, 'PEF', 1, 2);


CREATE TABLE DW_TBL_PRODUCT_MARKET
(
FK_DIM_PRODUCT NUMBER(10) NOT NULL,
FK_DIM_MARKET NUMBER(10) NOT NULL
);

Insert into DW_TBL_PRODUCT_MARKET values (1,20);
Insert into DW_TBL_PRODUCT_MARKET values (2,21);

SELECT
LPAD(' ',10*(LEVEL-1)) || market_desc market_tree
FROM dw_tbl_dim_market
START WITH market_desc = 'PPPAB'
CONNECT BY PRIOR srl = market_par_srl;

MARKET_TREE
----------------------------------------------
PPPAB
PPA
PA
A
PPB
PB
B
7 rows selected.

*Now my question is how I can show the result in two rows than 7 rows including the products like this:


1 PPPAB PPA PA A prd1
1 PPPAB PPB PB B prd2


Many thanks for your time,
Elahe

Hi Sir,

Just have a question regarding previous posted question on Feb. 4th.

Is it really doable by just using SQL? Or have to use reporting tools?

Thanks

ops$tkyte@ORA9IR2> SELECT
  2      sys_connect_by_path(market_desc,' ' )
  3  FROM dw_tbl_dim_market     y
  4  where not exists ( select * from dw_tbl_dim_market x where x.market_par_srl = y.srl)
  5  START WITH market_desc = 'PPPAB'
  6  CONNECT BY PRIOR srl = market_par_srl
  7  /
 
SYS_CONNECT_BY_PATH(MARKET_DESC,'')
-------------------------------------------------------------------------------
 PPPAB PPA PA A
 PPPAB PPB PB B


start with that, not really knowing how the other stuff comes "along"....


also, in 10g, the where becomes "where connect_by_isleaf = 1" instead of a where exists. 

Tom,

If could find some time for this sql query for the results required:

create table TBL1(ty VARCHAR2(2),ID VARCHAR2(2),MID VARCHAR2(3),REM NUMBER);


insert into TBL1 values('S','C','ABC',1);
insert into TBL1 values('B','C','ABC',1);
insert into TBL1 values('S','L','BCD',1);
insert into TBL1 values('B','C','BCD',1);
insert into TBL1 values('S','D','CDE',1);
insert into TBL1 values('B','C','CDE',1);
insert into TBL1 values('S','C','DEF',1);
insert into TBL1 values('B','C','DEF',1);
insert into TBL1 values('S','L','EFG',1);
insert into TBL1 values('B','C','EFG',1);


SQL> SELECT * FROM TBL1;

TY ID MID        REM
-- -- --- ----------
S  C  ABC          1
B  C  ABC          1
S  L  BCD          1
B  C  BCD          1
S  D  CDE          1
B  C  CDE          1
S  C  DEF          1
B  C  DEF          1
S  L  EFG          1
B  C  EFG          1

RESULT REQUIRED WITH THE FOLLOWING CONDITIONS:

a)The result should not include ID='C' if it's TY='B' and its' contra ID='D'.
b)And the result should also not include where ID='C' and its contra ID='L'.

The contra id determined by the column MID. And every TY with value 'S' will 
have a corresponding TY with value 'B'. MID values' are same for each contra item.


RESULT REQUIRD FOR THE DATA ABOVE IS:

TY   ID  MID  REM

S     C  ABC    1
B     C  ABC    1
S     L  BCD    1
S     D  CDE    1
S     C  DEF    1
B     C  DEF    1
S     L  EFG    1

My attempt:

I could get only a part of this, but when I add the 'C' value for ID column, the query blows :

SELECT A1.MID,A1.TY,A1.ID,A1.REM
FROM
(
SELECT MID,TY,ID,REM FROM TBL1 
WHERE 
ID IN('L') AND REM=1 )A1,
(SELECT MID FROM TBL1 WHERE ID IN'C' AND REM=1) A2
WHERE A1.MID=A2.MID

MID TY ID        REM
--- -- -- ----------
BCD S  L           1
EFG S  L           1
 

I need to make (trunc(sydate)-90) with field DATA_ABERTURA, as I must proceed?
SELECT DISTINCT DECODE(A.DIA_ABERTURA_DOC, '-1', ' ',
LPAD(A.DIA_ABERTURA_DOC, 2, '00') || '/' || LPAD(A.MES_ABERTURA_DOC, 2, '00') || '/' || A.ANO_ABERTURA_DOC) DATA_ABERTURA,
FROM BILHETE_ATIVIDADE A
,SGE_BA_SERV_ASSOC B
,ACIONAMENTO C
,ACIO_TECNICO_MICRO D
WHERE B.NUM_DOCUMENTO = A.NUM_DOCUMENTO
AND C.NUM_DOCUMENTO(+) = B.NUM_DOCUMENTO
AND D.NUM_DOCUMENTO(+) = C.NUM_DOCUMENTO
AND D.NUM_ACIONAMENTO(+) = C.NUM_ACIONAMENTO

Grateful

Tom,

Sorry for not being clear with the test case I gave above. The requirement is :

The query needs to do this:

a. Give all the records where ID='C' and REM=1 and also give it's contra record also ID= 'C' and REM=1 where MID for this pair of records is the same.
b. Give all the records where ID='L' and REM=1  but do not  give  its' contra record that is ID='C' and key is MID column value.
c. Give all the records where ID='D' and REM=1 but do not give its' contra record that is ID='C' (key is MID col value)

SQL> SELECT * FROM TBL1;

TY ID MID        REM
-- -- --- ----------
S  C  ABC          1
B  C  ABC          1
S  L  BCD          1
B  C  BCD          1
S  D  CDE          1
B  C  CDE          1
S  C  DEF          1
B  C  DEF          1
S  L  EFG          1
B  C  EFG          1




Expected REsult: (for the data above)

TY   ID  MID  REM

S     C  ABC    1
B     C  ABC    1
S     L  BCD    1
S     D  CDE    1
S     C  DEF    1
B     C  DEF    1
S     L  EFG    1

Thanks again for the time. 

I like to query v$database for name of the database and
dba_users to get the username.

If the username exists, the query must return the name
of the database and the username. If the username does
not exist, the query will return no rows

This is to cycle through all the ORACLE_SID from
oratab and find if username exists in any of the
databases in the server

Is it possible to construct a query like this

Tom,

I need to make (trunc(sydate)-90) with field DATA_ABERTURA, as I must proceed when the date (day, month and year) is in separate fields?
SELECT DISTINCT DECODE (A.DAY_DOC, '-1', ' ',
LPAD (A.DAY_DOC, 2, '00') || '/' || LPAD(A.MONTH_DOC, 2, '00') || '/' || A.YEAR_DOC) DATE_ OPENING
FROM BILHETE_ATIVIDADE A
,SGE_BA_SERV_ASSOC B
,ACIONAMENTO C
,ACIO_TECNICO_MICRO D
WHERE B.NUM_DOCUMENTO = A.NUM_DOCUMENTO
AND C.NUM_DOCUMENTO(+) = B.NUM_DOCUMENTO
AND D.NUM_DOCUMENTO(+) = C.NUM_DOCUMENTO
AND D.NUM_ACIONAMENTO(+) = C.NUM_ACIONAMENTO

I need its aid with urgency
Grateful.

The following query works without syntax error in SQL
select username,
CASE length(username)
when '' then ''
else (select name from v$database)
END
from dba_users where username = 'TEST19'

When used within unix shell script I get the error

1 select username,
2 CASE username
3 when '' then ''
4 else (select name from v$database)
5 END
6* from dba_users where username = 'TEST'
when '' then ''
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

when the username does not exist in the database.

Your suggested query,

select username, (select name from v$database)
from dba_users where username = :x

works

Thanks.

As for my subsequent posting, I DID use \$
for v$database in the shell script. Not sure why
I got the error from the shell script. I'll look
further into it

Thanks

Hi Tom,
I have a requirment where I've to break all rows in table in 10 batches.We have a column batch_number in the table.
For e.g If I have 10 rows in a table, 1st row would have batch_number 1,2nd row would have batch_number 2 etc...If I have 1000 rows in a table then first 100 rows would in batch 1,next 200 rows would be in batch 2 etc...
How can I do this? Can I update this in a single udpdate statement?

Thanks

I'd rather break the table into 10 pieces:

set echo off
                                                                                              
set verify off
define TNAME=&1
define CHUNKS=&2
                                                                                              
                                                                                              
select grp,
       dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
       dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
  from (
select distinct grp,
       first_value(relative_fno) over (partition by grp order by relative_fno, block_id
                   rows between unbounded preceding and unbounded following) lo_fno,
       first_value(block_id    ) over (partition by grp order by relative_fno, block_id
                   rows between unbounded preceding and unbounded following) lo_block,
       last_value(relative_fno) over (partition by grp order by relative_fno, block_id
                   rows between unbounded preceding and unbounded following) hi_fno,
       last_value(block_id+blocks-1) over (partition by grp order by relative_fno, block_id
                   rows between unbounded preceding and unbounded following) hi_block,
       sum(blocks) over (partition by grp) sum_blocks
  from (
select relative_fno,
       block_id,
       blocks,
       trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
              (sum(blocks) over ()/&CHUNKS) ) grp
  from dba_extents
 where segment_name = upper('&TNAME')
   and owner = user order by block_id
       )
       ),
       (select data_object_id from user_objects where object_name = upper('&TNAME') )
/

that'll give you 10 non-overlapping rowid ranges that cover the table (feed those into your batch process)


short of that, you'd be using "ntile" and merge.

ops$tkyte@ORA10G> create table t as select * from all_users;
 
Table created.
 
ops$tkyte@ORA10G> alter table t add nt number;
 
Table altered.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> merge into t
  2  using ( select rowid rid, ntile(10) over (order by username) nt from t ) t2
  3  on ( t.rowid = t2.rid )
  4  when matched then update set nt = t2.nt;
 
35 rows merged.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t order by nt;
 
USERNAME                          USER_ID CREATED           NT
------------------------------ ---------- --------- ----------
BI                                     63 19-DEC-04          1
ANONYMOUS                              43 10-AUG-04          1
CTXSYS                                 40 10-AUG-04          1
DBSNMP                                 22 10-AUG-04          1
DEMO                                   75 02-JAN-05          2
DMSYS                                  37 10-AUG-04          2
DIP                                    19 10-AUG-04          2
EXFSYS                                 32 10-AUG-04          2
LEAST_PRIVS                            68 02-JAN-05          3
HR                                     58 19-DEC-04          3
IX                                     60 19-DEC-04          3
MDDATA                                 49 10-AUG-04          3
OE                                     59 19-DEC-04          4
MGMT_VIEW                              56 10-AUG-04          4
MDSYS                                  36 10-AUG-04          4
OLAPSYS                                46 10-AUG-04          4
OPS$TKYTE                              80 17-JAN-05          5
ORDPLUGINS                             34 10-AUG-04          5
OUTLN                                  11 10-AUG-04          5
ORDSYS                                 33 10-AUG-04          5
PERFSTAT                               82 28-JAN-05          6
PM                                     62 19-DEC-04          6
SCOTT                                  57 10-AUG-04          6
SH                                     61 19-DEC-04          7
SYS                                     0 10-AUG-04          7
SI_INFORMTN_SCHEMA                     35 10-AUG-04          7
TEST                                   65 23-DEC-04          8
SYSMAN                                 54 10-AUG-04          8
SYSTEM                                  5 10-AUG-04          8
WKPROXY                                51 10-AUG-04          9
WKSYS                                  50 10-AUG-04          9
WK_TEST                                53 10-AUG-04          9
WSMGMT                                 81 28-JAN-05         10
XDB                                    42 10-AUG-04         10
WMSYS                                  23 10-AUG-04         10
 
35 rows selected.


(in 9i, you'd need to have a "when not matched then insert (nt) values ( null)" but it would basically be a big "no-operation" since all of the rows would in fact match....)

 

Excellent..Thanks tom.

Hi Tom,
We are using oracle 9i
You told
"in 9i, you'd need to have a "when not matched then insert (nt) values ( null)"
but it would basically be a big "no-operation" since all of the rows would in
fact match....)"

I didn't got that part..Could you please explain?

Thanks

Hi Tom,
I got it and implemented Merge statement to break the table into 10 batches..
Thanks a lot for the help

What I would like to know is as I use ((SYSDATE)-90) when the composition of the date this in different columns (day, month and year)

Hi Tom,
Please have a look at this query.

SQL> create table t(x int,y int)
  2  /

Table created.

SQL> insert into t select rownum,rownum+1 from cat where rownum 

<= 5
  2  /

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from t
  2  /

         X          Y                                             

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

              
         1          2                                             

              
         2          3                                             

              
         3          4                                             

              
         4          5                                             

              
         5          6                                             

              

SQL> select x,y,x+y from t
  2  union all
  3  select sum(x),sum(y),sum(x+y) from t
  4  /

         X          Y        X+Y                                  

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

              
         1          2          3                                  

              
         2          3          5                                  

              
         3          4          7                                  

              
         4          5          9                                  

              
         5          6         11                                  

              
        15         20         35                                  

              

6 rows selected.

Can this query be put in any other way??

I would like to eliminate union all from this query.
I even tried 
SQL > select decode(grouping(..

But it is not working properly.Can you provide
some other way??

 

ops$tkyte@ORA9IR2> select decode( grouping(rowid), 0, null, 1, 'the end' ) label,
  2         sum(x), sum(y), sum(x+y)
  3   from t
  4  group by rollup(rowid);
 
LABEL       SUM(X)     SUM(Y)   SUM(X+Y)
------- ---------- ---------- ----------
                 1          2          3
                 2          3          5
                 3          4          7
                 4          5          9
                 5          6         11
the end         15         20         35
 
6 rows selected.
 

Tom,

Excuse me for not replying immediately to your followup above with title "Efficient way to get counts"

Your followup was:

"need more info, is this table modified during the day or is this a warehouse.

is this example cut way down and there are 500 columns
or is this it.

is c1, c2, flag always involved,
or was that just a side effect of your example..

before anyone can suggest the "best way", details about how the data is used
need to be known"

The case example repeated :

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;


Here is the data spread asked for:

No. of columns used by the query : 25
Is c1,c2,flag always involved : yes and a combination of the 25 columns.
Is this table modified during the day or is this a warehouse: This table is modified regularly during
the day. But the query that I have asked for is run only for reporting purposes.


I went with the sum(case.. ) method after creating bitmap indexes for all the columns used in the query where the
distinct values were actually less than 5 or 6. And normal indexes for other columns where the
distinct values were actually close to 60% of the no of rows.

select sum(case when c1='abc' and c2='bcd' and flag=0 then 1 else 0 end) r1,
sum(case when c1='abc' and c2='bcd' and c3='cde' and flag=1 then 1 else 0 end)r2
from temp_del

Kindly advise if this is a preferred solution for 10 million rows table or please suggest something better.

Having a very few values for the columns, that's what I thought of. Does this affect performance in the normal running of the application and in the queries for reporting purposes?

Thanks for the time.

Yes. A few columns are updated quite frequently.

Tom,

I am not sure if the folowing logic can be done by a single sql.

I have two tables

receipts
store_id, order_id, prod_id, rec_date, rec_qty
-------------------------------------------------
1003 10001 2001, 3/5/2005, 3
1003 10002 2001, 3/8/2005, 1
1004 10003


sales
sales_date, store_id, prod_id, sales_qty, ...
----------------------------------------------
3/8/05, 1003, 2001, 1
3/10/05, 1003, 2001, 2
3/11/05, 1003, 2001, 1
3/12/05, 1003, 2001, 1
.....

I want to create a received_sales match report

store_id, order_id, prod_id, rec_date, rec_qty, sale_qty
1003 10001 2001, 3/5/2005, 2 2
1003 10002 2001, 3/8/2005, 1 1

Matching rule:
Given a receipt of product, if there is a sale for that type of the product within 14 day of receipt_date, then we conside a sale match.

Thanks in advanced!

Steve

Tom,
I need to run below script which displays only three columns.

EMPLOYEE_NAME
V.DEPT_NO DEPT_NO
V.PAY_LOCATION PAY_LOCATION

I got those three script from Our current Oracle reports.
emp_no from the first script is PK and other two columns has this column (emp_no) FK.
here are three scripts, how will I join below scripts and get values for only EMPLOYEE_NAME, V.DEPT_NO DEPT_NO
& V.PAY_LOCATION PAY_LOCATION columns.


SELECT V.LAST_NAME || ', ' || V.FIRST_NAME || ' ' || V.MIDDLE_INITIAL
EMPLOYEE_NAME
,E.home_phone PHONE
,E.home_street STREET
,E.home_city CITY
,E.home_zip1 ZIP1
,E.home_zip2 ZIP2
,nvl(E.bilingual_code, 'Not Applicable') BC
,nvl(V.specialty, 'Not Applicable') SPECIALTY
,nvl(V.sub_specialty,'Not Applicable') SBSP
,E.BILINGUAL_REF_CD BRC
,V.EMP_NO EMP_NO
,V.DEPT_NO DEPT_NO
,C.DEPT_TITLE DEPT_TITLE
,V.PAY_LOCATION PAY_LOCATION
,V.LAYOFF_SENIORITY_DATE LAYOFF_SENIORITY_DATE
,V.TIME_IN_GRADE TIME_IN_GRADE
,V.PAYROLL_ITEM_NO PAYROLL_ITEM_NO
,V.ITEM_LETTER ITEM_LETTER
,V.CLASS_TITLE CLASS_TITLE
,DECODE(V.REPRESENTATION_STATUS,'Y','REPRESENTED','NON-REPRESENTED')
REPRESENTATION_STATUS
,DECODE(V.PROBATION_STATUS,'1','FIRST','2','SUBSEQUENT','NONE')
PROBATION_STATUS
,DECODE(V.IMPROVEMENT_NEEDED_END_DATE,NULL,'NONE',
TO_CHAR(V.IMPROVEMENT_NEEDED_END_DATE,'MM/DD/YY')) IMPROVEMENT_NEEDED_END_DATE
FROM EMPINFO_VIEW V,
DEPARTMENTS C,
EMPLOYEES E
WHERE V.PRIOR_ITEM_INDICATOR IS NULL
AND V.JOB_CODE = 'A'
AND (V.EMP_NO = :EMP_PARM OR :EMP_PARM IS NULL)
AND (V.DEPT_NO = :DEPT_PARM OR :DEPT_PARM IS NULL)
AND (V.BUDGETED_GROUP_ID = :GROUP_PARM OR :GROUP_PARM IS NULL)
AND (C.DEPT_NO = :DEPT_PARM OR :DEPT_PARM IS NULL)
and (v.payroll_item_no = :ITEM_PARM or :ITEM_PARM is null)
AND C.DEPT_NO = V.DEPT_NO
and V.emp_no = E.emp_no
ORDER BY V.DEPT_NO
,V.PAY_LOCATION
,V.LAST_NAME


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

SELECT EMPITM.PAYROLL_ITEM_NO PAYROLL_ITEM_NO2
,ITEM.CLASS_TITLE CLASS_TITLE2
,EMPITM.ITEM_LETTER ITEM_LETTER2
,EMPITM.EMP_NO EMP_NO2
FROM EMPLOYEE_ITEMS EMPITM
,ITEMS ITEM
WHERE ITEM.ITEM_NO = EMPITM.PAYROLL_ITEM_NO
AND EMPITM.PRIOR_ITEM_INDICATOR = 'Y'
ORDER BY ITEM.MAXIMUM_ANNUAL_SALARY DESC

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

SELECT EMP_NO EMP_NO3,
PRIORITY_GEO_A PGA,
PRIORITY_GEO_B PGB,
PRIORITY_GEO_C PGC
FROM EMPLOYEES

Regards,
Dawar

Dear Tom,

The following query takes around 45 seconds...All tables have been analyzed using dbms_stats. Indexes are in place.  The view called in this query runs very fast and the response for the view is less than a second. Can you please give inputs to improve the performance?  Are joins bad here?
Should we rewrite the query?

Regards


=======================
SQL> SELECT  count(1) noofrecs FROM
OPAY.OC_CHEQUE_INTERFACE OCI,
OPAY.FILE_MST_PAY FIM,
OPAY.OC_CHEQUE_DETAIL OCD,
OPMAY.CLIENTALL VW_CLA
WHERE
OCD.COD_TXN_STATUS = 'ISSUED' and
OCI.AMT_CREDIT BETWEEN
0 AND 999999999999.99 and
OCD.ISSUE_DATE BETWEEN
TO_DATE('21-08-2004','DD-MM-YY') AND
TO_DATE ('29-08-2004','DD-MM-YY')
AND oci.cod_cust LIKE
DECODE ('VOLTEST', '', '%', 'VOLTEST')
AND vw_cla.dividend_number
LIKE DECODE ('VOLTESTDIV', '', '%','VOLTESTDIV')
AND
VW_CLA.COD_STOCK LIKE DECODE ('', '', '%', '') AND
TO_DATE (vw_cla.dat_payment, 'DD-MM-YY')
BETWEEN TO_DATE
('08-06-2006','DD-MM-YYYY') AND
TO_DATE ('15-06-2006','DD-MM-YYYY') AND
OCI.COD_FILEID = FIM.COD_FILEID AND
OCI.TXN_ID = OCD.TXN_ID AND
vw_cla.cycle_id = fim.cycle_id
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
SQL> /

Elapsed: 00:00:43.65
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=759 Card=1 Bytes=133
          )

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=759 Card=1 Bytes=133)
   3    2       HASH JOIN (Cost=757 Card=1 Bytes=109)
   4    3         NESTED LOOPS (Cost=11 Card=84 Bytes=5880)
   5    4           VIEW OF 'CLIENTALL' (Cost=8 Card=2 Bytes=112)
   6    5             SORT (UNIQUE) (Cost=8 Card=2 Bytes=122)
   7    6               UNION-ALL
   8    7                 NESTED LOOPS (Cost=2 Card=1 Bytes=57)
   9    8                   TABLE ACCESS (FULL) OF 'DVD_CYCLE_MST_PAY'
           (Cost=1 Card=1 Bytes=29)

  10    8                   TABLE ACCESS (BY INDEX ROWID) OF 'ORBICASH
          _CLIENTMST' (Cost=1 Card=119 Bytes=3332)

  11   10                     INDEX (UNIQUE SCAN) OF 'SYS_C0014447' (U
          NIQUE)
  12    7                 NESTED LOOPS (Cost=2 Card=1 Bytes=65)
  13   12                   TABLE ACCESS (FULL) OF 'DVD_CYCLE_MST_PAY'
           (Cost=1 Card=1 Bytes=29)

  14   12                   TABLE ACCESS (BY INDEX ROWID) OF 'COUNTER_
          MST_PAY' (Cost=1 Card=14 Bytes=504)

  15   14                     INDEX (UNIQUE SCAN) OF 'SYS_C0014648' (U
          NIQUE)

  16    4           INDEX (RANGE SCAN) OF 'PERF2' (NON-UNIQUE) (Cost=2
           Card=4767 Bytes=66738)

  17    3         TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_INTERFAC
          E' (Cost=745 Card=30 Bytes=1170)

  18   17           INDEX (RANGE SCAN) OF 'IDX_OC_CHEQUE_INT_CODCUST'
          (NON-UNIQUE) (Cost=36 Card=30)

  19    2       TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_DETAIL' (C
          ost=2 Card=49 Bytes=1176)

  20   19         INDEX (UNIQUE SCAN) OF 'SYS_C0015224' (UNIQUE) (Cost
    =1 Card=49)

 Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
    1693075  consistent gets
      47287  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed


============================================

 

Tom,

Thanks for the inputs. 

Please clarify on the following:

1. I am under the belief that when indexes are in place and the tables stand analyzed using dbms_stats, CBO will decide whether or not to use the index.This being the case how the index would be bad during select operations.

2. As suggested, the query was modified to compare the date column to  to_date of string. But the consistent gets continue to be the same as shown below. Is there any other part of the query to look at and modfiy?

Regards.

==============================================
SQL> SELECT  count(1) noofrecs FROM
OPAY.OC_CHEQUE_INTERFACE OCI,
OPAY.FILE_MST_PAY FIM,
OPAY.OC_CHEQUE_DETAIL OCD,
OPMAY.CLIENTALL VW_CLA
WHERE
OCD.COD_TXN_STATUS = 'ISSUED' and
OCI.AMT_CREDIT BETWEEN
0 AND 999999999999.99 and
OCD.ISSUE_DATE BETWEEN
TO_DATE('21-08-2004','DD-MM-YYYY') AND
TO_DATE ('29-08-2004','DD-MM-YYYY')
AND oci.cod_cust LIKE
DECODE ('VOLTEST', '', '%', 'VOLTEST')
AND vw_cla.dividend_number
LIKE DECODE ('VOLTESTDIV', '', '%','VOLTESTDIV')
AND
VW_CLA.COD_STOCK LIKE DECODE ('', '', '%', '') AND
vw_cla.dat_payment
BETWEEN TO_DATE
('08-06-2006','DD-MM-YYYY') AND
TO_DATE ('15-06-2006','DD-MM-YYYY') AND
OCI.COD_FILEID = FIM.COD_FILEID AND
OCI.TXN_ID = OCD.TXN_ID AND
vw_cla.cycle_id = fim.cycle_id
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
SQL> /

Elapsed: 00:00:27.42

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=766 Card=1 Bytes=133
          )

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=766 Card=1 Bytes=133)
   3    2       HASH JOIN (Cost=764 Card=1 Bytes=109)
   4    3         HASH JOIN (Cost=18 Card=84 Bytes=5880)
   5    4           VIEW OF 'CLIENTALL' (Cost=8 Card=2 Bytes=112)
   6    5             SORT (UNIQUE) (Cost=8 Card=2 Bytes=122)
   7    6               UNION-ALL
   8    7                 NESTED LOOPS (Cost=2 Card=1 Bytes=57)
   9    8                   TABLE ACCESS (FULL) OF 'DVD_CYCLE_MST_PAY'
           (Cost=1 Card=1 Bytes=29)
 10    8                   TABLE ACCESS (BY INDEX ROWID) OF 'ORBICASH
          _CLIENTMST' (Cost=1 Card=119 Bytes=3332)

  11   10                     INDEX (UNIQUE SCAN) OF 'SYS_C0014447' (U
          NIQUE)

  12    7                 NESTED LOOPS (Cost=2 Card=1 Bytes=65)
  13   12                   TABLE ACCESS (FULL) OF 'DVD_CYCLE_MST_PAY'
           (Cost=1 Card=1 Bytes=29)

  14   12                   TABLE ACCESS (BY INDEX ROWID) OF 'COUNTER_
          MST_PAY' (Cost=1 Card=14 Bytes=504)

  15   14                     INDEX (UNIQUE SCAN) OF 'SYS_C0014648' (U
          NIQUE)

  16    4           TABLE ACCESS (FULL) OF 'FILE_MST_PAY' (Cost=9 Card
          =4767 Bytes=66738)

  17    3         TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_INTERFAC
          E' (Cost=745 Card=30 Bytes=1170)
18   17           INDEX (RANGE SCAN) OF 'IDX_OC_CHEQUE_INT_CODCUST'
          (NON-UNIQUE) (Cost=36 Card=30)

  19    2       TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_DETAIL' (C
          ost=2 Card=49 Bytes=1176)

  20   19         INDEX (UNIQUE SCAN) OF 'SYS_C0015224' (UNIQUE) (Cost
          =1 Card=49)

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


============================================= 

Hi Tom,

I have a quick question about my decode I'm trying to do but I don't know if what I'm doing is allowed or not. I'm doing:

select a.name,
DECODE (r.text,INSTR (r.text, 'XXX') != 0, r.id || SUBSTR (r.text, 4),r.text)

from address a, requirements r
where a.key = r.key

I get ORA-00907: missing right parenthesis, but as you see they match. I think it doesn't like something about that INSTR. Thank you.

Tom:

I have two tables:

SQL> desc phone_log;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOG_NO                                    NOT NULL NUMBER(10)
 CALLER_ID                                 NOT NULL NUMBER(10)
 CONTACT_PERSON                                     VARCHAR2(50)
 PHONE                                              VARCHAR2(25)
 DATE_RECEIVED                                      DATE
 DATE_RESOLVED                                      DATE
 PURPOSE                                            VARCHAR2(20)
 TOPIC                                              VARCHAR2(25)
 PRIORITY                                           VARCHAR2(6)
 INITIAL_OUTCOME                                    VARCHAR2(20)
 ISSUE                                              VARCHAR2(500)
 INITIAL_RESPONSE                                   VARCHAR2(500)
 SOURCE                                             VARCHAR2(10)

SQL> desc phone_followup
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOG_NO                                    NOT NULL NUMBER(10)
 FOLLOWUP_NO                               NOT NULL NUMBER(2)
 FOLLOWUP_DATE                                      DATE
 RESPONSE                                           VARCHAR2(500)
 OUTCOME                                            VARCHAR2(20)

The phone log table is a parent table to phone_followup.  However a parent record 
can exist without any children.  I am trying to query on pending records which are defined as:
Find all records in phone_log without any children where date_resolved is null
or if there are children records in phone followup then find the maximum followup number
where outcome is not resolved.

I wrote this but it does not work.  Is there a way to join both tables and find the 
maximum followup number record to join and also find records in parent that do not have children.

  1  select f.log_no,g.outcome from
  2  (
  3  select f.log_no,f.date_received,f.date_resolved from phone_log f,
  4  (
  5   select * from
  6  (
  7  select a.log_no,a.outcome,a.followup_no from phone_followup a,
  8  (select log_no,max(followup_no) followup_no from phone_followup group by log_no) b
  9  where a.log_no = b.log_no and a.followup_no=b.followup_no
 10  ) e
 11  ) g
 12  where f.log_no=g.log_no(+)
 13* )
SQL> /
select f.log_no,g.outcome from
                *
ERROR at line 1:
ORA-00904: "G"."OUTCOME": invalid identifier





  1   select a.log_no,a.date_received,a.date_resolved,b.outcome,b.followup_no from
  2  phone_log a,phone_followup b where
  3  a.log_no=b.log_no(+)
  4  --and (a.date_resolved is null or b.outcome <> 'Resolved') and
  5* --b.followup_no=(select max(followup_no) from phone_followup where log_no=a.log_no)
SQL> /

    LOG_NO DATE_RECE DATE_RESO OUTCOME              FOLLOWUP_NO
---------- --------- --------- -------------------- -----------
        39 22-SEP-04           Referred                       1
        39 22-SEP-04           Follow-up Needed               2
        39 22-SEP-04           Resolved                       3
        39 22-SEP-04                                          4
        39 22-SEP-04           Referred                       5
        41 20-NOV-04           Call Escalated                 1
        41 20-NOV-04           Referred                       2
        41 20-NOV-04           Resolved                       3
        41 20-NOV-04           Follow-up Needed               4
        38 11-NOV-04 11-DEC-04 Follow-up Needed               1
        38 11-NOV-04 11-DEC-04 Resolved                       2

    LOG_NO DATE_RECE DATE_RESO OUTCOME              FOLLOWUP_NO
---------- --------- --------- -------------------- -----------
        38 11-NOV-04 11-DEC-04 Referred                       3
        50           27-MAY-05

13 rows selected.


What I want are the records with max(followup_no) for each log_No and check if the outcome 
is not 'Resolved'.  For record 50 which does not have children I want to only check 
"date resolved" field.


Thank you,
 

create table orders
(order_id number(10),
address varchar2(100))
insert into orders values(1,'addr1');
insert into orders values(2,'addr2');
insert into orders values(3,'addr3');
insert into orders values(4,'addr4');
insert into orders values(5,'addr5');
insert into orders values(6,'addr6');

insert into order_items values(1,1,100,5);
insert into order_items values(2,1,200,15);
insert into order_items values(3,2,100,5);
insert into order_items values(4,2,200,15);
insert into order_items values(5,3,100,5);
insert into order_items values(6,4,300,4);
insert into order_items values(7,5,400,4);
insert into order_items values(7,6,400,4);


I need to select identical orders with the same set on inventory_Id and ordered items,

First set is orders 1, 2, and second set - orders 5,6.
order_id address count(order_id) Set#
1 addr1 2 1
2 addr2 2 1

5 addr3 2 2
6 addr5 2 2

Thanks in advance.

We have a lot of customers making the same orders,
and want to select such customers's orders as a bulk,
then create file and ship those orders thru fedx as a one set.
The number of orders in such bulk should be more than 3.
Let's say, if more than 2 orders have 10 ordered item 1,
20 items 2, this is a bulk #1. And so on.

Sorry, I need it and will try again.

let's say we have:
order1 contains item1 10 pieces, item2 15 pieces
order2 contains item1 10 pieces, item2 15 pieces
order3 contains item3 30 pieces
order4 contains item4 15 pieces, item1 20 pieces
order5 contains item1 8 pieces, item2 15 pieces
order6 contains item1 3 pieces, item2 15 pieces
order7 contains item3 30 pieces
order8 contains item3 30 pieces
order9 contains item7 10 pieces
Another words:
create table T
(item_id number(10), order_id number(10),
item varchar2(10), itm_ordered number(10))
insert into T values (1,1,'item1',10);
insert into T values (2,1,'item2',15);
insert into T values (3,2,'item1',10);
insert into T values (4,2,'item2',15);
insert into T values (5,3,'item3',30);
insert into T values (6,4,'item4',15);
insert into T values (7,4,'item1',20);
insert into T values (8,5,'item1',8);
insert into T values (9,5,'item2',15);
insert into T values (10,6,'item1',3);
insert into T values (11,6,'item2',15);
insert into T values (12,7,'item3',30);
insert into T values (13,8,'item3',30);
insert into T values (14,9,'item7',10);
and I need:
orders count in bulk
order1 2
order2 2
order3 3
order7 3
order8 3
order4,5,6,9 arent interesting because there is not dups for them.

Thanks.

if you know the maximum items per order (and this maxes out at a varchar2(4000)) this'll do it

ops$tkyte@ORA9IR2> select *
  2    from (
  3  select order_id, data, count(*) over (partition by data) cnt
  4    from (
  5  select order_id,
  6          max(decode(r,1,item||'/'||itm_ordered)) || '|' ||
  7          max(decode(r,2,item||'/'||itm_ordered)) || '|' ||
  8          max(decode(r,3,item||'/'||itm_ordered)) || '|' ||
  9          max(decode(r,4,item||'/'||itm_ordered)) || '|' ||
 10          max(decode(r,5,item||'/'||itm_ordered)) || '|' ||
 11          max(decode(r,6,item||'/'||itm_ordered)) || '|' ||
 12          max(decode(r,7,item||'/'||itm_ordered)) data
 13    from (select a.*, row_number() over (partition by order_id order by item, itm_ordered) r
 14            from t a
 15             )
 16   group by order_id
 17         )
 18         )
 19   where cnt > 1
 20  /
 
  ORDER_ID DATA                                                      CNT
---------- -------------------------------------------------- ----------
         1 item1/10|item2/15|||||                                      2
         2 item1/10|item2/15|||||                                      2
         3 item3/30||||||                                              3
         7 item3/30||||||                                              3
         8 item3/30||||||                                              3
 

How about this? I've been trying to think of a more effficient or at least more "elegant" way but so far this is the best I could come up with. I hope it helps.

SELECT * FROM
(SELECT order_id,
COUNT(*) OVER(PARTITION BY items) cnt FROM
(SELECT x.*,
ROW_NUMBER() OVER(PARTITION BY order_id
ORDER BY lvl DESC) rn
FROM
(SELECT y.*, LEVEL lvl,
SYS_CONNECT_BY_PATH(item || '/'
|| itm_ordered,
'|') items
FROM
(SELECT t.*,
ROW_NUMBER()
OVER (PARTITION BY order_id
ORDER BY item_id) itm_idx
FROM t
) y
CONNECT BY order_id = PRIOR order_id
AND itm_idx - 1 = PRIOR itm_idx
) x
)
WHERE rn = 1
)
WHERE cnt > 1

Hi Tom,
My requirement is
"I would like to get grouped rows from EMP table
where no individual makes more than 3000"

I tried this query but getting errors.

SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having sal <= 3000
  5  /
having sal <= 3000
       *
ERROR at line 4:
ORA-00979: not a GROUP BY expression
 

Hi Tom,
My requirement is:
I want to exclude grouped rows when atleast one individual
in each group gets a salary of more than 3000.

Hi Tom

I wanted to find a way to fetch from emp table where I pass deptno. And if deptno is not in emp then fetch all.

And this is what I did. Since deptno=50 not exists and 'OR' clauese 1=1 bribgs all results.

And when I use deptno=10 or 1=1 then also it fetches all deptno.
scott@ORA9I> select ename, empno from emp where deptno=10;

scott@ORA9I> select ename, empno, deptno from emp where deptno=10;

I think my concept of 'OR' caluse is messedup.

ENAME EMPNO DEPTNO
---------- ---------- ----------
CLARK 7782 10
KING 7839 10
MILLER 7934 10

3 rows selected.

scott@ORA9I> select ename, empno, deptno from emp where deptno=50;

no rows selected

scott@ORA9I> select ename, empno, deptno from emp where deptno=50 or 1=1;

ENAME EMPNO DEPTNO
---------- ---------- ----------
SMITH 7369 20
ALLEN 7499 30
WARD 7521 30
JONES 7566 20
MARTIN 7654 30
BLAKE 7698 30
CLARK 7782 10
SCOTT 7788 20
KING 7839 10
TURNER 7844 30
ADAMS 7876 20
JAMES 7900 30
FORD 7902 20
MILLER 7934 10

14 rows selected.

scott@ORA9I> select ename, empno, deptno from emp where deptno=10 or 1=1;

ENAME EMPNO DEPTNO
---------- ---------- ----------
SMITH 7369 20
ALLEN 7499 30
WARD 7521 30
JONES 7566 20
MARTIN 7654 30
BLAKE 7698 30
CLARK 7782 10
SCOTT 7788 20
KING 7839 10
TURNER 7844 30
ADAMS 7876 20
JAMES 7900 30
FORD 7902 20
MILLER 7934 10

14 rows selected.

scott@ORA9I>

Thanks and regards.

Hi Tom

You are right and some time I want to figure out some way to work around possibility in shorter and correct way. Although I can have two blocks in if statements and either one will work. That's double coding and if I add just 'were' clause then will it work?

What I am trying to get is that if I pass deptno which exists then get me that dept and if not exists then from all deptno.

Thanks for the hint and it works

scott@ORA9I> CREATE OR REPLACE PROCEDURE Demo(p_dep IN NUMBER )
2 AS
3 v1 NUMBER := p_dep;
4 BEGIN
5 FOR i IN (SELECT *
6 FROM Emp
7 WHERE (CASE WHEN p_dep = 0
8 THEN v1
9 ELSE deptno
10 END ) = p_dep )
11
12 LOOP
13 dbms_output.put_line(i.ename||' - '||i.deptno);
14 END LOOP;
15 END Demo;
16 /

Procedure created.

scott@ORA9I> exec demo(10)
CLARK - 10
KING - 10
MILLER - 10

PL/SQL procedure successfully completed.

scott@ORA9I> exec demo(20)
SMITH - 20
JONES - 20
SCOTT - 20
ADAMS - 20
FORD - 20

PL/SQL procedure successfully completed.

scott@ORA9I> exec demo(30)
ALLEN - 30
WARD - 30
MARTIN - 30
BLAKE - 30
TURNER - 30
JAMES - 30

PL/SQL procedure successfully completed.

scott@ORA9I> exec demo(0)
SMITH - 20
ALLEN - 30
WARD - 30
JONES - 20
MARTIN - 30
BLAKE - 30
CLARK - 10
SCOTT - 20
KING - 10
TURNER - 30
ADAMS - 20
JAMES - 30
FORD - 20
MILLER - 10

PL/SQL procedure successfully completed.

scott@ORA9I>

Thanks and regards.

guess I'll recommend

ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> exec :x := 50;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> declare
  2      l_cursor sys_refcursor;
  3      l_rec    emp%rowtype;
  4  begin
  5      open l_cursor for select * from emp where deptno = :x;
  6      fetch l_cursor into l_rec;
  7      if ( l_cursor%notfound )
  8      then
  9          close l_cursor;
 10          open l_cursor for select * from emp;
 11          fetch l_cursor into l_rec;
 12      end if;
 13      loop
 14          exit when l_cursor%notfound;
 15          dbms_output.put_line( l_rec.deptno || ', ' || l_rec.ename );
 16          fetch l_cursor into l_rec;
 17      end loop;
 18      close l_cursor;
 19  end;
 20  /
20, SMITH
30, ALLEN
30, WARD
20, JONES
30, MARTIN
30, BLAKE
10, CLARK
20, SCOTT
10, KING
30, TURNER
20, ADAMS
30, JAMES
20, FORD
10, MILLER
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec :x := 10
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
10, CLARK
10, KING
10, MILLER
 
PL/SQL procedure successfully completed.
 

 

Hi Tom

Thanks and you showed one more way to do this.

Thanks and regards

Hi Tom,

I have 2 tables like

create table t1
(
id1 number,
name varchar2(60),
id2 number
);

create table t2
(
id2 number,
xxx char(1)
);

insert into t1 values (1, 'firstname', 1);
insert into t1 values (2, 'secondname', 2);
insert into t2 values (1, 'A');
insert into t2 values (1, 'B');
insert into t2 values (2, 'A');
insert into t2 values (2, 'B');
commit;

I have a query

select t1.id1, t1.name, t1.id2, t2.xxx from t1, t2
where t1.id2 = t2.id2
/

ID1 NAME ID2 X
---------- ------------------------------------------------------------ ---------- -
1 firstname 1 A
1 firstname 1 B
2 secondname 2 A
2 secondname 2 B


Is it possible to get something like

ID1 NAME ID2 XA XB

1 firstname 1 A B
2 secondname 2 A B

The values for columns ID1, NAME, ID2 for all rows for A and B will always be the same.

Thanks.

No firstname will always have only 2 rows and so will secondname.

Thanks.

ops$tkyte@ORA9IR2> Select id1, name, id2, max( decode(rn,1,xxx) ) c1, max(decode(rn,2,xxx)) c2
  2    from (
  3  select t1.id1, t1.name, t1.id2, t2.xxx ,
  4         row_number() over (partition by t1.id1, t1.name, t1.id2 order by t2.xxx) rn
  5    from t1, t2
  6   where t1.id2 = t2.id2
  7         )
  8   group by id1, name, id2
  9  /
 
       ID1 NAME                                  ID2 C C
---------- ------------------------------ ---------- - -
         1 firstname                               1 A B
         2 secondname                              2 A B
 


there is of course an easier answer if xxx is always A/B and nothing but nothing else. 

Yes xxx will not have anything else other than A/B. Is there an easier way to do this in that case?

Thanks a lot for your help Tom.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select t1.id1, t1.name, t1.id2, max(decode(t2.xxx,'A','A')) xa, max(decode(t2.xxx,'B','B')) xb  2    from t1, t2
  3   where t1.id2 = t2.id2
  4   group by t1.id1, t1.name, t1.id2
  5  /
 
       ID1 NAME                                  ID2 X X
---------- ------------------------------ ---------- - -
         1 firstname                               1 A B
         2 secondname                              2 A B
 

I have a similar requirement but I am selecting around 20 fields and so my "group by" clause also has 20 columns. If I try a similar query with the 20 fields, it is taking a very long time, do I have to do anything special for that? Please let me know.

Thanks.

Our database is 10g. I setup the session for 10046 trace level 12 and I am attaching below the last part of the tkprof output. Is this what I should be looking at for the wait times. Please let me know and if it is tell me what I should see to determine how to make my query faster.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 10 0.00 0.02 0 11 10 10
Fetch 5 39.51 427.26 566787 575769 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 39.51 427.30 566787 575780 10 20

Misses in library cache during parse: 5
Misses in library cache during execute: 4

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 12 0.00 0.00
SQL*Net message from client 11 13.07 13.14
direct path write temp 10800 0.02 3.35
db file sequential read 732 0.03 4.49
db file scattered read 38827 0.06 389.03
direct path read temp 22 0.04 0.33


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 14 0.01 0.01 0 2 8 8
Fetch 6 0.00 0.00 0 15 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.01 0.01 0 17 8 14

Misses in library cache during parse: 6
Misses in library cache during execute: 5

15 user SQL statements in session.
1 internal SQL statements in session.
16 SQL statements in session.
********************************************************************************
Trace file: wicis_ora_1116.trc
Trace file compatibility: 9.00.01
Sort options: default

3 sessions in tracefile.
18 user SQL statements in trace file.
1 internal SQL statements in trace file.
16 SQL statements in trace file.
13 unique SQL statements in trace file.
51339 lines in trace file.

Thanks.

ops$tkyte@ORA9IR2> select 38827/389.03 from dual;
 
38827/389.03
------------
  99.8046423
 
ops$tkyte@ORA9IR2> select 389.03/38827 from dual;
 
389.03/38827
------------
  .010019574


You are doing 100 multiblock io's/second or about 0.01 seconds per IO.  Does that seem "fast or correct" given your hardware?

but therein lies your low hanging fruit, look for the do'ers of IO and see what you can do to have them do less of it. 

I am sorry but I am not able to understamd much from your response. Can you elaborate it a little please?

Thanks.

Regd the following query from previous followup

select t1.id1, t1.name, t1.id2, max(decode(t2.xxx,'A','A'))
xa, max(decode(t2.xxx,'B','B')) xb 2 from t1, t2
where t1.id2 = t2.id2
group by t1.id1, t1.name, t1.id2
/

I have about 10 mil rows in t2. I have an index on t2(xxx). But it is still doing a table scan and the query results are very slow. Should I create some sort of functional index or something ?

Thanks.

Ho Tom,

Will you please help me with this. I have a table that looks like below:

col1 col2
---- ----
1 1
2 2
3 3
4 4

I would like a select statement that displays:

col1 col2
---- ----
1 1
2 1
3 1
4 1

In other words, I'd like the value in col2 to show the smallest value of all rows.

Thank you very much.

Hi Tom,

I have the foll requirement:

create table t1
(
datecol date,
idcol1 number,
idcol2 number,
col1 varchar2(20),
charcol char(1),
opcol varchar2(10)
);

I am doing the following queries in a single procedure all based on t1.

select max(datecol), max(idcol1), max(idcol2)
into tdatecol, tidcol1, tidcol2
from t1
where col1 = '1234'
and charcol = 'A';

select max(datecol), max(idcol2)
into tdatecol1, tidcol21
from t1
where idcol1 = tidcol1
and charcol = 'B';

select opcol into topcol
from t1
where idcol2 = tidcol2;

if (tidcol21 is not null) then
select opcol into topcol1
from t1
where idcol2 = tidcol21;
end if;

Is it possible for me to put this all in a single query? Please help.

Thanks.

I thank you for your suggestion but how can we do that on an non-enterprise version? Below is what I have:

SQL> select banner from v$version;

BANNER
----------------------------------------------------------
Oracle8i Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.2.1       Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

and

SQL> select * from v$option
PARAMETER                        VALUE
---------------------------      -----------
...                              ...
OLAP Window Functions            FALSE

I appreciate your help. 

I have a table this way:

eff_date term_date contr_type
01/01/2001 01/31/2001 12
01/01/2001 01/31/2001 13

I want the output to be like this:

01/01/2001 01/31/2001 12,13

Can this be done through sql ? I use Oracle 9i

Search for Pivot Table or check out this thread:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:766825833740 <code>

That works great! Thanks again for your help.

Tom,

I am running this query and every time getting different answer. Is it a bug or feature mind you I am the only one logged in.

2. Does DISTINCT apply only on first following column or all columns in the list? 

Thank you in advance.

  1  SELECT DISTINCT loan_id, program_name
  2  FROM   loan_history
  3* WHERE ROWNUM < 30

14:16:31 SQL> /

LOAN_ID              PROGRAM_NAME
-------------------- ------------------------------
0000000000           FR 15 Yr CF
1111111111           FR 15 Yr CF
2222222222           FR 15 Yr CF
3333333333           FR 15 Yr CF
4444444444           FR 25-30 Yr CF
5555555555           FR CF 20-30 Yr
6666666666           FR 15 Yr CF
7777777777           CMT ARM 3/1 CF (salable)
8888888888           FR 15 Yr CF

9 rows selected.

14:16:34 SQL> l
  1  SELECT DISTINCT loan_id, program_name
  2  FROM   loan_history
  3* WHERE ROWNUM < 30
14:17:22 SQL> /

LOAN_ID              PROGRAM_NAME
-------------------- ------------------------------
5555555555           FR CF 20-30 Yr
4444444444           FR 25-30 Yr CF
0000000000           FR 15 Yr CF
4141414141           30 Yr Fixed FHA/VA - Salable
4444444444           FR 25-30 Yr CF
4141414141           FR 15 Yr CF
5353535353           FR 15 Yr CF
5454545454           FR 25-30 Yr CF
6565656565           FR 25-30 Yr CF
6767676767           FR 15 Yr CF
8787878787           FR 25-30 Yr CF

11 rows selected.

14:17:23 SQL> l
  1  SELECT DISTINCT loan_id, program_name
  2  FROM   loan_history
  3* WHERE ROWNUM < 30
14:17:25 SQL> /

LOAN_ID              PROGRAM_NAME
-------------------- ------------------------------
3232323232           FR 15 Yr CF
3434343434           FR 15 Yr CF
4545454545           FR 15 Yr CF
5656565656           FR 15 Yr CF
6767676767           FR CF 20-30 Yr
7878787878           FR 15 Yr CF
8989898989           FR CF 20-30 Yr

7 rows selected.

14:17:26 SQL> 

Tom,

Essentially query had to answer very simple question "show me all distinct loan ids and programs" but I started testing and could not understand why same query returns different number of rows.
I always thought that selecting ROWNUN returns a sequential number of rows. Why would Oracle select any random number of rows when I asked to show me all 29 or less?

2. Still not clear whether DISTINCT/UNIQUE apply on first column that follows or all columns in the select list following DISTINCT/UNIQUE.

Thank you,

Tom,

1. Would it be safe to say that Distinct is sort of Group By clause? If so how is it internally different or similar?
2. If Oracle is free to give me any number of rows below 30 how would I find out exact count of unique Last Names of occupants in a 100 stories building below 30th floor?

Thanks,

Hi Tom,
My requirement is
"Retrieve all employees from emp table
who have joined before their manager".

I used this query.
Is this correct??
Do you have a better way to put this query??

SQL> select * from emp e
  2  where hiredate <= all(
  3  select hiredate from emp where empno in(select distinct mgr 

from emp)
  4  and deptno = e.deptno)
  5  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL   

    COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- 

---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        880   

                 20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600   

     300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250   

     500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975   

                 20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850   

                 30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450   

                 10

6 rows selected.