Where can I find more
Guy, September 17, 2002 - 10:35 am UTC
Tom, Great solution. When can find the documentaion for these special queries (analytic functions)?
Swell analytic function usage, but what about that implicit arithmetic grouping?
Scott, September 17, 2002 - 10:44 am UTC
Yes indeed, analytic functions rule. However your use of implicit arithmetic precidence kinda scares me:
1/24/60*2.5
I have to either figure out what this is meant to resolve to from the context of the question or I have to go dig into Oracle's operator precidence resolution rules. And do they vary between the sql engine and the pl/sql engine? Ick.
1/(24*60*2.5)
Whew.
Apart from that, much like analytic functions, you rule Tom (lest my nit-picking be construed as undue criticism).
Cheers,
Scott
Excellent implementation ! Hats off !
Rama, September 17, 2002 - 11:08 am UTC
Question
Prashant, September 17, 2002 - 12:00 pm UTC
Can you please clarify "1/24/60*2.5"? Why you have 2.5?
Also I needed if account number is also duplicated.
September 17, 2002 - 12:48 pm UTC
1/24 = 1 hour
1/24/60 = 1 minute
2.5 * (1 minute) = 2 and 1/2 minutes.
We wanted a 5 minute "window" around the current row -- and to count all of the rows in that 5 minute window. That is what:
5 over (partition by specialist_id
6 order by date_created
7 range between 1/24/60*2.5 preceding and 1/24/60*2.5 following
does -- it breaks the result set up by specialist_id
Within each group of specialist-ids, it sorted by DATE_CREATED
The count(*) was then computed for all rows within + or -2 1/2 minutes of the current rows date_created (the range is always based on the order by column)
You could either partition by specialist_id, account_no to get it by specialist_id/acount_no
Can we use self join for this...
Anand, February 28, 2003 - 8:19 am UTC
Tom, as usual learnt a lot. Can we use self join to get the same done?
select a.specialist_id,a.account_no,to_chara.date_created,'dd-mon-yy hh:mi:ss')
from testtable a, testtable b
where
a.specialist_id=b.specialist_id
and a.date_created <> b.date_created
and ((a.date_created - b.date_created)*24*60* 60) between -150 and 150 --Window of 5 minutes
and trunc(a.date_created) between '10-SEP-2002' and '16-SEP-2002'
order by 2,3
February 28, 2003 - 10:09 am UTC
I usually use self joins as part two of examples with analytics to show how inhumanly inefficient they are to accomplish this task!
Sure -- you can do it.
But
It'll be WAY slow
put a couple thousand rows in there -- say 10-20k -- then check it out.
Note: word to the wise....
would you compare an apple to a toaster oven?
Then why compare a date to a string -- ALWAYS but ALWAYS compare dates to dates, strings to strings, numbers to number and NEVER NEVER store a date as a number/string or a number as a string.
data type ?
A reader, February 28, 2003 - 12:57 pm UTC
Hi tom,
do we have any fn that will tell me the datatype of the variable >
type () ?
if we try to create one how hard it is ?
February 28, 2003 - 1:55 pm UTC
with the new inheritance system for object types -- yes, as long as you have a routine that accepts the parent type -- you can ask "what type are you really"
for the scalar types -- it is pretty easy
ops$tkyte@ORA920> create or replace package what
2 as
3 function type_am_i( x in number ) return varchar2;
4 function type_am_i( x in varchar2 ) return varchar2;
5 function type_am_i( x in date ) return varchar2;
6 end;
7 /
Package created.
ops$tkyte@ORA920> create or replace package body what
2 as
3 function type_am_i( x in number ) return varchar2
4 is begin
5 return 'number'; end;
6
7 function type_am_i( x in varchar2 ) return varchar2
8 is begin
9 return 'varchar2'; end;
10
11 function type_am_i( x in date ) return varchar2
12 is begin
13 return 'date'; end;
14
15 end;
16 /
Package body created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 a number;
3 b varchar2(20);
4 c date;
5 begin
6 dbms_output.put_line( what.type_am_i(a) );
7 dbms_output.put_line( what.type_am_i(b) );
8 dbms_output.put_line( what.type_am_i(c) );
9 end;
10 /
number
varchar2
date
PL/SQL procedure successfully completed.
But -- why? the caller would already KNOW what type the thing was -- how could it "not" know????
just like that !
A reader, February 28, 2003 - 3:57 pm UTC
if it annoied you, I am sorry
I was just wondering if I need to find the type at run time or something.
by the way the "LET US KNOW" link is a the bottom "NOW"
that's very cool and convinient
thanks :)
February 28, 2003 - 4:46 pm UTC
you didn't annoy me -- i'm just wondering what piece of code you could have that would need to find the datatype at runtime -- I cannot imagine where or how this would be used.
I mean, you have a variable "X".
You wrote the code.
You defined X
You by definition know the datatype already.
compare
A reader, February 28, 2003 - 5:02 pm UTC
Wrote file afiedt.buf
1 declare
2 a number;
3 b varchar2(20);
4 c date;
5 begin
6 c := '01-JAN-2003'; -- 1
7 dbms_output.put_line( what.type_am_i(c) ||' 1:'|| c);
8 c := sysdate; --2
9 dbms_output.put_line( what.type_am_i(c) ||' 2:'|| c);
10 c := to_date('01-JAN-2003'); --3
11 dbms_output.put_line( what.type_am_i(c) ||' 3:'|| c);
12 c := to_date('01-JAN-2003','dd-MON-rrrr'); --4
13 dbms_output.put_line( what.type_am_i(c) ||' 4:'|| c);
14* end;
SQL> /
date 1:01-JAN-03
date 2:28-FEB-03
date 3:01-JAN-03
date 4:01-JAN-03
PL/SQL procedure successfully completed.
so in above what is implicit conversion
(should I use it ?)
what is explicit conversion (should I use it ?)
explicitly with dates in chars
February 28, 2003 - 7:29 pm UTC
1 - implicit
2 - no conversion
3 - explicit
4 - explicit
you should ALWAYS explicity convert a string to a date in my opinion. Do this:
alter session set nls_date_format = 'dd/mm/yyyy';
and then run your block and see what happens.
what's the point in doing that ?
A reader, March 03, 2003 - 10:31 am UTC
Hi tom,
I executed the block with change in the sesssion date format . what is the difference you were pointing ?
please explain !!
10:32:25 SQL> alter session set nls_date_format = 'dd/mm/yyyy';
Session altered.
Elapsed: 00:00:00.00
10:32:31 SQL> declare
10:32:36 2 a number;
10:32:36 3 b varchar2(20);
10:32:36 4 c date;
10:32:36 5 begin
10:32:36 6 c := '01-JAN-2003'; -- 1
10:32:36 7 dbms_output.put_line( what.type_am_i(c) ||' 1:'|| c);
10:32:36 8 c := sysdate; --2
10:32:36 9 dbms_output.put_line( what.type_am_i(c) ||' 2:'|| c);
10:32:36 10 c := to_date('01-JAN-2003'); --3
10:32:36 11 dbms_output.put_line( what.type_am_i(c) ||' 3:'|| c);
10:32:36 12 c := to_date('01-JAN-2003','dd-MON-rrrr'); --4
10:32:36 13 dbms_output.put_line( what.type_am_i(c) ||' 4:'|| c);
10:32:36 14 end;
10:32:36 15 /
date 1:01/01/2003
date 2:03/03/2003
date 3:01/01/2003
date 4:01/01/2003
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
March 03, 2003 - 10:39 am UTC
ok, try this one
ops$tkyte@ORA920> alter session set nls_date_format = 'yyyy/mm/dd';
Session altered.
changed the nls_date format..
A reader, March 03, 2003 - 10:48 am UTC
10:32:55 SQL> alter session set nls_date_format = 'yyyy/mm/dd';
Session altered.
Elapsed: 00:00:00.00
10:45:37 SQL> declare
10:45:43 2 a number;
10:45:43 3 b varchar2(20);
10:45:43 4 c date;
10:45:43 5 begin
10:45:43 6 c := '01-JAN-2003'; -- 1
10:45:43 7 dbms_output.put_line( what.type_am_i(c) ||' 1:'|| c);
10:45:43 8 c := sysdate; --2
10:45:43 9 dbms_output.put_line( what.type_am_i(c) ||' 2:'|| c);
10:45:43 10 c := to_date('01-JAN-2003'); --3
10:45:43 11 dbms_output.put_line( what.type_am_i(c) ||' 3:'|| c);
10:45:43 12 c := to_date('01-JAN-2003','dd-MON-rrrr'); --4
10:45:43 13 dbms_output.put_line( what.type_am_i(c) ||' 4:'|| c);
10:45:43 14 end;
10:45:43 15 /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6
Elapsed: 00:00:00.00
10:45:44 SQL>
/*****************************************************/
obviously, as my sesssion date format has changed and
if i assign c := '01-JAN-03';
it is not going to work, I have to
do something like this c := '03-JAN-01';
in the first case !! (because I selected to enter date in that format by changing the date format)
/*****************************************************/
Wrote file afiedt.buf
1 declare
2 a number;
3 b varchar2(20);
4 c date;
5 begin
6 c := '2003-01-01'; -- 1
7 dbms_output.put_line( what.type_am_i(c) ||' 1:'|| c);
8 c := sysdate; --2
9 dbms_output.put_line( what.type_am_i(c) ||' 2:'|| c);
10 c := to_date('2003-01-01'); --3
11 dbms_output.put_line( what.type_am_i(c) ||' 3:'|| c);
12 c := to_date('01-JAN-2003','dd-MON-rrrr'); --4
13 dbms_output.put_line( what.type_am_i(c) ||' 4:'|| c);
14* end;
10:50:57 SQL> /
date 1:2003/01/01
date 2:2003/03/03
date 3:2003/01/01
date 4:2003/01/01
PL/SQL procedure successfully completed.
March 03, 2003 - 11:13 am UTC
that is why I say --
you should ALWAYS explicity convert a string to a date in my opinion.
Thanks !!
A reader, March 03, 2003 - 11:15 am UTC
jim, March 03, 2003 - 6:12 pm UTC
Analytical functions are execellent....
I'm still gettting the hang of writing them
but they save ALOT of coding..
"Then why compare a date to a string -- ALWAYS but ALWAYS compare dates to dates, strings to strings, numbers to number and NEVER NEVER store a date as a number/string or a number as a string."
I run a small mailing list answering Oracle problems
and one of the most common issues are people
using to_date on a a date, comparing dates to strings
and storing dates as strings
select a maximum date from table for update
Charlie, March 04, 2003 - 12:57 am UTC
Tom,
I have an update like,
Update emp a
set sal= 1.1*
where trunc(hire_date)=(select max(hire_date)
from emp b, dept d
where a.emp_id=b.emp_id
and b.dept_no = d.dept_no
and d.dept_no=10)
Is it possible to update this using analytic functions.
thanks
March 04, 2003 - 6:55 am UTC
sort of -- but the subquery doesn't make sense to me - emp_id, isn't that a primary key....
implicit and explicit Conversion
Marcio, November 13, 2003 - 3:19 pm UTC
you said: you should ALWAYS explicity convert a string to a date in my opinion.
Is there any performance issue about use of implicity/explicity conversion?
November 13, 2003 - 9:18 pm UTC
there are BUGS with regards (rely on implicit conversions and you have a bug)
other then that, nothing to consider here. You always want to use explicit conversions to avoid BUGS and erroneous code.
flaw in query
Daniel, November 21, 2003 - 11:14 am UTC
We can't assume a -2.5min ~ 2.5min window on current row, if the first two rows in sample data as follow:
1 100 15-SEP-2002 09:10:10
1 100 15-SEP-2002 09:15:00
I think they meet the requirement, but Tom's query will not pick them up.
Here is another approach,
select specialist_id, account_no, to_char(date_created, 'dd-MON-yyyy hh24:mi:ss') date_created
from (
select t.*,
abs(date_created -
nvl(lag(date_created)
over ( partition by specialist_id order by date_created ), date_created-1)
) interval1,
abs(date_created -
nvl(lead(date_created)
over ( partition by specialist_id order by date_created ), date_created+1)
) interval2
from interaction_log t
where date_created between to_date( '10-SEP-2002', 'dd-mon-yyyy' )
and to_date( '16-SEP-2002', 'dd-mon-yyyy' )
)
where interval1 <= 5/24/60 or interval2 <= 5/24/60
/
Question
PRS, December 04, 2003 - 10:54 am UTC
Tom,
I have a trading table named trade.
TRADE
CUST_ID
TRADE_ID
TRADE_DATE
I want to find out the average trade time for a customer. Is there any analytical function available to do this.
Thanks,
PRS
December 04, 2003 - 11:17 am UTC
Here is the "average time of creation of objects by owner" in my database.
substitute cust_id for owner, trade_date for created....
ops$tkyte@ORA920PC> select owner,
2 avg( to_number( to_char(created,'sssss') ) ) ,
3 to_char( to_date( round(avg( to_number( to_char(created,'sssss') ) ) ), 'sssss' ), 'hh24:mi:ss' )
4 from all_objects
5 group by owner
6 /
OWNER AVG(TO_NUMBER(TO_CHAR(CREATED,'SSSSS'))) TO_CHAR(
------------------------- ---------------------------------------- --------
AQ 36189.4783 10:03:09
BIG_TABLE 40086.6667 11:08:07
CTW 26908 07:28:28
CTXSYS 52019.7778 14:27:00
DEMO 42547.5 11:49:08
SSSSS is the number of seconds past midnight, get the average of that, convert back to date and print it.
question
PRS, December 04, 2003 - 11:45 am UTC
I did not understand the query totally. I have the trade_date difference in days. And you are printing it in HH24:MM:SS. Can you please clarify?
Thanks very much
December 04, 2003 - 12:17 pm UTC
now I don't understand.
i would have thought that trade_date was an oracle date datatype -- a timestamp if you will.
i took the time component (sssss) and averaged it. turned it back into a date and display the time portion (the average time of day they traded)
maybe you better explain your data better with an example
Details
PRS, December 04, 2003 - 12:41 pm UTC
Sorry for not explained properly.
Let me put the data below.
CUST_ID TRADE_ID TRADE_DATE
---------- ---------- ----------------------
1 1 8/26/2003 11:03:13 AM
1 2 9/5/2003 11:03:13 AM
1 3 9/15/2003 11:03:13 AM
1 4 9/25/2003 11:03:13 AM
1 5 10/15/2003 11:03:13 AM
1 6 5/18/2003 11:03:13 AM
1 7 7/17/2003 11:03:13 AM
1 8 6/17/2003 11:03:13 AM
1 9 3/29/2003 11:03:13 AM
2 1 8/26/2002 11:03:13 AM
2 2 9/5/2002 11:03:13 AM
2 3 9/15/2002 11:03:13 AM
2 4 9/25/2003 11:03:13 AM
2 5 10/15/2003 11:03:13 AM
3 1 8/26/2000 11:03:13 AM
3 2 9/5/2001 11:03:13 AM
3 3 9/15/2002 11:03:13 AM
3 4 9/25/2003 11:03:13 AM
So the output should be.
Cust Id Average Trade Time (Days)
1 23.32 (Just an Example)
2 100.10
3 360
I have just put the days as an example. It is not calculated for the above data.
Thanks in advance.
PRS
December 04, 2003 - 12:50 pm UTC
do you mean "the average number of days between consectutive trades by customer id"????
ops$tkyte@ORA920PC> select cust_id, avg( next_trade_date - trade_date )
2 from (
3 select cust_id, trade_date, lead(trade_date) over (partition by cust_id
4 order by trade_date) next_trade_date
5 from t
6 )
7 group by cust_id
8 /
CUST_ID AVG(NEXT_TRADE_DATE-TRADE_DATE)
---------- -------------------------------
1 25
2 103.75
3 375
Thanks.........
PRS, December 04, 2003 - 1:11 pm UTC
Thanks a lot. One of the best site for ORACLE questions. Really appreciate it.
PRS
Question
PRS, December 15, 2003 - 10:17 am UTC
I have a table named X and it has three columns LAST_NAME, FIRST_NAME, MIDDLE_NAME. Following is the test data.
Last Name First Name Middle Name
------------------------------------------
WHITE ROBERT R
KENNY JOHN U
I want to wirte the query in such a way that the output shown below.
Text Type
---------------------
WHITE L
KENNY L
ROBERT F
JOHN F
U M
R M
Appreciate your help.
Thanks,
PRS
December 15, 2003 - 10:40 am UTC
select decode( type, 'L', last_name, 'F', first_name, 'M', middle_name ),
type
from X, ( select 'L' type from dual UNION ALL
select 'F' type from dual UNION ALL
select 'M' type from dual )
Ome more question
PRS, December 15, 2003 - 10:35 am UTC
Also for the above query I need a output as shown below
Text Type
WHITE L
ROBERT F
R M
KENNY L
JOHN F
U M
Youe help is appreciated.
PRS
December 15, 2003 - 10:43 am UTC
select decode( type, 'L', last_name, 'F', first_name, 'M', middle_name ),
type
from (select rownum r, x.* from X), ( select 'L' type from dual UNION ALL
select 'F' type from dual UNION ALL
select 'M' type from dual )
order by r, decode( type, 'L', 1, 'F', 2, 'M' 3 )
First query no order by
PRS, December 15, 2003 - 11:15 am UTC
In the first query reply
select decode( type, 'L', last_name, 'F', first_name, 'M', middle_name ),
type
from X, ( select 'L' type from dual UNION ALL
select 'F' type from dual UNION ALL
select 'M' type from dual )
Middle Initial 'U' row does not come first. It come later. Can we just do an order by descending order for last_name,
first_name, middle_name?
Thanks,
PRS
December 15, 2003 - 11:18 am UTC
add an appropriate order by. I did not know "order" was relevant (wasn't specified, in the second query is was more "obvious" since the only difference was order)
but you seem to have attached some meaning to the order of the rows in the table itself -- but there isn't any really, you seem to assume the rows will come out in order of insertion or something -- they do not, they are just random.
Date Breaks
Suleiman Akbar, December 27, 2003 - 9:17 am UTC
Tom,
I need ur help in solving this query.
looking for an output which breaks the dates. have a look at the query which is in the view. I have to call this view and have to make the date breaks.
View :
CREATE OR REPLACE VIEW EH_AF_CSTMKTFARE_VW AS
SELECT orgdst_type,
point_of_sale,
cst.point_of_sale
cst.destination,
mkp.booking_from_date,
mkp.booking_to_date,
mkp.travel_from_date,
mkp.travel_to_date,
cst.fare_id,
cst.rule_id,
mkt.market_fare_id,
mkt.rule_id market_rule_id,
mkt.booking_from_date mkt_booking_from_date,
mkt.booking_to_date mkt_booking_to_date,
mkt.travel_from_date mkt_travel_from_date,
mkt.travel_to_date mkt_travel_to_date
FROM eh_af_orgdst_cost cst, eh_af_markup mkp,
eh_af_mktfare_fare mkt
WHERE cst.fare_id = mkp.fare_id AND
cst.point_of_sale = cst.point_of_sale AND
cst.origin = mkt.origin(+) AND
cst.destination = mkt.destination(+) AND
cst.category = mkt.category(+) AND
cst.active_flag = mkt.active_flag(+) AND
( (mkp.booking_from_date BETWEEN
mkt.booking_from_date AND mkt.booking_to_date) OR
(mkp.booking_to_date BETWEEN mkt.booking_from_date
AND mkt.booking_to_date) OR
(mkt.booking_from_date BETWEEN
mkp.booking_from_date AND mkp.booking_to_date) OR
(mkt.booking_to_date BETWEEN mkp.booking_from_date
AND mkp.booking_to_date) OR
mkt.booking_from_date IS NULL
) AND
( (mkp.travel_from_date BETWEEN mkt.travel_from_date
AND mkt.travel_to_date) OR
(mkp.travel_to_date BETWEEN mkt.travel_from_date
AND mkt.travel_to_date) OR
(mkt.travel_from_date BETWEEN mkp.travel_from_date
AND mkp.travel_to_date) OR
(mkt.travel_to_date BETWEEN mkp.travel_from_date
AND mkp.travel_to_date) OR
mkt.travel_from_date IS NULL
)
WHAT iam looking for is.....
In the table EH_AF_MARKUP, will as the dates as follows:
BkgFrm BkgTo DepFrm DepTo
01/04/2003 31/03/2004 01/04/2003 31/03/2004
Corresponding entries of dates exists in EH_AF_MARKET_FARE table as
BkgFrm BkgTo DepFrm DepTo
01/04/2003 30/06/2003 01/04/2003 30/06/2003
01/04/2003 31/12/2003 01/07/2003 31/12/2003
Now I want the date result as
BkgFrm BkgTo DepFrm DepTo
01/04/2003 30/06/2003 01/04/2003 30/06/2003
01/04/2003 31/12/2003 01/07/2003 31/12/2003
01/04/2003 31/03/2004 01/01/2004 31/03/2004
I really appreciate if you can answer either in SQL / PLSQL ...
Thxs in advance.
Suleiman Akbar.
December 27, 2003 - 9:55 am UTC
sorry -- a tad confusing here. when I'm taking new questions, feel free to ask there but you would want to provide a tiny example and explain how you get from your inputs to your desired outputs.
unless the depfrm in the last row there in the outputs is a typo -- i don't get it.
if it is a typo -- read about "union all".
Follow-up for ur queries
Suleiman akbar, December 27, 2003 - 11:34 am UTC
Tom,
If the dates are like this in Table 1
BkgFrm BkgTo DepFrm DepTo
01/04/2003 31/03/2004 01/04/2003 31/03/2004
And few dates are like this in Table 2
BkgFrm BkgTo DepFrm DepTo
01/04/2003 30/06/2003 01/04/2003 30/06/2003
01/04/2003 31/12/2003 01/07/2003 31/12/2003
Now I want the date result as
BkgFrm BkgTo DepFrm DepTo
01/04/2003 30/06/2003 01/04/2003 30/06/2003
01/04/2003 31/12/2003 01/07/2003 31/12/2003
01/04/2003 31/03/2004 01/01/2004 31/03/2004
(last row of Booking From & To is taken from the table1 and Departure From is calculated as the end of the second row (departure to date) plus one (01/01/2004) and it's (3rd row) departure to will be the greatest of Table 1's or Table 2's Departure Date!
Pls. throw light with ur own example if u didn't get the above big query.
I really appreciate if you can answer either in SQL / PLSQL ...
Thxs in advance.
Suleiman Akbar.
December 27, 2003 - 11:52 am UTC
not fully baked -- don't understand 100% how you group the rows in t1 and t2 together, but I'll make the bold assumption that you group by bkgfrm (when equal, these are a "group") and you order that group by bkgto.
if so:
ops$tkyte@ORA9IR2> create table t1 ( bf date, bt date, df date, dt date );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( bf date, bt date, df date, dt date );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd/mm/yyyy';
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( '01/04/2003','31/03/2004','01/04/2003','31/03/2004' );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( '01/04/2003','30/06/2003','01/04/2003','30/06/2003' );
1 row created.
ops$tkyte@ORA9IR2> insert into t2 values ( '01/04/2003','31/12/2003','01/07/2003','31/12/2003' );
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select bf, bt,
2 decode( oc, 1, df, lag(dt+1) over (partition by bf order by bf, bt, oc) ) df,
3 dt
4 from (
5 select 2 oc, t1.* from t1
6 union all
7 select 1 oc, t2.* from t2
8 )
9 order by bf, bt, oc
10 /
BF BT DF DT
---------- ---------- ---------- ----------
01/04/2003 30/06/2003 01/04/2003 30/06/2003
01/04/2003 31/12/2003 01/07/2003 31/12/2003
01/04/2003 31/03/2004 01/01/2004 31/03/2004
Confusion
S.Akbar, December 27, 2003 - 12:01 pm UTC
Tom,
We are not using any Partioned Tables, I think the logic what u have enclosed will work for me. Pls. assume that it's an ordinary table without any partitions.
Got confused with "Lag". It's better if u can show the same results without using partitions.
Thxs in advance.
S.Akbar
December 27, 2003 - 12:15 pm UTC
lag is an analytic function.
the partition clause has nothing to do with physically partitioning a table.
it is just SQL (my example has not partitioned tables either!)
see the data warehousing guide for analytic functions or if you have my book "Expert one on one Oracle" i have a lengthly chapter on them. Coolest thing to happen to sql since the keyword select.
how to insert rows between empty rows and rowsdatavalues
suresh, December 28, 2003 - 12:56 am UTC
Hi tom,
In database table somebody inserting the rows into the table like this 1 to 10 rows values,next 20 to 30 rows are values,next 40 to 50 rows have values and in between 10 to 20 is empty rows(nodata), 30 to 40 empty rows.
Question :
when i insert the row into the table my first row is inserted into 11 th,next 12 th....upto 20th.
next 30 th row,next 31 st row......like this upto 40th row...
How to insert my row values into the firstempty row in the table(insert 11 th row,next 12 th row...like this?
pls reply,
Regards
suresh
December 28, 2003 - 10:54 am UTC
there is no such thing as the "N'th row in a table"
I hope you aren't thinking that data comes out in the order it went in or anything like that.
In short -- i'm not following your question at all, but I think you think data has "some order" associated with it which is 100% false.
how to retrive the empty rows only.
Suresh, December 28, 2003 - 1:19 am UTC
hi tom,
i created a sequence for one column in database table, i inserted some rows(2 rows,seq.no:1,2) into that dbtable and commited and again i inserted some rows(2 rows seq.no:3,4) and rollback these rows.
again i inserted some rows(2 rows seq.no:5,6) and commit.
to Query the dbtable(seq.no's 1,2,5,6....) rows are displayed only .
this process is going on for some rows...
Question :
How to retrive the empty rows(null rows)
(rows like seq.no : 3,4,7,8.....) ?
pls reply,
Regards,
Suresh.
December 28, 2003 - 11:03 am UTC
ahh, I see, you want to get all of the "gaps".
the most efficient is to actually just get the start/stop and let your "mind" fill in the rest. For example:
ops$tkyte@ORA9IR2> select * from t;
X
----------
1
2
5
6
8
9
15
7 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x+1, next_x-1
2 from (
3 select x, lead(x) over (order by x) next_x
4 from t
5 )
6 where x <> next_x-1
7 /
X+1 NEXT_X-1
---------- ----------
3 4
7 7
10 14
shows that 3, 4, 7, 10, 11, 12, 13, 14 are "missing" in this set.
Else, you have to synthesize a result set that is "as large as the difference between the max and min values" and then join. It could look like this:
ops$tkyte@ORA9IR2> create or replace type myTableType as table of number
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function result_set(p_iterations in number) return myTableType
2 pipelined
3 as
4 begin
5 for i in 1 .. p_iterations
6 loop
7 pipe row(i);
8 end loop;
9 return;
10 end;
11 /
Function created.
ops$tkyte@ORA9IR2> select * from table( result_set( (select max(x)-min(x)+1 from t)) );
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.column_value
2 from table( result_set( (select max(x)-min(x)+1 from t)) ) a
3 left join t on ( a.column_value = t.x )
4 where t.x is null
5 order by a.column_value
6 /
COLUMN_VALUE
------------
3
4
7
10
11
12
13
14
8 rows selected.
OK
Ram, December 28, 2003 - 2:30 am UTC
Dear sir,
well and wish the same from you.I tried the query shown
below
sql>select count(*) as cnt from dual
where exists(select 'x' from emp where deptno = 10);
cnt
-----
1
The query output comes as 1 not as 3.I use version 8.0.
How to correct this?
Bye!
December 28, 2003 - 11:07 am UTC
how many rows does dual have?
1
you are counting the rows in dual., not in emp or dept.
Why do you want to use dual?
Sami, December 28, 2003 - 11:05 am UTC
Can't you write
select count(*) from emp where deptno=10;
OR
select (select count(*) from emp where deptno=10) cnt from dual;
OK
Ram, December 29, 2003 - 8:17 am UTC
Dear sir,
Thanks for your response.I wanted the value obtained from
the subquery to be passed to the dual table using that
EXISTS condition.Is that possible?
Bye!
December 29, 2003 - 10:41 am UTC
nope, that is not what exists does.
How to invoke one form to another .
Suresh, January 08, 2004 - 11:01 am UTC
Hi tom,
In a database(Forms 6i) User(A) developed one form(say form1), another user(B) to developed the form(say form2) in another schema.
How User A invokes form2(this is User B Form) through form1(User A Form). How to do this ?
It is possible through Open_Form(Parameters),Call_Form(),
New_Form() ? what is the diff. of these 3 built-in's by
Calling the above scenario.(Forms in diif. Schema) ?
Pls reply,
Regards,
Suresh.
January 08, 2004 - 2:49 pm UTC
sorry -- been over 8 years, going on 9 years (in about 2 months) since I've actually used forms.
suggestion:
a) read documentation. I know these three functions are still documented.
b) see otn.oracle.com -> discussion forums after reading the documentation if you still cannot see the way.
Nice
Rosleyn, January 09, 2004 - 12:51 am UTC
Dear Tom,
I need a query in the following format.
Use emp table in scott schema
Format:
Mgr_ename Sub_ename
--------- ---------
Please do reply.
Bye!
January 09, 2004 - 8:29 am UTC
"join"
just "join" emp to emp by emp1.mgr = emp2.empno
Dynamic parameter in query
Natasha, January 13, 2004 - 10:06 pm UTC
How to dynamically select an Arithmatic Operator and join it to our sql query for these below mentioned four column (Booking & Travel dates). Assume that a table has got the structure as
Table EMP
--------
FLTNO PLS_INTEGER,
FLTNAME VARCHAR2(20),
AMOUNT NUMBER(12,2),
BOOKING_FROM_DATE DATE,
BOOKING_TO_DATE DATE,
TRAVEL_FROM_DATE DATE,
TRAVEL_TO_DATE DATE
)
And from the front-end, he passes all date parameters and their respective arithematic operators, then how to properly set the sql statments in SQL query.
Booking_From_Date =
Booking_To_Date <=
Travel_From_Date =
Travel_To_Date <=
Currently I am using a variable by putting some conditions and concatenating the desired arithematic operators (as per the parameter) and then passing it to REF cursor variable which I don't want to do it.
Appreciate, if you can make it simpler.
January 14, 2004 - 1:07 am UTC
there is ONE and only ONE way to have a dynamic predicate like that.....
Using dynamic sql..
If you don't "like" a ref cursor:
open l_cursor for some_query_youve_built;
you can use the dbms_sql package to accomplish the same, albeit with lots more code.
the ref cursor approach is simple and elegant.
Oracle 7.3.4 Gap finder
Luis Soto, January 14, 2004 - 10:49 am UTC
Do Know any way to find the gaps in Oracle 7.3.4
Thanks
Question
PRS, February 06, 2004 - 9:49 am UTC
Hi Tom,
I have built a following view named PS_WSI_USER_BU_VW_FILL.
CREATE OR REPLACE VIEW ps_wsi_user_bu_vw (name1,
person_id,
business_unit,
sales_user_type,
first_name,
last_name,
rb_work_func_id,
wsi_tls_grp_name,
oprid,
setid,
provider_grp_id,
wsi_group
)
AS
SELECT b.first_name || ' ' || b.last_name, a.person_id, e.business_unit,
e.sales_user_type, b.first_name, b.last_name, c.rb_work_func_id,
d.wsi_tls_grp_name, a.oprid, f.setid, f.provider_grp_id, g.descr
FROM psopralias a,
ps_rd_person_name b,
ps_rb_worker_func c,
ps_wsi_rd_prsn_fld d,
ps_rsf_suser e,
ps_rf_grp_member f,
ps_rb_wrk_func_tbl g,
ps_rb_worker h
WHERE b.person_id = a.opraliasvalue(+)
AND a.opraliastype = 'PER'
AND b.primary_ind = 'Y'
AND b.person_id = c.person_id(+)
AND b.person_id = d.person_id
AND b.person_id = e.person_id
AND b.person_id = f.person_id(+)
AND c.rb_work_func_id = g.rb_work_func_id
AND b.person_id = h.person_id
/
All tables are analyzed by percent 35. Indexes are analyzed by 100%. It is using cost based optimizer.
I have following query which comes back in 1 second. It used the right indexes.
SQL> l
1 SELECT
2 fill.name1, fill.person_id, fill.business_unit, fill.sales_user_type,
3 fill.first_name, fill.last_name, fill.rb_work_func_id,
4 fill.wsi_tls_grp_name, fill.oprid, fill.setid, fill.provider_grp_id,
5 fill.wsi_group
6 FROM ps_wsi_user_bu_vw fill
7 WHERE oprid = 'James Mayfield'
8* OR (oprid LIKE '%Branch %' AND provider_grp_id = '100841')
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=745 Card=2 Bytes=348
)
1 0 CONCATENATION
2 1 NESTED LOOPS (Cost=12 Card=1 Bytes=174)
3 2 NESTED LOOPS (Cost=11 Card=1 Bytes=144)
4 3 NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=132)
5 4 FILTER
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS (Cost=6 Card=1 Bytes=81)
8 7 NESTED LOOPS (Cost=8 Card=1 Bytes=113)
9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=63)
10 9 TABLE ACCESS (FULL) OF 'PSOPRALIAS' (Cost=2 Card=1 Bytes=37)
11 9 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RD_ PERSON_NAME' (Cost=3 Card=1 Bytes=26)
12 11 INDEX (RANGE SCAN) OF 'PSDRD_PERSON_NAME' (NON-UNIQUE) (Cost=2 Card=1)
13 8 INDEX (UNIQUE SCAN) OF 'PS_RB_WORKER' (UNIQUE)
14 7 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RSF_SUSER' (Cost=1 Card=1 Bytes=18)
15 14 INDEX (UNIQUE SCAN) OF 'PS_RSF_SUSER' (UNIQUE)
16 6 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RF_GRP_MEMBER' (Cost=2 Card=1 Bytes=22)
17 16 INDEX (RANGE SCAN) OF 'PSARF_GRP_MEMBER' (NON-UNIQUE) (Cost=1 Card=1)
18 4 INDEX (RANGE SCAN) OF 'PS_RB_WORKER_FUNC' (UNIQUE)(Cost=1 Card=1 Bytes=19)
19 3 TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_RD_PRSN_FLD' (Cost=2 Card=1 Bytes=12)
20 19 INDEX (UNIQUE SCAN) OF 'PS_WSI_RD_PRSN_FLD' (UNIQUE) (Cost=1 Card=1)
21 2 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RB_WRK_FUNC_TBL'(Cost=1 Card=1 Bytes=30)
22 21 INDEX (UNIQUE SCAN) OF 'PS_RB_WRK_FUNC_TBL' (UNIQUE)
23 1 NESTED LOOPS (Cost=12 Card=1 Bytes=174)
24 23 NESTED LOOPS (Cost=11 Card=1 Bytes=144)
25 24 NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=132)
26 25 NESTED LOOPS (Cost=8 Card=1 Bytes=113)
27 26 FILTER
28 27 NESTED LOOPS (OUTER)
29 28 NESTED LOOPS (Cost=6 Card=1 Bytes=81)
30 29 NESTED LOOPS (Cost=5 Card=1 Bytes=63)
31 30 TABLE ACCESS (BY INDEX ROWID) OF 'PSOPRALIAS' (Cost=2 Card=1 Bytes=37)
32 31 INDEX (UNIQUE SCAN) OF 'PS_PSOPRALIAS' (UNIQUE) (Cost=1 Card=4526)
33 30 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RD_PERSON_NAME' (Cost=3 Card=1 Bytes=26)
34 33 INDEX (RANGE SCAN) OF 'PSDRD_PERSON_NAME' (NON-UNIQUE) (Cost=2 Card=1)
35 29 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RSF_SUSER' (Cost=1 Card=1 Bytes=18)
36 35 INDEX (UNIQUE SCAN) OF 'PS_RSF_SUSER' (UNIQUE)
37 28 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RF_GRP_MEMBER' (Cost=2 Card=1 Bytes=22)
38 37 INDEX (RANGE SCAN) OF 'PSARF_GRP_MEMBER' (NON-UNIQUE) (Cost=1 Card=1)
39 26 INDEX (UNIQUE SCAN) OF 'PS_RB_WORKER' (UNIQUE)
40 25 INDEX (RANGE SCAN) OF 'PS_RB_WORKER_FUNC' (UNIQUE)(Cost=1 Card=1 Bytes=19)
41 24 TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_RD_PRSN_FLD' (Cost=2 Card=1 Bytes=12)
42 41 INDEX (UNIQUE SCAN) OF 'PS_WSI_RD_PRSN_FLD' (UNIQUE) (Cost=1 Card=1)
43 23 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RB_WRK_FUNC_TBL' (Cost=1 Card=1 Bytes=30)
44 43 INDEX (UNIQUE SCAN) OF 'PS_RB_WRK_FUNC_TBL' (UNIQUE)
SQL> spool off
Now I have following query in which I add extra where clause with OR condition and it takes more than 2 minutes. It changes the
entire explain plan. It does not use the index on PS_RD_PERSON_NAME which has more than 3million rows.
SQL> SELECT
2 fill.name1, fill.person_id, fill.business_unit, fill.sales_user_type,
3 fill.first_name, fill.last_name, fill.rb_work_func_id,
4 fill.wsi_tls_grp_name, fill.oprid, fill.setid, fill.provider_grp_id,
5 fill.wsi_group
6 FROM ps_wsi_user_bu_vw fill
7 WHERE oprid = 'James Mayfield'
8 OR (oprid LIKE '%Branch %' AND provider_grp_id = '100841')
9 OR provider_grp_id = '100841'
10 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6947 Card=1 Bytes=174)
1 0 NESTED LOOPS (Cost=6947 Card=1 Bytes=174)
2 1 NESTED LOOPS (OUTER) (Cost=6946 Card=1 Bytes=144)
3 2 NESTED LOOPS (Cost=6945 Card=1 Bytes=125)
4 3 NESTED LOOPS (Cost=6943 Card=1 Bytes=113)
5 4 NESTED LOOPS (OUTER) (Cost=6925 Card=9 Bytes=684)
6 5 HASH JOIN (Cost=6907 Card=9 Bytes=486)
7 6 TABLE ACCESS (FULL) OF 'PS_RSF_SUSER' (Cost=7 Card=3881 Bytes=69858)
8 6 NESTED LOOPS (Cost=6898 Card=7962 Bytes=286632)
9 8 TABLE ACCESS (FULL) OF 'PS_RD_PERSON_NAME' ( Cost=6898 Card=3387373 Bytes=88071698)
10 8 INDEX (UNIQUE SCAN) OF 'PS_RB_WORKER' (UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RF_GRP_MEMBER' (Cost=2 Card=1 Bytes=22)
12 11 INDEX (RANGE SCAN) OF 'PSARF_GRP_MEMBER' (NON-UNIQUE) (Cost=1 Card=1)
13 4 TABLE ACCESS (BY INDEX ROWID) OF 'PSOPRALIAS' (Cost=2 Card=1 Bytes=37)
14 13 INDEX (RANGE SCAN) OF 'PSBPSOPRALIAS' (NON-UNIQUE) (Cost=1 Card=1)
15 3 TABLE ACCESS (BY INDEX ROWID) OF 'PS_WSI_RD_PRSN_FLD' (Cost=2 Card=1 Bytes=12)
16 15 INDEX (UNIQUE SCAN) OF 'PS_WSI_RD_PRSN_FLD' (UNIQUE) (Cost=1 Card=1)
17 2 INDEX (RANGE SCAN) OF 'PS_RB_WORKER_FUNC' (UNIQUE) (Cost=1 Card=1 Bytes=19)
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RB_WRK_FUNC_TBL' (Cost=1 Card=1 Bytes=30)
19 18 INDEX (UNIQUE SCAN) OF 'PS_RB_WRK_FUNC_TBL' (UNIQUE)
SQL> spool off
Can you please show how to resolve this 2nd query? It is just optimized is unsing a different explain plan.
Thanks for your help.
PRS
February 07, 2004 - 1:13 pm UTC
quick comment:
WHERE b.person_id = a.opraliasvalue(+)
AND a.opraliastype = 'PER'
hmmm, if a.* is "made up", a.* will all be null and a.opraliastype = 'PER' will never be true (or false :). Hence, the (+) on A is not only "not relevant", it only stops the optimizer from considering other plans...
Anytime you see an outer join, ask yourself "why, do i really need it, and if so, why do i really really need it". In this case, you obviously do not need it!
Hmm, another one:
AND b.person_id = c.person_id(+)
...
AND c.rb_work_func_id = g.rb_work_func_id
if c.* is made up, c.rb_work_func_id will be null and that'll never be equal to g.rb_work_func_id so -- again, lose the outer join!
Lets start there first.
Changed
PRS, February 09, 2004 - 9:24 am UTC
Hi Tom,
I totally agree with your suggestion on outer join. I removed all outer join and recreated the view as shown below.
create or replace view ps_wsi_user_bu_vw as
SELECT b.first_name || ' ' || b.last_name name1, a.person_id, e.business_unit,
e.sales_user_type, b.first_name, b.last_name, c.rb_work_func_id,
d.wsi_tls_grp_name, a.oprid, f.setid, f.provider_grp_id, g.descr wsi_group
FROM psopralias a,
ps_rd_person_name b,
ps_rb_worker_func c,
ps_wsi_rd_prsn_fld d,
ps_rsf_suser e,
ps_rf_grp_member f,
ps_rb_wrk_func_tbl g,
ps_rb_worker h
WHERE b.person_id = a.opraliasvalue
AND a.opraliastype = 'PER'
AND b.primary_ind = 'Y'
AND b.person_id = c.person_id
AND b.person_id = d.person_id
AND b.person_id = e.person_id
AND b.person_id = f.person_id
AND c.rb_work_func_id = g.rb_work_func_id
AND b.person_id = h.person_id
But the explain plan remains identical for the query 2. There is no change in the plan.
I do not understand why optimizer is doing this way. Can you help?
Thanks,
PRS
February 09, 2004 - 9:49 am UTC
if you look at the two predicates, they are very very very very different. Might not seem so to you -- but they are:
7 WHERE oprid = 'James Mayfield'
8 OR (oprid LIKE '%Branch %' AND provider_grp_id = '100841')
10 /
versus
7 WHERE oprid = 'James Mayfield'
8 OR (oprid LIKE '%Branch %' AND provider_grp_id = '100841')
9 OR provider_grp_id = '100841'
10 /
now, you do understand "lots of tables", "no idea of what indexes are where", "big query" makes this a bit hard
but what about just union all'ing
where oprid = 'xxxxx'
with
where provider_grp_id = '100841'
as the other predicate piece is "already covered" and just confusing the issue.
Sorry
PRS, February 09, 2004 - 9:54 am UTC
Hi Tom,
I wrongly stated my answer in the previous reply. Sorry for that.
But When I removed all outer joins 2nd query works fine.
But I cannot remove an outer join for following condition.
AND b.person_id = f.person_id(+)
As long as this outer join is there it is doing a full table scan on PS_RD_PERSON_NAME(3+ million rows) and plan remains almost identical and query takes 2 minutes. So I do not know what's going on.
Is their any Standard pll or package available to generate web reports in Forms??
rajkumar, April 01, 2004 - 1:18 am UTC
hi tom,
Is their any standard PLL or Package available in
Forms 4.5/5.0/6i(Developer 2000) to generate the
excel-report in web by capturing the form values at runtime ?
eg : i run dept-Form based on dept table, (on button click) at runtime to capture all the form values(deptno-10,dname-Accounts,loc-India) including lables(deptno,dname,loc) and these values to be displayed in IE-browser in excel-format(grids)in the web? how to achieve this scenario ?
Any Information related to above is their any Standard PLL available ? Pls posted immd..
Thanks & Regards,
rajkumar.
April 01, 2004 - 10:03 am UTC
don't know -- good question for the forms discussion forums on otn.oracle.com
I haven't touched forms since march, 1995 myself (when this web thing came into being...)
How to make rows as columns ??
rajkumar, April 07, 2004 - 12:32 am UTC
hi tom,
How to make rows as columns ? ie, suppose i have a Questions table(Q.No - Primary key) like:
Q.No(PK) Question
1 Q1
2 Q2
..........
and MultiChoices table(Q.No - Foreign key) like :
Q.No(FK) Choices
1 C1
1 C2
1 C3
1 C4
2 C1
2 C2
2 C3
2 C4
.............
based on these two tables to generate one more table Ques_Choices like :
Q.No Choice1 Choice2 Choice3 Choice4
1 C1 C2 C3 C4
2 C1 C2 C3 C4
...........................................
How to generate this table(Write the Query)? Pls Posted Immd.. any information related to this ..make rows as Columns and Columns as rows ????
Thanks & Regards,
rajkumar.
April 07, 2004 - 9:07 am UTC
I think I answer this one about 3 times a day.
search this site for
PIVOT
Question
PRS, August 26, 2004 - 12:48 pm UTC
I want to retrieve the data from a table where each person id amount is not nullified. (i.e There is no matching
negative value for the given positive value)
I have following data
create table t1 (person_id number(10),date_created date,amount number(5,2))
insert into t1 values(1,sysdate-365,1);
insert into t1 values(1,sysdate-365,-1);
insert into t1 values(1,sysdate-300,3.25);
insert into t1 values(1,sysdate-365,-3.25);
insert into t1 values(1,sysdate-200,1);
insert into t1 values(1,sysdate-200,1.25);
insert into t1 values(2,sysdate-365,1);
insert into t1 values(2,sysdate-365,-1);
insert into t1 values(2,sysdate-300,2);
insert into t1 values(2,sysdate-300,-2);
insert into t1 values(2,sysdate-200,3);
insert into t1 values(2,sysdate-200,-3);
insert into t1 values(3,sysdate-365,1);
insert into t1 values(3,sysdate-365,-1);
insert into t1 values(3,sysdate-300,1.25);
insert into t1 values(3,sysdate-300,-1.25);
insert into t1 values(3,sysdate-200,2.23);
insert into t1 values(3,sysdate-200,1);
commit
Output should be
Person ID Date Created Amount
1 2/8/2004 1
1 2/8/2004 1.25
3 2/8/2004 2.23
3 2/8/2004 1
Thanks,
PRS
August 26, 2004 - 3:17 pm UTC
why pick the 2/8/2004 for person id = 1, why not 27-aug-2003?
seems rather "arbitrary"
but here we go -- I'll build this one.
<b>First, we assign row_number after partitioning the data by person_id and amount -- ordering by date (tend to keep the "last date")
this will take person_id = 1, amount = 1 and assign the numbers 1, 2, 3, ... to them -- sequence all of the "1"s for that person and do the same for -1 and so on:
</b>
ops$tkyte@ORA9IR2> select *
2 from (
3 select person_id, date_created, amount,
4 row_number() over (partition by person_id, amount order by date_created) rn
5 from t1
6 )
7 order by person_id, abs(amount), rn
8 /
PERSON_ID DATE_CREA AMOUNT RN
---------- --------- ---------- ----------
1 27-AUG-03 -1 1
1 27-AUG-03 1 1
1 08-FEB-04 1 2
1 08-FEB-04 1.25 1
1 27-AUG-03 -3.25 1
1 31-OCT-03 3.25 1
2 27-AUG-03 -1 1
2 27-AUG-03 1 1
2 31-OCT-03 -2 1
2 31-OCT-03 2 1
2 08-FEB-04 -3 1
2 08-FEB-04 3 1
3 27-AUG-03 -1 1
3 27-AUG-03 1 1
3 08-FEB-04 1 2
3 31-OCT-03 -1.25 1
3 31-OCT-03 1.25 1
3 08-FEB-04 2.23 1
18 rows selected.
<b>Now we have that. We partition by person_id and the ABS(AMOUNT) -- treating 1 and -1 as the "same" -- and that RN. So, it groups the first 1/-1 together (person_id = 1, abs(amount)=1 and rn=1 will either be a singleton or a pair..</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select person_id, date_created, amount, rn,
2 lag(amount) over (partition by person_id, abs(amount), rn order by amount) last_amt
3 from (
4 select person_id, date_created, amount,
5 row_number() over (partition by person_id, amount order by date_created) rn
6 from t1
7 )
8 /
PERSON_ID DATE_CREA AMOUNT RN LAST_AMT
---------- --------- ---------- ---------- ----------
1 27-AUG-03 -1 1
1 27-AUG-03 1 1 -1
1 08-FEB-04 1 2
1 08-FEB-04 1.25 1
1 27-AUG-03 -3.25 1
1 31-OCT-03 3.25 1 -3.25
2 27-AUG-03 -1 1
2 27-AUG-03 1 1 -1
2 31-OCT-03 -2 1
2 31-OCT-03 2 1 -2
2 08-FEB-04 -3 1
2 08-FEB-04 3 1 -3
3 27-AUG-03 -1 1
3 27-AUG-03 1 1 -1
3 08-FEB-04 1 2
3 31-OCT-03 -1.25 1
3 31-OCT-03 1.25 1 -1.25
3 08-FEB-04 2.23 1
18 rows selected.
<b>now, if we filter out just amount>0 after pairing:</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
2 from (
3 select person_id, date_created, amount, rn,
4 lag(amount) over (partition by person_id, abs(amount), rn order by amount) last_amt
5 from (
6 select person_id, date_created, amount,
7 row_number() over (partition by person_id, amount order by date_created) rn
8 from t1
9 )
10 )
11 where amount > 0
12 /
PERSON_ID DATE_CREA AMOUNT RN LAST_AMT
---------- --------- ---------- ---------- ----------
1 27-AUG-03 1 1 -1
1 08-FEB-04 1 2
1 08-FEB-04 1.25 1
1 31-OCT-03 3.25 1 -3.25
2 27-AUG-03 1 1 -1
2 31-OCT-03 2 1 -2
2 08-FEB-04 3 1 -3
3 27-AUG-03 1 1 -1
3 08-FEB-04 1 2
3 31-OCT-03 1.25 1 -1.25
3 08-FEB-04 2.23 1
11 rows selected.
<b>we are almost there.... just keep "last_amount is NULL" and we got it I think</b>
ops$tkyte@ORA9IR2> select *
2 from (
3 select person_id, date_created, amount, rn,
4 lag(amount) over (partition by person_id, abs(amount), rn order by amount) last_amt
5 from (
6 select person_id, date_created, amount,
7 row_number() over (partition by person_id, amount order by date_created) rn
8 from t1
9 )
10 )
11 where amount > 0
12 and last_amt is null
13 /
PERSON_ID DATE_CREA AMOUNT RN LAST_AMT
---------- --------- ---------- ---------- ----------
1 08-FEB-04 1 2
1 08-FEB-04 1.25 1
3 08-FEB-04 1 2
3 08-FEB-04 2.23 1
Another one using Outer Join
Logan Palanisamy, August 26, 2004 - 8:10 pm UTC
Tom,
Here is another solution using Outer Join. Performance wise, how will it compare with your analytics solution
SQL> l
1 select * from (
2 select a.*, b.amount bamount
3 from (select * from t1 where amount > 0) a
4 full outer join
5 (select * from t1 where amount < 0) b
6 on a.person_id = b.person_id
7 and trunc(a.date_created) = trunc(b.date_created)
8 and a.amount = -b.amount)
9 where bamount is null
10* order by person_id
SQL> /
PERSON_ID DATE_CREA AMOUNT BAMOUNT
---------- --------- ---------- ----------
1 08-FEB-04 1
1 08-FEB-04 1.25
3 08-FEB-04 2.23
3 08-FEB-04 1
August 26, 2004 - 8:31 pm UTC
your's makes an assumption mine did not, that the date_created would be the same.
Another shot with Full Outer Join with no date constraint
Logan Palanisamy, August 27, 2004 - 1:16 am UTC
Tom,
How does it compare, now that I have removed the date restriction?
SQL> select person_id, date_created, amount from
2 (select p.*, n.amount namount
3 from
4 (select person_id, amount, date_created,
5 row_number() over (partition by person_id, amount order by date_created) rn from t1 where amount > 0 ) p
6 full outer join
7 (select person_id, amount, date_created,
8 row_number() over (partition by person_id, amount order by date_created) rn from t1 where amount < 0 ) n
9 on p.person_id = n.person_id
10 and p.rn = n.rn
11 and p.amount = -n.amount)
12 where namount is null
13 order by person_id, amount;
PERSON_ID DATE_CREA AMOUNT
---------- --------- ----------
1 08-FEB-04 1
1 08-FEB-04 1.25
3 08-FEB-04 1
3 08-FEB-04 2.23
August 27, 2004 - 7:41 am UTC
benchmark it and let us know?
query in matrix format
friend, August 27, 2004 - 8:47 am UTC
hai,
i want a query o/p as shown in this format
street-a street-b street-c
a b c a b c a b c
quantity 10 20 30 40 99 34 12 34 56
is this format possible with sql query ???????
thanks in advance
August 27, 2004 - 8:55 am UTC
virtually anything is possible...
but without data, nothing is probable.
you see, we have no idea what "a", "b", "c" are or what your table looks like...
create table ....
insert into....
insert into.....
Your approach wins hands down
Logan Palanisamy, August 27, 2004 - 2:12 pm UTC
Tom,
Your approach wins like it does most of the time. Here is the test.
SQL> select *
2 from (
3 select person_id, date_created, amount, rn,
4 lag(amount) over (partition by person_id, abs(amount), rn order by
5 amount) last_amt
6 from (
7 select person_id, date_created, amount,
8 row_number() over (partition by person_id, amount order by
9 date_created) rn
10 from t1
11 )
12 )
13 where amount > 0
14 and last_amt is null;
PERSON_ID DATE_CREA AMOUNT RN LAST_AMT
---------- --------- ---------- ---------- ----------
1 08-FEB-04 1 2
1 08-FEB-04 1.25 1
3 08-FEB-04 1 2
3 08-FEB-04 2.23 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT)
3 2 VIEW
4 3 WINDOW (SORT)
5 4 TABLE ACCESS (FULL) OF 'T1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
582 bytes sent via SQL*Net to client
423 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
SQL> select person_id,
2 date_created,
3 amount from
4 (select p.*,
5 n.person_id nperson_id, n.date_created ndate_created, n.amount namount
6 from
7 (select person_id, amount, date_created,
8 row_number() over (partition by person_id, amount order by date_created) rn
9 from t1 where amount > 0 ) p
10 full outer join
11 (select person_id, amount, date_created,
12 row_number() over (partition by person_id, amount order by date_created) rn
13 from t1 where amount < 0 ) n
14 on p.person_id = n.person_id
15 and p.amount = -n.amount
16 and p.rn = n.rn)
17 where namount is null
18 order by person_id, amount;
PERSON_ID DATE_CREA AMOUNT
---------- --------- ----------
1 08-FEB-04 1
1 08-FEB-04 1.25
3 08-FEB-04 1
3 08-FEB-04 2.23
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=5 Bytes=240)
1 0 SORT (ORDER BY) (Cost=45 Card=5 Bytes=240)
2 1 VIEW (Cost=34 Card=5 Bytes=240)
3 2 UNION-ALL
4 3 FILTER
5 4 HASH JOIN (OUTER)
6 5 VIEW (Cost=15 Card=4 Bytes=192)
7 6 WINDOW (SORT) (Cost=15 Card=4 Bytes=140)
8 7 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=4 B
ytes=140)
9 5 VIEW (Cost=15 Card=4 Bytes=156)
10 9 WINDOW (SORT) (Cost=15 Card=4 Bytes=140)
11 10 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=4 B
ytes=140)
12 3 FILTER
13 12 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=3
5)
14 12 FILTER
15 14 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes
=35)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
444 bytes sent via SQL*Net to client
423 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
Strange behavior of Full Outer Joins when using inline views
Logan Palanisamy, August 27, 2004 - 3:20 pm UTC
Tom,
I thought in Full Outer Joins, the order of the tables in the FROM clause don't matter. For example
select t1.*, t2.*
from t1 full outer join t2
on t1.c1 = t2.c1
is the same as
select t1.*, t2.*
from t2 full outer join t1
on t1.c1 = t2.c1
But it seems to be true only if t1 and t2 are tables, not inline views.
Here is the scenario:
SQL> -- Outer Join Query 1. (With Inline Views)
SQL> select p.*, n.*
2 from
3 (select person_id, amount, date_created,
4 row_number() over (partition by person_id, amount order by date_created) rn
5 from t1 where amount > 0 ) p
6 full outer join
7 (select person_id, amount, date_created,
8 row_number() over (partition by person_id, amount order by date_created) rn
9 from t1 where amount < 0 ) n
10 on p.person_id = n.person_id
11 and p.amount = -n.amount
12 and p.rn = n.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
3 2.23 08-FEB-04 1
3 1 08-FEB-04 2
1 -1 27-AUG-03
1 -3.25 27-AUG-03
2 -1 27-AUG-03
2 -2 31-OCT-03
2 -3 08-FEB-04
3 -1 27-AUG-03
3 -1.25 31-OCT-03
18 rows selected.
SQL>
SQL> -- Outer Join Query 2. (With Inline Views. Same as 1 except n and p are just reversed)
SQL> select p.*, n.*
2 from
3 (select person_id, amount, date_created,
4 row_number() over (partition by person_id, amount order by date_created) rn
5 from t1 where amount < 0 ) n
6 full outer join
7 (select person_id, amount, date_created,
8 row_number() over (partition by person_id, amount order by date_created) rn
9 from t1 where amount > 0 ) p
10 on p.person_id = n.person_id
11 and p.amount = -n.amount
12 and p.rn = n.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
1 1 27-AUG-03
1 3.25 27-AUG-03
1 1 08-FEB-04
1 1.25 08-FEB-04
2 1 27-AUG-03
2 2 31-OCT-03
2 3 08-FEB-04
3 1 27-AUG-03
3 1.25 31-OCT-03
3 2.23 08-FEB-04
3 1 08-FEB-04
18 rows selected.
SQL>
SQL> -- Tables corresponding to the inline views
SQL> drop table p;
Table dropped.
SQL> create table p as
2 select person_id, amount, date_created,
3 row_number() over (partition by person_id, amount order by date_created) rn
4 from t1 where amount > 0 ;
Table created.
SQL>
SQL> drop table n;
Table dropped.
SQL> create table n as
2 select person_id, amount, date_created,
3 row_number() over (partition by person_id, amount order by date_created) rn
4 from t1 where amount < 0;
Table created.
SQL>
SQL> select * from p order by person_id, amount;
PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- ---
1 1 27-AUG-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
1 3.25 27-AUG-03 1
2 1 27-AUG-03 1
2 2 31-OCT-03 1
2 3 08-FEB-04 1
3 1 27-AUG-03 1
3 1 08-FEB-04 2
3 1.25 31-OCT-03 1
3 2.23 08-FEB-04 1
11 rows selected.
SQL> select * from n order by person_id, amount;
PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- ---
1 -3.25 27-AUG-03 1
1 -1 27-AUG-03 1
2 -3 08-FEB-04 1
2 -2 31-OCT-03 1
2 -1 27-AUG-03 1
3 -1.25 31-OCT-03 1
3 -1 27-AUG-03 1
7 rows selected.
SQL>
SQL> -- Outer Join Query 3. (With equivalent tables)
SQL> select p.*, n.*
2 from p full outer join n
3 on p.person_id = n.person_id
4 and p.amount = -n.amount
5 and p.rn = n.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
3 2.23 08-FEB-04 1
3 1 08-FEB-04 2
11 rows selected.
SQL>
SQL> -- Outer Join Query 4. (With equivalent tables. Same as 3 except n and p are just reversed)
SQL> select p.*, n.*
2 from n full outer join p
3 on p.person_id = n.person_id
4 and p.amount = -n.amount
5 and p.rn = n.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
3 2.23 08-FEB-04 1
3 1 08-FEB-04 2
11 rows selected.
SQL>
Notes:
Q1, Q2 use inline views
Q3, Q4 use equivalent tables
Q2 is the same as Q1 except for the reversal in the FROM clause.
Q3 is the same as Q1 except that Q3 uses equivalent tables
Q4 is the same as Q3 except for the reversal in the FROM clause.
Observations:
Output of Q1 and Q2 are different
Output of Q3 and Q1 are different
Output of Q4 is the same as Q3.
Questions:
Shouldn't the output of all the four queries be exactly the same?
Why are the outputs of Q1 and Q2 different?
Why are the outputs of Q3 and Q1 different?
Is it because of the query merge when using inline views?
Is there a hint or something which tells the optimizer not to merge?
Just curious.
August 27, 2004 - 3:33 pm UTC
yes, that would be "a product issue" apparently.
ops$tkyte@ORA9IR2> select * from t1;
PERSON_ID DATE_CREA AMOUNT
---------- --------- ----------
1 28-AUG-03 1
1 28-AUG-03 -1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select p.*, n.*
2 from
3 (select person_id, amount, date_created,
4 row_number() over (partition by person_id, amount order by date_created) rn
5 from t1 where amount > 0 ) p
6 full outer join
7 (select person_id, amount, date_created,
8 row_number() over (partition by person_id, amount order by date_created) rn
9 from t1 where amount < 0 ) n
10 on p.person_id = n.person_id
11 and p.amount = -n.amount
12 and p.rn = n.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
1 1 28-AUG-03 1 1 -1 28-AUG-03 1
1 -1 28-AUG-03
<b>is sufficient to show the problem.</b>
I'll file a bug.... wait, turns out I already did.
workaround I found then was to use WITH subquery factoring and it works.
one more debug test case
Gabe, August 27, 2004 - 4:19 pm UTC
Seems to have nothing to do with the analytics ... just the full outer join with inline views (well, maybe something else as well!!) ...
flip@flop> select p.*, n.*
2 from ( select rownum x,person_id, amount, date_created
3 from t1
4 where amount > 0
5 ) p
6 full outer join
7 ( select rownum x,person_id, amount, date_created
8 from t1
9 where amount < 0
10 ) n
11 on p.person_id = n.person_id
12 and p.amount = -n.amount
13 ;
ERROR:
ORA-03113: end-of-file on communication channel
It works when both rownums (not just one) are taken out of the inline views.
SQL*Plus: Release 9.2.0.1.0
Oracle9i Enterprise Edition Release 9.2.0.4.0
WITH Sub-Query-Factoring workaround for Full Outer Join
Logan Palanisamy, August 27, 2004 - 4:31 pm UTC
Tom,
The "With Sub-Query-Factoring" workaround does indeed work in the place of inline views for Full Outer Joins.
SQL> WITH p1 as (select person_id, amount, date_created,
2 row_number() over (partition by person_id, amount order by date_created) rn
3 from t1 where amount > 0 ),
4 n1 as (select person_id, amount, date_created,
5 row_number() over (partition by person_id, amount order by date_created) rn
6 from t1 where amount < 0 )
7 select p1.*, n1.*
8 from p1 full outer join n1
9 on p1.person_id = n1.person_id
10 and p1.amount = -n1.amount
11 and p1.rn = n1.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
3 2.23 08-FEB-04 1
3 1 08-FEB-04 2
11 rows selected.
SQL>
SQL> WITH p1 as (select person_id, amount, date_created,
2 row_number() over (partition by person_id, amount order by date_created) rn
3 from t1 where amount > 0 ),
4 n1 as (select person_id, amount, date_created,
5 row_number() over (partition by person_id, amount order by date_created) rn
6 from t1 where amount < 0 )
7 select p1.*, n1.*
8 from n1 full outer join p1
9 on p1.person_id = n1.person_id
10 and p1.amount = -n1.amount
11 and p1.rn = n1.rn;
PERSON_ID AMOUNT DATE_CREA RN PERSON_ID AMOUNT DATE_CREA RN
--------- ---------- --------- --- --------- ---------- --------- ---
1 1 27-AUG-03 1 1 -1 27-AUG-03 1
1 3.25 27-AUG-03 1 1 -3.25 27-AUG-03 1
2 1 27-AUG-03 1 2 -1 27-AUG-03 1
2 2 31-OCT-03 1 2 -2 31-OCT-03 1
2 3 08-FEB-04 1 2 -3 08-FEB-04 1
3 1 27-AUG-03 1 3 -1 27-AUG-03 1
3 1.25 31-OCT-03 1 3 -1.25 31-OCT-03 1
1 1 08-FEB-04 2
1 1.25 08-FEB-04 1
3 2.23 08-FEB-04 1
3 1 08-FEB-04 2
11 rows selected.
OK
Raju, August 29, 2004 - 11:06 am UTC
Hello Tom,
Are there any other ways that the following query can be put?
SQL> select * from emp where deptno = 10 and job in (select job from emp where deptno = (select
2 deptno from dept where dname = upper('sales')) )
3 /
August 29, 2004 - 12:01 pm UTC
yes, lots. try some....
OK
Raju, August 30, 2004 - 1:36 am UTC
Dear Tom,
I find it difficult to arrive it.Could you please provide
some ways?
Bye!
August 30, 2004 - 8:33 am UTC
looks like homework, since if this was "work", you'd be done -- you have the answer.
hints for you to research:
o "where exists"
o inline views and joins.
row_number()
A reader, October 10, 2004 - 9:57 pm UTC
Welcome back!
I need to order a set of rows by 5 different criteria, some ASC, some DESC and get the first row for each pk.
row_number() is a perfect fit here.
select * from (
select pk,row_number() over (partition by pk order by c1,c2 desc,c3,c4,c5 desc) rn from Q
) where rn=1;
Question:
What kind of optimization does the CBO do here because of the "rn=1" predicate?
Does it fully materialize the query Q above before assigning the row_numbers() or does it make use of the fact that I need only the first row in each partition to reduce the work?
Thanks
October 11, 2004 - 7:36 am UTC
it will basically materialize the inner query and then just keep the first row from each partition.
partition by "pk" looks "strange" -- in fact -- that query would be the same as
select distinct pk, 1 rn from q;
as coded -- so there should be more to it?
How to execute the Job on week-day's only.....????
raja, November 23, 2004 - 1:52 pm UTC
Hi tom,
1. Using triggers, the Job is executed only on
monday to friday, not execute on saturday
and sunday,
where to write this condition in trigger's....??
the job is executed only "Mon to Fri"
and how to do this ....??
2. If the Select Query takes more time to run...
How to do the Query tunning...what are the step's
to follow ???
3. what is fine grain policy future....?
4. when u r using Index-organized tables (related
to Index's ?) at what king of scenario's ??
5. how to call the C-Programs in pl/sql ?
Tx,
raja.
November 23, 2004 - 2:50 pm UTC
use an interval of
case when to_char(sysdate+1,'DY') in ('SAT','SUN')
then sysdate+3
else sysdate+1
end
or whatever you need to do to sysdate to get it to be the next "time"
2) understand all of the tools available to you -- the sql language in full, the functions (analytics etc), have some amount of experience, understand how and why you index. I guess I would start with the concepts guide (i always do....)
I have another suggestion -- I have a book for you -- "Expert One on One Oracle" -- i cover these topics (although -- read the concepts manual, freely available on otn.oracle.com -- it too covers these basic features of the database)
How to use rownum in place of row_number()
Stalin, January 11, 2005 - 8:27 pm UTC
Hi Tom,
The query i wrote to pull the latest 1000 log entries for each account works great with smaller dataset but runs for hours on a 35million rows table.
here goes the query :
SELECT log_id, creation_date, account_id, ....
FROM (SELECT l.*, row_number() OVER (PARTITION BY l.account_id ORDER BY l.creation_date DESC) rn
FROM logs l) x
WHERE x.rn <= 1000
plan output :
----------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35M| 260G| | 2902K|
|* 1 | VIEW | | 35M| 260G| | 2902K|
|* 2 | WINDOW SORT PUSHED RANK| | 35M| 4473M| 12G| 2902K|
| 3 | TABLE ACCESS FULL | LOGS | 35M| 4473M| | 115K|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------
1 - filter("X"."RN"<=1000)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "L"."ACCOUNT_ID" ORDER BY
"L"."CREATION_DATE" DESC )<=1000)
It estimates 12G temp space for sorting and 38 hrs to complete from longops. I was wondering how to rewrite this query to use the advantage of rownum where it stops sorting once it reaches 1k rows.
Thanks,
Stalin
January 12, 2005 - 8:36 am UTC
you need the latest 1,000 log entries BY ACCOUNT.
you cannot use rownum for that (it is doing the pushed rank there -- it is stopping).
but if you have lots of accounts, thisis a huge result set -- do you see it is estimating that it'll get 35m rows as output there?
what is your sort sizes here, 35mill rows on a machine today shouldn't take very long at all. I know this is not exactly the same as your example (you give us no real idea the number of accounts and the average rows/account which would be pretty important) but -- this is one a rather small (as compared to a server) desktop pc:
big_table@ORA9IR2> delete from plan_table;
4 rows deleted.
big_table@ORA9IR2> explain plan for
2 select *
3 from ( select t.*, row_number() over (partition by owner order by created) rn
4 from big_table t
5 )
6 where rn <= 1000
7 /
Explained.
big_table@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35M| 5140M| | 935K|
|* 1 | VIEW | | 35M| 5140M| | 935K|
|* 2 | WINDOW SORT PUSHED RANK| | 35M| 3337M| 8965M| 935K|
| 3 | TABLE ACCESS FULL | BIG_TABLE | 35M| 3337M| | 76995 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_001"."RN"<=1000)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."OWNER" ORDER BY
"T"."CREATED")<=1000)
Note: cpu costing is off
18 rows selected.
big_table@ORA9IR2>
big_table@ORA9IR2> set timing on
big_table@ORA9IR2> set autotrace traceonly
big_table@ORA9IR2> select *
2 from ( select t.*, row_number() over (partition by owner order by created) rn
3 from big_table t
4 )
5 where rn <= 1000
6 /
22000 rows selected.
Elapsed: 00:04:44.37
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=935319 Card=35000000 Bytes=5390000000)
1 0 VIEW (Cost=935319 Card=35000000 Bytes=5390000000)
2 1 WINDOW (SORT PUSHED RANK) (Cost=935319 Card=35000000 Bytes=3500000000)
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=76995 Card=35000000 Bytes=3500000000)
Statistics
----------------------------------------------------------
0 recursive calls
471 db block gets
507329 consistent gets
588968 physical reads
720 redo size
886470 bytes sent via SQL*Net to client
16625 bytes received via SQL*Net from client
1468 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
22000 rows processed
big_table is just a copy of all_objects over and over and over to get to 35m rows. Seems it is a bit skinnier than your table (and the tmpspc guess -- is just that, a high end guess).
but -- it was only a couple of minutes.
sooo, what is your workarea size policy, pga aggregate target, sort area size set to.
Stalin, January 12, 2005 - 3:37 pm UTC
workarea_policy was set to auto, and PGA was set to 50m. however, after increasing PGA to 250m, i could get the same query to run in ~26mins with 8G of tempspace.
SELECT log_id, creation_date, account_id, ....
FROM (SELECT l.*, row_number() OVER (PARTITION BY l.account_id ORDER BY l.creation_date DESC) rn
FROM logs l) x
WHERE x.rn <= 1000
/
3606781 rows selected.
Elapsed: 00:25:48.16
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1246958 Card=3580870
0 Bytes=279773373100)
1 0 VIEW (Cost=1246958 Card=35808700 Bytes=279773373100)
2 1 WINDOW (SORT PUSHED RANK) (Cost=1246958 Card=35808700 By
tes=4690939700)
3 2 TABLE ACCESS (FULL) OF 'LOGS' (Cost
=115964 Card=35808700 Bytes=4690939700)
Statistics
----------------------------------------------------------
0 recursive calls
2788 db block gets
773627 consistent gets
1986398 physical reads
0 redo size
304203179 bytes sent via SQL*Net to client
2645475 bytes received via SQL*Net from client
240454 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
3606781 rows processed
January 12, 2005 - 4:05 pm UTC
If this is a batch query you have to do from time to time during off time (non-peak), you could consider
a) alter session to set the workarea policy to manual
b) alter session to up your sort area size to something really nice and big (you don't have to share the memory if this is off peak, just use it all)
Stalin, January 12, 2005 - 4:56 pm UTC
Thanks tom.
If i'd to get the latest 1,000 log entries BY ACCOUNT within last 30days, i should be able to get off by using range preceding right. I hope that makes a huge difference.
SELECT log_id, creation_date, account_id, ....
FROM (SELECT l.*, row_number() OVER (PARTITION BY l.account_id ORDER BY l.creation_date DESC range 30 preceding) rn
FROM logs l) x
WHERE x.rn <= 1000
January 12, 2005 - 6:10 pm UTC
that is not semantically equivalent -- if it even worked :)
not sure what "row_number" would mean with a range clause, probably that is why it only does partition and order by....
you'd be "whereing"
select ...
from ( select l.*, row_number() over (partition by accound_id order by creation_date desc) rn
from logs
WHERE CREATION_DATE >= sysdate-30 )
where rn <= 1000;
Stalin, January 12, 2005 - 8:01 pm UTC
yes, you are right. range 30 preceding within partition group didn't work.
sorry, i should have said latest 1,000 log entries BY ACCOUNT within last 30days for each account. That's why i was trying to put range 30 within account partition group thinking that it would just list me latest 1000 logs for each account and filter out the latest 1000 logs from outer where rn <= 1000. unfortunately oracle doesn't like that syntax.
to keep it simple we need to retain the latest 1000 logs or 30 day older logs, whichever comes first for each accout.
Thanks so much.
January 13, 2005 - 8:06 am UTC
"latest 1000 logs" -- understand that
"30 day older logs" -- don't get that.
If you need to retrieve upto 1000 log entries for each account, given that the entry was made in the last 30 days, the where clause above does that.
Query
PRS, April 07, 2005 - 10:48 am UTC
Hi Tom,
Please Look at the following data and query.
select count(*) count,title,reason
from
(select lzc.activity_date term_date,to_char(lzc.activity_date,'mm') term_month,
to_char(lzc.activity_date,'yyyy') term_year,
ee.employ_date hire_dt,(lzc.activity_date - ee.employ_date) days_worked,
f.employee_name,j.turnover_group title,r.rev_cyborg_desc reason
from tmp_cyb_lzc lzc,tmp_cyb_lzr lzr,tmp_cyb_lzd lzd,
wrktbl_cyb_ee ee,rev_ox_turnover_group j,wrktbl_cyb_f f,REV_SKR_CYB_CODES r
where lzc.full_employee_no = lzr.full_employee_no
and lzc.full_employee_no = lzd.full_employee_no
and lzc.full_employee_no = ee.full_employee_no
and lzc.full_employee_no = f.full_employee_no
and lzc.resulting_emp_stat in ('11','12','13','14','15','16','17','18','19')
and lzr.ctrl_three = '0048'
and to_char(lzc.activity_date,'yyyy') = '2005'
and lzc.effdt <= sysdate
and lzd.effdt <= sysdate
and lzr.effdt <= sysdate
and trim(j.jobid) = lzd.job_code
and r.rev_table_name = 'ACTIVITY_CODES'
and r.rev_cyborg_cd = lzc.activity_code)
group by title,reason
count title reason
1 Mechanics Term-Personal Reasns
2 Assemblers TERM FOR CAUSE
1 Assemblers Term-Attnd/Punctual
1 Assemblers Term-Job Abandonment
3 Assemblers Term-No Ret From LOA
1 Processors TERMED OTHER
1 Processors Term-Attnd/Punctual
2 Processors Term-No Ret From LOA
1 Processors Term-Personal Reasns
1 Miscellaneous RSGN-1ST SHIFT JOB
3 Miscellaneous Term-Attnd/Punctual
1 Miscellaneous Term-Job Abandonment
1 Warehouse Techs Term-Attnd/Punctual
2 Warehouse Techs Term-Job Abandonment
1 Warehouse Techs Term-No Ret From LOA
1 Machine Operators Term-Relocation
1 Machine Operators RSGN-1ST SHIFT JOB
5 Machine Operators TERM-FLD TRL PERIOD
3 Machine Operators Term-Attnd/Punctual
1 Machine Operators TERM-NO-RET-FROM-FML
5 Machine Operators Term-Job Abandonment
6 Machine Operators Term-Job Opportunity
2 Machine Operators Term-No Ret From LOA
2 Machine Operators Term-Personal Reasns
1 Pic Packers/Order Pickers Term-Job Opportunity
I want data like this
Reason Assemblers Processors
TERM FOR CAUSE 2 0
Reason is a database column. But Assembler,processors,etc.. are the column values of database column named title.
How can I do this?
Thanks,
PRS
April 07, 2005 - 11:40 am UTC
take your query, call it Q
select reason,
max( decode( title, 'Mechanics', count ) ) "Mechanics",
max( decode( title, 'Assemblers', count ) ) "Assemblers",
.....
max( decode( title, 'Processors', count ) ) "Processors"
from (Q)
group by reason;
use nvl( max(....), 0 ) if you like.
sql query
AD, April 13, 2005 - 5:11 pm UTC
Hi Tom,
First create table script etc.
create table xxxx
(acc_no number,
x number,
year_month number(6))
/
insert into xxxx values (111, 1, 200410);
insert into xxxx values (111, 2, 200411);
insert into xxxx values (111, 3, 200412);
insert into xxxx values (111, 15, 200501);
insert into xxxx values (111, 6, 200502);
insert into xxxx values (111, 7, 200503);
insert into xxxx values (111, 8, 200504);
insert into xxxx values (111, 9, 200505);
insert into xxxx vALUES (222, 10, 200501);
insert into xxxx vALUES (222, 11, 200502);
insert into xxxx vALUES (222, 12, 200503);
insert into xxxx vALUES (222, 13, 200504);
insert into xxxx vALUES (222, 14, 200505);
I would like to get the following o/p
acc_no max_x_lst_3_mnths max_x_lst_6_mnths max_x_lst_9_mnths
====== ================= ================= ================
111 9 15 15
222 14 14 14
The purpose is to get the max value of x for each acc_no within the last three months, six months, nine months etc.
e.g. to get the max value of x by acc_no within the last three months, the result would be something like the following.
1 select acc_no,year_month, max(x) over (partition by acc_no) from xxxx
2* where year_month >=200502
SQL> /
ACC_NO YEAR_MONTH MAX(X)OVER(PARTITION BY ACC_NO)
---------- ---------- ------------------------------
111 200502 9
111 200503 9
111 200504 9
111 200505 9
222 200502 14
222 200503 14
222 200504 14
222 200505 14
Thanks for your time,
April 13, 2005 - 5:19 pm UTC
ugh, why why why do people do that to dates. oh well, gotta convert it back, hope they are all valid.
ops$tkyte@ORA9IR2> select acc_no,
2 max( case when
3 months_between(sysdate,to_date(to_char(year_month),'yyyymm'))<=3 4 then x end ) max_3,
5 max( case when
6 months_between(sysdate,to_date(to_char(year_month),'yyyymm'))<=6 7 then x end ) max_6,
8 max( case when
9 months_between(sysdate,to_date(to_char(year_month),'yyyymm'))<=9 10 then x end ) max_9
11 from xxxx
12 group by acc_no;
ACC_NO MAX_3 MAX_6 MAX_9
---------- ---------- ---------- ----------
111 9 15 15
222 14 14 14
RE: original answer
Vinayak, April 15, 2005 - 4:33 pm UTC
This is regarding your answer to the original question.
Lets say I've this data:
vmahajan@ECADEV> SELECT * FROM t1 ORDER BY c1 ;
C1 C2
---------- ------------------
1 01-JAN-05 15:00:00
1 01-JAN-05 15:04:00
1 01-JAN-05 15:08:00
2 01-JAN-05 15:00:00
2 01-JAN-05 15:02:00
3 01-JAN-05 00:00:00
4 10-JAN-05 16:00:00
4 10-JAN-05 16:10:00
If we use the -2.5 minutes to +2.5 minute "windowing", we will get the following result set:
vmahajan@ECADEV> select * from (
2 select t1.*,
3 count(*)
4 over (partition by c1
5 order by c2
6 range between 1/24/60*2.5 preceding and 1/24/60*2.5 following) dups
7 from t1 )
8* where dups>1
vmahajan@ECADEV> /
C1 C2 DUPS
---------- ------------------ ----------
2 01-JAN-05 15:00:00 2
2 01-JAN-05 15:02:00 2
Which is not correct. Although for column c1=1, we have a fifference of 4 minutes (less than 5 minutes), we are still not picking that one.
I think we need to use LAG/LEAD functions to appropriately do it. something like:
1 SELECT c1,c2 FROM
2 (SELECT t1.*,
3 LAG(c2) OVER (PARTITION BY c1 ORDER BY c2) prev,
4 LEAD(c2) OVER (PARTITION BY c1 ORDER BY c2) nxt
5 FROM t1)
6* WHERE (c2-prev)*24*60 < 5 OR (nxt-c2)*24*60 < 5
vmahajan@ECADEV> /
C1 C2
---------- ------------------
1 01-JAN-05 15:00:00
1 01-JAN-05 15:04:00
1 01-JAN-05 15:08:00
2 01-JAN-05 15:00:00
2 01-JAN-05 15:02:00
Maybe you should edit your answer to correct this.
*) I agree completely with you that the ANALYTIC functions rock. I've used these recently and tested their performance versus writing queries the old way and they are VERY FAST.
April 15, 2005 - 6:20 pm UTC
if +- 2.5 minutes wasn't right to you, why wouldn't +- 5 minutes by the next answer?
You could read it as a 5 or 2.5 minute surrounding window, either or, it would be more obvious I would think to expand to a 5 minute window.
lag/lead won't work, what about when you have 3 observations in the same window.
Duplicate on one column but different on other column
Tracy, April 20, 2005 - 8:28 am UTC
My table looks like this:
USERNAME CARD DATECREAT T
---------- ---------- --------- -
User 1 Card 1 20-APR-05 P
User 2 Card 2 20-APR-05 L
User 3 Card 3 20-APR-05 L
User 4 Card 2 20-APR-05 X
User 1 Card 10 20-APR-05 X
User 1 Card 11 20-APR-05 X
User 3 Card 2 20-APR-05 L
User 3 Card 3 20-APR-05 L
User 3 Card 3 20-APR-05 L
In my query I want to return all columns from the table where the card is the same as the card in another row but the user is different.
So given the above data in the table I want to return:
User 2 Card 2 20-APR-05 L
User 4 Card 2 20-APR-05 X
User 3 Card 2 20-APR-05 L
Is this a job for analytics - I can't get it to work using 'simple' sql?
April 20, 2005 - 8:34 am UTC
no creates
no inserts
no testing by me, but
select *
from (
select a.*, count(distinct username) over (partition by card) cnt
from t a
)
where cnt > 1
/
is probably it.
No creates, no inserts, no testing !!?
Tracy, April 20, 2005 - 9:23 am UTC
Doesn't matter. Your sql works perfectly, thanks.
Expert one on one
Jorge, April 20, 2005 - 10:05 am UTC
Hi Tom!
Has been "Expert one to one" translated into other languages? (for instance Spanish).
Thanks a lot, you are a Genius.
April 20, 2005 - 8:53 pm UTC
yes, lots - russian, korean, philippine, among others -- "spain" spanish is one I believe it was done in.
Confused with rownum
A reader, April 20, 2005 - 4:19 pm UTC
>I got two queries as mentioned below.
>
>1. SELECT BchCtl_Bus_YMD,
> BchCtl_Mrg_Sts,
> BchCtl_Mrg_YMDHMS,
> BchCtl_IRN,
>
>TRUNC(BchCtl_Run_YMDHMS)
> INTO ValDte,
> MrgSts,
> MrgDte,
> BchCtlIRN,
> BchRunYMD
> FROM BchCtl_Tbl A
> WHERE BchCtl_IRN =
> (SELECT bchctl_irn
> FROM (SELECT
>MAX(BchCtl_IRN) bchctl_irn,
>
>BchCtl_Bus_YMD
> FROM
>BchCtl_Tbl bc
> WHERE
>bc.BchCtl_End_Sts = 'S' AND
>
>(BchCtl_Cod = 'ENX' || ExtBmkPvder.ENS_Cod OR
>
>(BchCtl_Cod = 'ENX' AND
>
>BchCtl_Job = ExtBmkPvder.ENS_Cod)) AND
>
>bc.BchCtl_Bus_YMD <= ActDte(PriActDt + 1)
> GROUP BY
>BchCtl_Bus_YMD
> ORDER BY
>BchCtl_Bus_YMD DESC)
> WHERE Rownum = 1);
>
>
>
>
>2. SELECT BchCtl_Bus_YMD,
> BchCtl_Mrg_Sts,
> BchCtl_Mrg_YMDHMS,
> BchCtl_IRN,
> TRUNC(
>BchCtl_Run_YMDHMS )
> INTO ValDte,
> MrgSts,
> MrgDte,
> BchCtlIRN,
> BchRunYMD
> FROM BchCtl_Tbl A
> WHERE BchCtl_IRN = ( SELECT
>MAX( BchCtl_IRN )
> FROM
>BchCtl_Tbl
> WHERE
>BchCtl_End_Sts = 'S'
> AND
>( ( BchCtl_Cod = 'ENX' || ExtBmkPvder.ENs_Cod )
>
>OR ( BchCtl_Cod = 'ENX'
>
>AND BchCtl_Job = ExtBmkPvder.ENS_Cod ) )
> AND
>BchCtl_Bus_YMD = ( SELECT MAX( BchCtl_Bus_YMD )
>
> FROM BchCtl_Tbl
>
> WHERE BchCtl_End_Sts = 'S'
>
> AND ( ( BchCtl_Cod = 'ENX' ||
>ExtBmkPvder.ENs_Cod )
>
> OR ( BchCtl_Cod = 'ENX'
>
> AND BchCtl_Job =
>ExtBmkPvder.ENS_Cod ) )
>
> AND BchCtl_Bus_YMD <= ActDte(
>PriActDt + 1 ) ) );
>
>
>I got the following questins.
>1. Are query 1 and 2 going to return the same results.
>2. Is Rownum always going to return the same rownum
> when I have order by in my subquery/sub_Table.
>3.
>a. select * from (select max(sal) from emp group by
>deptno order by deptno desc) where rownum=1
>
>b. select max(sal) from emp where deptno in (select
>max(deptno) from emp);
>
>Are a. and b. same. Is the result of a. predicatble.
Date Functions
sasanka, April 28, 2005 - 6:07 am UTC
Hi Tom,
is there any way we can find that " what are the dates fall on sunday in a particular month"
For example, In the current month April what are the dates comes on sunday?
------------------
the ouput should look like
3-APR-2005 SUNDAY
10-APR-2005 SUNDAY
17-APR-2005 SUNDAY
24-APR-2005 SUNDAY
Thanks,
sasanka.
April 28, 2005 - 8:27 am UTC
1 select dt
2 from (
3 select next_day( to_date( 'apr-2005', 'mon-yyyy' )-1, 'SUN' )+l*7 dt
4 from (select level-1 l from dual connect by level <= 5 )
5 )
6* where to_char(dt,'mon-yyyy')='apr-2005'
ops$tkyte@ORA9IR2> /
DT
---------
03-APR-05
10-APR-05
17-APR-05
24-APR-05
works in 9i and above. 'apr-2005' would of course be a bind variable.
OK
Raju, April 28, 2005 - 1:55 pm UTC
Hi Tom,
Some times in queries we use
SQL> select * from dual where -1=-1
what is the purpose of using that "where clause" ?
Is there any documentation for that??
Please do reply.
Bye!
April 28, 2005 - 2:07 pm UTC
nope.
How to do sum ?
Parag Jayant Patankar, May 04, 2005 - 5:30 am UTC
Hi,
I am trying to write following SQL statement
select a.deptno, a.ename, a.sal, sum(b.sal)
from emp a, emp b
where a.deptno = b.deptno
and a.sal >= b.sal
group by a.deptno, a.ename, a.sal
order by 1, 3
14:27:16 SQL> /
DEPTNO ENAME SAL SUM(B.SAL)
---------- ---------- ---------- ----------
10 KING -2000 -2000
10 MILLER 1300 -700
10 CLARK 2450 1750
20 SMITH 800 800
20 ADAMS 1100 1900
20 JONES 2975 4875
20 FORD 3000 10875
20 SCOTT 3000 10875
30 MARTIN 1250 2500
30 WARD 1250 2500
30 TURNER 1500 4000
30 ALLEN 1600 5600
30 BLAKE 2850 8450
13 rows selected.
This query is working fine for deptno, sal wise output.
But when I try to get sum of sal deptno, ename wise I am not getting correct result. I know this can be easily achived thru analytical function. Can you tell me what should be "where condition" for such SQL ?
I am asking you this question for my understanding of writing such type of SQLs.
regards & thanks
pjp
May 04, 2005 - 9:14 am UTC
do you just want to do a running total?
scott@ORA10G> select deptno, ename, sal, sum(sal) over (partition by deptno order by sal, empno ) running_total
2 from emp;
DEPTNO ENAME SAL RUNNING_TOTAL
---------- ---------- ---------- -------------
10 MILLER 1300 1300
10 CLARK 2450 3750
10 KING 5000 8750
20 SMITH 800 800
20 ADAMS 1100 1900
20 JONES 2975 4875
20 SCOTT 3000 7875
20 FORD 3000 10875
30 JAMES 950 950
30 WARD 1250 2200
30 MARTIN 1250 3450
30 TURNER 1500 4950
30 ALLEN 1600 6550
30 BLAKE 2850 9400
14 rows selected.
running total
parag jayant patankar, May 04, 2005 - 9:40 am UTC
Hi Tom,
Sorry my question was not precise. I want running total departmentwise order by deptno and ename not using by analytical function.
select a.deptno, a.ename, a.sal, sum(b.sal)
from emp1 a, emp1 b
where a.deptno = b.deptno
and a.sal >= b.sal
group by a.deptno, a.ename, a.sal
order by 1, 3
/
I want your help to modify this query to give running total department no, ename wise total.
regards & thanks
pjp
May 04, 2005 - 10:14 am UTC
why? I mean it would be totally and wholly inefficient to do this?
What is the technical reason we cannot do it with analytics?
running total
parag jayant patankar, May 04, 2005 - 10:32 am UTC
Hi Tom,
There is no reason which is preventing us from writing analytical function for running total. I agree with you, if I use analytical function then it is already better for perfomance.
It is just for my educational purpose how to write sql without using analytical function for running total.
regards & thanks
pjp
May 04, 2005 - 10:58 am UTC
do you have expert one on one Oracle?
I actually used this example in the analytics chapter :)
A quick example and an explanation of what exactly is happening will get us jump started here:
tkyte@TKYTE816> break on deptno skip 1
tkyte@TKYTE816> SELECT ename, deptno, sal,
2 SUM(sal) OVER
3 (ORDER BY deptno, ename) running_total,
4 SUM(sal) OVER
5 (PARTITION BY deptno
6 ORDER BY ename) department_total,
7 row_number() OVER
8 (PARTITION BY deptno
9 ORDER BY ename ) seq
10 FROM emp
11 order by deptno, ename
12 /
ENAME DEPTNO SAL RUNNING_TOTAL DEPARTMENT_TOTAL SEQ
---------- ---------- ---------- ------------- ---------------- ----------
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 3000 12850 4100 2
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5
ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6
14 rows selected.
So, as you can see - we were able to compute a RUNNING_TOTAL for the entire query. We did this by using the entire ordered result set via "SUM(SAL) OVER (ORDER BY DEPTNO, ENAME)". We were also able to compute a running total within each department - a total that would be reset at the beginning of the next department. The PARTITION BY DEPTNO in that SUM(SAL) caused that to happen, we specified a partitioning clause in the query to break the data up into groups. Just to see the data with a sequence number within each partition - we also added in a SEQ column. It shows the position of the row within a partition - the same partition we used to compute the department total. That lets me see that SCOTT was the "4th" row in department 20 when ordered by ENAME. This ROW_NUMBER() feature will have many uses elsewhere as we will see later - not just to display the fact that SCOTT is the 4th row - but to transpose or pivot result sets.
As you can see - if you are a pretty good SQL query developer now - this new set of functionality holds some pretty exciting possibilities. It opens up a whole new way of looking at the data. It will remove tons of procedural code and complex/inefficient queries you would have had to develop yourself to achieve the same result. Just to give you a flavor of how efficient this can be over the old "pure relational ways" we can see how the above query might work on 1,000 rows instead of just 14 rows performance wise. We'll test using both the new analytical functions and the "old" relational methods. The table I used is an EMP look alike:
tkyte@TKYTE816> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
tkyte@TKYTE816> create index t_idx on t(deptno,ename);
Index created.
Those two statements setup our EMP like table with just the ENAME, DEPTNO and SAL columns. We created the only index really needed for this example - we are doing everything by DEPTNO and ENAME. We'll run the query from above using this new table using AUTOTRACE in trace only mode - just to see how much work is done:
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> SELECT ename, deptno, sal,
2 SUM(sal) OVER
3 (ORDER BY deptno, ename) running_total,
4 SUM(sal) OVER
5 (PARTITION BY deptno
6 ORDER BY ename) department_total,
7 row_number() OVER
8 (PARTITION BY deptno
9 ORDER BY ename ) seq
10 FROM T emp
11 order by deptno, ename
12 /
1000 rows selected.
Elapsed: 00:00:00.61
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (BUFFER)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T'
3 2 INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
292 consistent gets
66 physical reads
0 redo size
106978 bytes sent via SQL*Net to client
7750 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1000 rows processed
So, that took 0.61 seconds and 294 logical IOS. Now, we'll do the same exact thing using only "standard" SQL functionality:
tkyte@TKYTE816> select ename, deptno, sal,
2 (select sum(sal)
3 from t e2
4 where e2.deptno < emp.deptno
5 or (e2.deptno = emp.deptno and e2.ename <= emp.ename )
6 ) running_total,
7 ( select sum(sal)
8 from t e3
9 where e3.deptno = emp.deptno
10 and e3.ename <= emp.ename
11 ) department_total,
12 ( select count(ename)
13 from t e3
14 where e3.deptno = emp.deptno
15 and e3.ename <= emp.ename
16 ) seq
17 from t emp
18 order by deptno, ename
19 /
1000 rows selected.
Elapsed: 00:00:06.89
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
665490 consistent gets
0 physical reads
0 redo size
106978 bytes sent via SQL*Net to client
7750 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
tkyte@TKYTE816> set autotrace off
We get the same exact answer from both - but what a difference these functions can make. The run time is many times longer and the number of logical IOS is up by many orders of magnitude. The analytical functions processed the result set using significantly fewer resources and an astoundingly reduced amount of time. Not only that but once you understand the syntax of the analytic functions, you will find them easier to code with then the equivalent standard SQL - just compare the syntax of the above two queries to see what a difference they can make.
Calendar in SQL
Parag J Patankar, May 11, 2005 - 11:06 am UTC
Hi Tom,
Thanks for your great answer to me in this thread. Is it possible to generate calendar like unix
@mumsa128200: /f205/prod > cal 5 2005
May 2005
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
thru SQL ?
regards & thanks
pjp
May 11, 2005 - 12:22 pm UTC
OK
Raj, July 14, 2005 - 1:52 pm UTC
Hi Tom,
Is there any predefined order to use the logical
operators namely AND,OR and NOT.
Whether NOT must be followed by AND or OR?
or any other fixed order???
July 14, 2005 - 1:59 pm UTC
only your logic can dictate where NOT belongs?
you can NOT an "OR"
you can NOT an "AND"
you can "AND" a "NOT NOT" with a "NOT"
your logical will tell you what you must do to get the right answer.
To find out greatest across columns
a reader, July 14, 2005 - 5:25 pm UTC
Hi Tom,
Could you help with the following:
create table v (c1 number , c2 number , c3 number);
Table created.
insert into v values (10, null, 12);
insert into v values (20, 15, 90);
insert into v values (30, null, null);
SQL> select * from v;
C1 C2 C3
---------- ---------- ----------
10 12
20 15 90
30
I am trying to get an o/p like the below.
SQL> select c1, greatest(c2, c3) from v
desired o/p
C1 GREATEST(C2,C3)
---------- -----------------------------
10 12
20 90
30
The following works in a way but then if both (c1, C2) are null, then I expect the result to return null instead of a specific value
SQL> select c1, greatest(nvl(c2, 0), nvl(c3, 0) ) from v
2 /
C1 GREATEST(NVL(C2,0),NVL(C3,0))
---------- -----------------------------
10 12
20 90
30 0
Your help is very much appreciated.
Thanks
July 15, 2005 - 7:27 am UTC
if you have more than 2 columns:
ops$tkyte-ORA9IR2> select c1,
2 greatest( nvl(c2,coalesce(c2,c3)),
3 nvl(c3,coalesce(c2,c3)) ) g
4 from t;
C1 G
---------- ----------
10 12
20 90
30
if you have just two columns:
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> select c1,
2 greatest( nvl(c2,c3),
3 nvl(c3,c2) ) g
4 from t;
C1 G
---------- ----------
10 12
20 90
30
Small correction to the first solution
Peter Kinsella, July 15, 2005 - 7:48 am UTC
partition should be by specialist_id and account_no, not just specialist_id:
select *
from (
.....
----> (partition by specialist_id, account_no <-----
.....
where dups > 1
A reader, July 21, 2005 - 9:27 am UTC
Dear Tom
Please help me to write this query.
create table ptest
(id number(2) ,
cd number(2),
pid number(2),
cid number(2))
/
insert into ptest values(1,1,1,0)
/
insert into ptest values(1,1,2,1)
/
insert into ptest values(1,1,3,1)
/
insert into ptest values(1,1,4,0)
/
insert into ptest values(1,1,30,2)
/
insert into ptest values(1,1,70,3)
/
insert into ptest values(1,1,40,4)
/
insert into ptest values(1,1,46,40)
/
insert into ptest values(1,1,60,30)
/
insert into ptest values(1,1,72,70)
/
select * from ptest order by pid;
ID CD PID CID
---------- ---------- ---------- ----------
1 1 1 0
1 1 2 1
1 1 3 1
1 1 4 0
1 1 30 2
1 1 40 4
1 1 46 40
1 1 60 30
1 1 70 3
1 1 72 70
For eg: If I have the PID value 72, fetch the next row
with PID = previous row's CID value thats 70 and so on until CID =0
ie, how can I get an output like this?
ID CD PID CID
---------- ---------- ---------- ----------
1 1 1 0
1 1 3 1
1 1 70 3
1 1 72 70
Thanking you in advance
Raj
July 21, 2005 - 4:14 pm UTC
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
2 from ptest
3 start with pid = 72
4 connect by prior cid = pid and prior cid <> 0
5 order by pid
6 /
ID CD PID CID
---------- ---------- ---------- ----------
1 1 1 0
1 1 3 1
1 1 70 3
1 1 72 70
Nice
Raju, July 31, 2005 - 10:42 pm UTC
Hi Tom,
Is there any limit in the number of columns that can be used in ORDER BY clause???
August 01, 2005 - 7:26 am UTC
none documented,
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
2 l_sql long;
3 begin
4 l_sql := 'create table t ( c0001 char(2000) default ''x''';
5 for i in 2 .. 1000
6 loop
7 l_sql := l_sql || ',c'||to_char(i,'fm0000') || ' char(2000) default ''x''';
8 end loop;
9 l_sql := l_sql || ')';
10 execute immediate l_sql;
11
12 execute immediate 'insert into t (c0001) values (''x'')';
13
14 l_sql := 'select * from t order by c0001';
15 for i in 2 .. 1000
16 loop
17 l_sql := l_sql || ',c'||to_char(i,'fm0000');
18 end loop;
19 open :x for l_sql;
20 end;
21 /
PL/SQL procedure successfully completed.
at least 1,000
quick question
Joe, August 08, 2005 - 11:48 am UTC
If a table contains following numbers in a column
1..24, 26, 29 and 30 ( where 1..24 is all int numbers between 1 and 24 inluding both 1 and 24)
Now given a number, lets say 27, I want to return all numbers from that table which are less than or equal to next higher number of 27. If 27 is already present in the table then return all numbers which are less than or equal to 27.
So when we send in 27, I should retrieve 1..24,26 and 29.
When I send in 29, I should retrieve 1..24,26 and 29.
Could it be done in ONE SQL?
August 08, 2005 - 8:08 pm UTC
select num
from t
where num <= nvl( (select min(num)
from t
where num >= ?), ?)
I think, no create table, no inserts, no testing.
if 27 is in there, 27 is >= 27 and will be the min.
if 27 is not in there, then the number number above 27 comes out.
if nothing above 27 exists, then return 27 (nvl)
Decode in Group By
Magesh, September 29, 2005 - 1:26 pm UTC
Tom,
I want to do a custom sort using decode in group by like the following:
select get_dname(deptno) Dname,count(*) from emp
group by decode(Deptno,10,1,30,2,20,3) dname;
But ofcourse group by does not let me to do this in this way, if I remove decode and simply say deptno, it sorts as 10,20,30. Is there a way to do what I want to do in Oracle?
September 30, 2005 - 8:32 am UTC
without an order by - you have NO SORT, you need to use order by in order to get sorted data. group by is not a replacement for order by!!!!!!!!!!!!!!!
Is the value returned by get_dname(deptno) UNIQUE over all deptno's (that is, two deptnos will never have the same name)??? If so
select get_dname(deptno), cnt
from ( select deptno, count(*) cnt
from emp
group by deptno )
order by decode( deptno, 10, 1, 30, 2, 20, 3 )
is probably what you are looking for.
Decode in group by
Magesh, September 29, 2005 - 1:58 pm UTC
Tom,
Please ignore my previous question. I figured it out as follows:
select get_dname(deptno) Dname,count(*) from emp
group by decode(deptno,10,1,30,2,20,3,deptno),deptno
/
Thanks anyway.
September 30, 2005 - 8:37 am UTC
(that doesn't sort.....)
Help
Bateson, September 30, 2005 - 1:14 pm UTC
Hi Tom,
I am getting Oracle error
ORA-01858 : a non-numeric character was found
where a numeric was expected.
I have two subqueries which return data as follows
Subquery A
===========
Country User_Grade User_Cnt
--------- ----------- --------
US A 670
US B 203
Subquery B
==========
Country User_Cnt
-------- --------
US 540
My main query is as follows
SQL> select to_char(sysdate,'dd/mm/yyyy') as Today,
a.country,
a.grade,
a.user_cnt,
round(nvl(a.user_cnt/b.user_cnt)*100,0),2) as user_percent
from (
A,B)
WHERE A and B are subqueries mentioned above.
when I put this query, I am getting the ORA-01838 error.
what can be the reason behind this??
How to correct this??
Please help me with a solution.
September 30, 2005 - 2:22 pm UTC
you give me no tables nor inserts....
Query
Ravi Thapliyal, October 05, 2005 - 6:37 am UTC
Hi Tom,
I have a query here, take the two system tables "all_tab_comments" and "all_col_comments". I need the result in this manner:-
table_name1 comment
col_name1 comment
col_name2 comment
col_name3 comment
table_name2 comment
col_name1 comment
col_name2 comment
col_name3 comment
......... ........
I will appreciate your help.
Regards
Ravi Thapliyal
October 05, 2005 - 7:50 am UTC
1 select decode( what, 'TABLE', '', ' ' )||data, comments
2 from (
3 select 1 oc, 'TABLE' what, table_name data, comments, table_name
4 from all_tab_comments
5 union all
6 select 2 oc, 'COLUMN', column_name, comments , table_name
7 from all_col_comments
8 )
9* order by table_name, oc, data
FIFO in SQL
Parag Jayant Patankar, October 05, 2005 - 8:51 am UTC
Hi Tom,
Excellent answer to question. Tom, I want to do subtract sales amount from purchase amount in SQL by FIFO method.
for e.g.
create table t
(
indicator varchar2(1),
dt date,
amt number
);
insert into t values ('p', sysdate-100, 20);
insert into t values ('p', sysdate-90, 30);
insert into t values ('p', sysdate-85, 70);
insert into t values('s', sysdate-85, 60);
insert into t values('p', sysdate-83, 100);
insert into t values('s', sysdate-84, 40);
insert into t values('s', '01-aug-05', 80);
commit;
So my table having following records
18:32:51 SQL> select * from t;
I DT AMT
- --------- ----------
p 14-JUN-05 20
p 24-JUN-05 30
p 29-JUN-05 70
s 29-JUN-05 60
p 01-JUL-05 100
s 30-JUN-05 40
s 01-AUG-05 80
7 rows selected.
So I have to deduct sales amount 60, 40 and 80 from the purchase amount by FIFO method and print purchase and sales balance in next columns. for e.g. I want to deduct sales amount 60 from 20 so sales amount bal is 40. now balance 40 I want to deduct from 30 so purchase amount balance is 0 and sales amount balance is 10. ...so on.
So my desired output is
Ind Pur Dt Pur Amt Sales Dt Sales Pur sal
Amt Bal Bal
p 14-Jun-05 20 29-Jul-05 60 0 40
p 24-Jun-05 30 29-Jul-05 40 0 10
p 29-Jun-05 70 29-Jul-05 10 60 0
30-Jun-05 40 20 0
...
...
Is it possible to generate such output from SQL ?
regards & thanks
pjp
October 05, 2005 - 11:11 am UTC
continue the thought, what happens withthe 40/80 rows there.
FIFO in SQL
Parag Jayant Patankar, October 06, 2005 - 6:54 am UTC
Hi Tom,
Sorry I have not understood your answer, will you pl explain more
"continue the thought, what happens withthe 40/80 rows there."
total Sales amount will be always less than purchase amount, and in a month there will not be more than 50 records of purchase and sales.
regards & thanks
pjp
October 06, 2005 - 7:56 am UTC
finish the example. is the 40/80 row really to be treated as a single 120 row.
I'm not following your logic.
SQL
Parag Jayant Patankar, October 06, 2005 - 9:22 am UTC
Hi Tom,
Sorry for the confustion. 40/80 sales records can not be considered as 120 ( 40+80 ) as I required balance amount of sales as shown in the example.
regards & thanks
pjp
October 06, 2005 - 11:51 am UTC
but your example didn't process them, i didn't follow it - I don't see the 40/80
SQL
parag jayant patankar, October 06, 2005 - 12:17 pm UTC
Hi Tom,
In my desired output I have shown only partial results. Full output will be ( something similar )
18:32:51 SQL> select * from t;
I DT AMT
- --------- ----------
p 14-JUN-05 20
p 24-JUN-05 30
p 29-JUN-05 70
s 29-JUN-05 60
p 01-JUL-05 100
s 30-JUN-05 40
s 01-AUG-05 80
7 rows selected.
Ind Pur Dt Pur Amt Sales Dt Sales Pur sal
Amt Bal Bal
p 14-Jun-05 20 29-Jul-05 60 0 40 (20-60) p-s
p 24-Jun-05 30 29-Jul-05 40 0 10 (30-40) p-s
p 29-Jun-05 70 29-Jul-05 10 60 0 (70-10) p-s
30-Jun-05 40 20 0 (60-40) p-s
01-aug-05 80 0 60 (20-80)p-s
p 01-jul-05 100 01-aug-05 60 40 0 (100-60)p-s
I hope my desired output and example is clear now.
regards & thanks
pjp
October 06, 2005 - 12:34 pm UTC
the only way I can think to run this is from the bottom up and I don't think it is going to "work" as they say.
I have problems with the data in general here - not fully understanding how it is to be applied and why the 40/80 records are "not the same". The logic isn't "take this p record and look forward for the next S record" - it is sort of a cumulative thing.
sorry - I don't see it. I don't see a clean SQL solution for it either.
SQL
Parag Jayant Patankar, October 06, 2005 - 12:49 pm UTC
Hi Tom,
Thanks for your clear answer. Is it possible to do some simple calculation of deducting total sales amount ( instead of individual sales amount )from each purchase record and print balance of purchase for e.g.
18:32:51 SQL> select * from t;
I DT AMT
- --------- ----------
p 14-JUN-05 20
p 24-JUN-05 30
p 29-JUN-05 70
s 29-JUN-05 60
p 01-JUL-05 100
s 30-JUN-05 40
s 01-AUG-05 80
Then my total sales amount is 180 (60+40+80). So can I have output like
Ind Dt Pur Amount Tot SalesAmt Pur Balance
p 14-JUN-05 20 180 0
p 24-JUN-05 30 180 0
p 29-JUN-05 70 180 0
p 01-jul-05 100 180 40
regards & thanks
pjp
October 06, 2005 - 1:29 pm UTC
ops$tkyte@ORA10GR2> select indicator, dt, amt, salesamt,
2 greatest( runningTotal-salesamt, 0) purbal
3 from (
4 select indicator,
5 dt,
6 amt,
7 (select sum(amt) from t where indicator = 's') salesamt,
8 sum(amt) over (order by dt) runningTotal
9 from t
10 where indicator = 'p'
11 )
12 /
I DT AMT SALESAMT PURBAL
- --------- ---------- ---------- ----------
p 28-JUN-05 20 180 0
p 08-JUL-05 30 180 0
p 13-JUL-05 70 180 0
p 15-JUL-05 100 180 40
OK
Raju, October 12, 2005 - 7:10 am UTC
Hi Tom,
Any other way to put this query??
SQL > select 'No Rows Found' from dual where 1 > (select count(*) from t)
If the table T has no rows it must return 'No Rows Found'.
Can we use Exists operator here??
October 12, 2005 - 7:39 am UTC
you use NOT exists and probably should
select 'no data' from dual where not exists (select null from t)
Help for SQL Query
kamal, June 01, 2006 - 8:13 am UTC
Hi Tom,
We have table with Group and User data.
Create table GroupUser
(
G varchar2(10),
U varchar2(10)
);
The Input to the Query will group names (example: 'G1','G2')
The Output should be only users who belong all the groups given as input.
thanks in advance
kamal
June 01, 2006 - 10:58 am UTC
well, with that table, we can just skip it - since it is empty.
Probably the answer is something like:
select u
from t
where g in ( 'G1', 'G2' )
group by u
having count(distinct g) = 2;
Using Analytics
Kamal, June 05, 2006 - 8:12 am UTC
Hi Tom
Thanks for your reply...i found out the same using analytical query
Select Distinct "User" from
(
Select G "Group", U "User", count(*) over (partition by U) cou
from Test_sql
where G in ('G2','G3')
)
Where cou = 2;
Kamal
June 05, 2006 - 9:39 am UTC
that would be the less effective way to do it, sure.
see above.
Get data with same id in one line
Sean, March 28, 2007 - 5:48 pm UTC
Hi Tom,
I would like to get data like this from the table t1. Thanks so much for your help. -- Sean
C1 C2 C2 C2
1 A AA AAA
2 B BB BBB
SQL> select * from t1;
C1 C2
---------- --------------------
1 A
1 AA
1 AAA
2 B
2 BB
2 BBB
create table t1(c1 number, c2 varchar(20));
insert into t1(c1, c2)
values(1, 'A');
insert into t1(c1, c2)
values(1, 'AA');
insert into t1(c1, c2)
values(1, 'AAA');
insert into t1(c1, c2)
values(2, 'B');
insert into t1(c1, c2)
values(2, 'BB');
insert into t1(c1, c2)
values(2, 'BBB');
March 30, 2007 - 11:48 am UTC
search for the word
pivot
on this site for many many many examples.
Efficient SQL
Ram, April 12, 2007 - 2:47 pm UTC
Hi tom ,
I have two tables
=======
Table A
========
UniqueName, Name
----------, ----
123 , Test
124 , Test1
125 , Test2
126 , Test3
=======
Table B
========
code , grade
-----, -----
123 , A
126 , B
I need the output from these tables in the below format
123, Test, 'EXIST'
124,Test1, 'NONEXIST'
125, test2, 'NONEXIST'
126, Test3,'EXIST'
I have written a query as listed below
SELECT distinct a.UNIQUENAME,'EXIST' FROM A a,B b
Where b.code=a.UNIQUENAME
union all
select distinct a.UNIQUENAME,'NONEXIST' from A a where
a.UNIQUENAME not in
( SELECT a.UNIQUENAME FROM A a,B b
Where b.code=a.UNIQUENAME)
But not sure whether this is a good approach or not , Please suggest.
Thanks
April 13, 2007 - 12:21 pm UTC
is code unique in tableb?
select a.*, case when b.code is null then 'NOT EXIST' else 'EXIST' end e
from a, b
where a.uniquename = b.code(+);
Efficient SQL
RAM, April 12, 2007 - 3:53 pm UTC
Hi tom ,
Also please tell me whether this approcah is good
SELECT a.ID,(case when b.code is null then 'notexists' else 'exists' end) value FROM A left outer join B b
on a.id=b.code
April 13, 2007 - 12:25 pm UTC
that would be good IF code in B is unique.
A reader, June 20, 2007 - 1:27 am UTC
In table i have following date ranges
ID From_Date To_Date
1 05/01/2007 05/30/2007
2 01/01/2007 12/31/2007
3 05/15/2007 05/25/2007
4 04/01/2007 05/12/2007
5 05/20/2007 10/06/2007
6 01/02/2007 04/30/2007
7 06/15/2007 09/15/2007
I want only those recored which are with in this provided input
From_Date =05/01/2007
To_Date =05/30/2007
The out put should contain following records.
ID From_Date To_Date
1 05/01/2007 05/30/2007
2 01/01/2007 12/31/2007
3 05/15/2007 05/25/2007
4 04/01/2007 05/12/2007
5 05/20/2007 10/06/2007
CREATE TABLE TEMP_DATE
(
ID NUMBER(10),
DATE_FROM DATE,
DATE_TO DATE
);
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
2, TO_Date( '01/01/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '12/31/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
3, TO_Date( '05/15/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '05/25/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
4, TO_Date( '04/01/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '05/12/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
5, TO_Date( '05/20/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '10/06/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
6, TO_Date( '01/02/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '04/30/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
7, TO_Date( '06/15/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '09/15/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEMP_DATE ( ID, DATE_FROM, DATE_TO ) VALUES (
1, TO_Date( '05/01/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '05/30/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
Currently i am using following query.
SELECT *
FROM TEMP_DATE
WHERE ((DATE_FROM >TO_DATE('05/01/2007','MM/DD/YYYY') AND DATE_TO <=TO_DATE('05/30/2007','MM/DD/YYYY'))
OR (DATE_FROM <TO_DATE('05/01/2007','MM/DD/YYYY') AND DATE_TO >=TO_DATE('05/30/2007','MM/DD/YYYY'))
OR (DATE_FROM =TO_DATE('05/01/2007','MM/DD/YYYY') OR DATE_TO =TO_DATE('05/30/2007','MM/DD/YYYY') )
OR (DATE_FROM <TO_DATE('05/01/2007','MM/DD/YYYY') AND DATE_TO >=TO_DATE('05/01/2007','MM/DD/YYYY') )
OR (DATE_FROM <TO_DATE('05/30/2007','MM/DD/YYYY') AND DATE_TO >TO_DATE('05/01/2007','MM/DD/YYYY') )
)
Is there some better way to avoid these ORS
June 20, 2007 - 9:27 am UTC
ops$tkyte%ORA10GR2> select *
2 from temp_date
3 where (date_from <= to_date(:p_ending,'mm/dd/yyyy') )
4 AND (date_to >= to_date(:p_beginning,'mm/dd/yyyy') )
5 order by id
6 /
ID DATE_FROM DATE_TO
---------- --------- ---------
1 01-MAY-07 30-MAY-07
2 01-JAN-07 31-DEC-07
3 15-MAY-07 25-MAY-07
4 01-APR-07 12-MAY-07
5 20-MAY-07 06-OCT-07
A reader, June 20, 2007 - 10:07 am UTC
Thanks a lot for prompt reply. It will make my life very easy at maintenance time. You are doing a lot for Oracle community.
Why optimizer avoiding index when using to_nchar function
V, June 21, 2007 - 4:13 pm UTC
Tom,
My question regarding why optimizer could not use index when I use function t_nchar on a string literal in predicate to compare the column value where optimizer can use an index when used to_char
Thanks for your help.
V
1* create table temp as select to_char(rownum) col1 from dba_objects
Table created.
1* create index i_temp on temp(col1)
SQL> /
Index created.
SQL> exec dbms_stats.gather_table_stats('XXX','TEMP',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autotrace on explain
SQL> select * from temp where col1='1';
COL1
----------------------------------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 INDEX (RANGE SCAN) OF 'I_TEMP' (INDEX) (Cost=1 Card=1 Byte
s=5)
1* select * from temp where col1=to_char(1)
SQL> /
COL1
----------------------------------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 INDEX (RANGE SCAN) OF 'I_TEMP' (INDEX) (Cost=1 Card=1 Byte
s=5)
Wrote file afiedt.buf
1* select * from temp where col1=to_nchar(1)
SQL> /
COL1
----------------------------------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=490 Bytes=24
50)
1 0 TABLE ACCESS (FULL) OF 'TEMP' (TABLE) (Cost=25 Card=490 By
tes=2450)
June 22, 2007 - 10:14 am UTC
because you have an index on a CHAR value and when you compare a CHAR to an NCHAR - there is a conversion of the CHAR to a NCHAR.
where varchar_column = to_nchar(something)
is the same logically as:
where to_nchar(varchar_column) = to_nchar(something)
you have applied a function to the database column.
Same query above
A reader, June 22, 2007 - 12:37 pm UTC
Tom,
Same from the above question,
for the query below:
select * from temp where col1=to_nchar('1')
[where col1 in varchar2 and has index on col1]
Why can't optimizer think to
implicitly convert to_nchar('1') to varchar2 [as to to_char(to_nchar('1')) because col1 in varchar2 and use index on col1
instead of checking
to_nchar(col1) = to_nchar('1') which is causing to avoid index.
Thanks for your help.
June 22, 2007 - 5:14 pm UTC
because of the rules
the rule when you compare a number to a string is to convert the string to a number
where string_database_column = any_number
is the same as
where to_number(string_database_column) = any_number
that is the rule for string to number comparisions. the rule for varchar to nvarchar is convert the varchar into nvarchar (since converting the nvarchar into a varchar WOULD STAND TO CHANGE THE DATA)....
Followup from above
V, June 23, 2007 - 1:00 pm UTC
Tom,
Follow-up from the question above,
We are an ERP development company.
After doing analysis, for performance/storage reasons, we decided CJK customers should use NVARCHAR2 (AL16UTF16) and US/Europe use VARCHAR2 (and use AL32UTF8).
(Being AL16UTF16 not supported as character set that means UTF16 is not possible to use in VARCHAR2).
So we provide a tool to switch from VARCHAR2 to NVARCHAR2 to support NVARCHAR2 for CJK customers(simple statment alter table t1 modify(col varchar2).
My question here is how do we pass literals from platform (middleware). Should we prefix with N or not.
Like
Select * from temp where col1 = N'Hello' OR
Select * from temp where col1 = 'Hello'
If we N' Prfix we loose index capability due to implicit conversion from VARCHAR2. For CJK¿s, it will be Invalid comparison for NVARCHAR2 without N'.
Is there any better way under given circumstances
Your help is greatly appreciated.
June 23, 2007 - 2:06 pm UTC
you do NOT want to use literals, you will use BIND VARIABLES.
I do not think you made the right choice there - you'll need different indexing strategies and all.
You should just use a unicode character set and be done with it.
using literals - that, that would be the worst performace/security/scalability decision you could make.
V, June 23, 2007 - 9:14 pm UTC
Tom,
We use bind variables but there are certain cases where we may need to use literals to get advantages of skewed data ,overcome certain cases like bind peeking
Do you think literals absolutely avoided in OLTP application?
Thanks for your help.
July 02, 2007 - 8:47 am UTC
if you have an "OLTP" application that would need to use literals because of data skew (so that sometimes a full scan is taken over an index) - then I would say - you do not have an "OLTP" application anymore.
OLTP - characterized by short, predicable length queries returning tiny (relatively speaking) result sets. Hundreds/thousands of statements executed per second. Binding is the only way to do it.
Should we literals some time instead of bind variables in OLTP also
V, June 23, 2007 - 10:16 pm UTC
Hi Tom,
Your input is always very helpful to us.
From the question above do you think we should use literals instead of bind variables (OLTP app)where we knew that data highly skewed and few distinct values exists in the column.
My example for that scenario below :
SQL> select count(*),yes_no_fl from temp group by yes_no_fl;
COUNT(*) YES
---------- ---
195944 No
196 Yes
SQL> var l_var varchar2(3);
1 select count(*) from
2 (
3 (select rownum,yes_no_fl from temp where yes_no_fl = :l_var)
4* )
SQL> /
COUNT(*)
----------
0
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=80 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=80 Card=99554)
3 2 COUNT
4 3 TABLE ACCESS (FULL) OF 'TEMP' (TABLE) (Cost=80 Card=
99554 Bytes=298662)
Statistics
----------------------------------------------------------
304 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> exec :l_var := 'Yes';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> /
COUNT(*)
----------
196
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=80 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=80 Card=99554)
3 2 COUNT
4 3 TABLE ACCESS (FULL) OF 'TEMP' (TABLE) (Cost=80 Card=
99554 Bytes=298662)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
304 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1 select count(*) from
2 (
3 (select rownum,yes_no_fl from temp where yes_no_fl = 'Yes')
4* )
5 /
COUNT(*)
----------
196
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=4 Card=345)
3 2 COUNT
4 3 INDEX (RANGE SCAN) OF 'L_TEMP' (INDEX) (Cost=4 Card=
345 Bytes=1035)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
July 02, 2007 - 8:51 am UTC
those are NOT oltp queries, that would be a report maybe - run once in a long time. It doesn't look like something that
a) should be run
b) would be run much
why would you count yes/no's for every record in a table like that?
If it is a "common" requirement, then materialized views are likely part of the solution (so you would have a two row table). I use them myself for the asktom homepage to tell you how many articles have been added/modified recently.
Need your help
vikas, June 26, 2007 - 4:43 am UTC
Hi Tom,
Appreciate your help!
I have a table t with a column Request_term. Some sample data is listed for your reference:
boldly going where captain cook has gone before |: category_travel
Ambitious Brew : The Story of American Beer |: category_history |: category_food_and_wine
Its Values are Term and categories separater by a delimiter |:
So the Terms are :
1.boldly going where captain cook has gone before
2.Ambitious Brew : The Story of American Beer
and their associated categories are :
1.category_travel
2.category_history
2.category_food_and_wine
We need to formulate a SQL query to get the output as :
Category Count
category_travel 1
category_history 1
category_food_and_wine 1
Thanks
Here ya go Vikas
Yuan, June 26, 2007 - 2:59 pm UTC
Normalizing your data would make this a whole lot easier, but here:
create table yuantest (x varchar2(100));
insert into yuantest values ('boldly going where captain cook has gone before |: category_travel');
insert into yuantest values ('Ambitious Brew : The Story of American Beer |: category_history |: category_food_and_wine');
insert into yuantest values ('You gotta be kiddin |: category_history |: category_humor |: category_junk');
select cats, count(1)
from (select trim(substr(x, instr(x, '|:', 1, level) + 2, decode(connect_by_isleaf, 1, 100, instr(x, '|:', 1, level + 1) - instr(x, '|:', 1, level) -2))) cats from yuantest connect by prior x = x and level <= (length(x) - length(replace(x, '|:', NULL))) / 2 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL)
group by cats
PRIOR dbms_random.string ('p', 10) IS NOT NULL
Yuan, June 26, 2007 - 3:05 pm UTC
Tom, in my solution for Vikas, I stole the connect by clause:
PRIOR dbms_random.string ('p', 10) IS NOT NULL
from your response in another post on this site. Before I added that I was getting ORA-01436: CONNECT BY loop in user data. I see that adding this clause fixes that, but to be honest I don't understand how. When you get a chance, can you explain?
Thanks a ton!
Vikas, June 28, 2007 - 6:24 am UTC
Hi Yaun,
Too good a solution, it fits my need. Has not even thought in that direction to solve that query.
Once again, wonderful! Its best to learn some extremely good fundamentals as to how to avoid a loop using PL/SQL and do that stuff in SQL.
It challenges you!
Regards,
Question
PRS, January 12, 2008 - 9:58 am UTC
Tom,
I know that you may delete this question. But I needed a help, So I am putting a question.
I have table order_stg as under.
Order_ID event_num rcvng_desk_id custm_3_txt
1 0 ABC
1 1 DEF
1 2 JKH
2 0 DEF
2 1 JKH
I have a stored procedure to update 15M rows in order_Stg table as shown below. It takes 2Hrs to do that.
I have an option of BULK collect. But is there any other way I can bring down this less than 30 minutes using a SQL.
Any help is appreciated.
PROCEDURE UPD_AGG_ORDER_STG IS
CURSOR C_CUR IS
select a.ordr_id,a.rcv_desk_id rcvdesk,b.agg_id aggid,b.desk_id deskid
from order_stg a,desk_agg_map b
where a.rcv_desk_id = b.desk_id
and a.src_sys = 'AGO'
and a.ordr_evnt_num = 0;
V_SQLCODE NUMBER;
V_SQLERRM VARCHAR2(500);
BEGIN
FOR REC IN C_CUR
LOOP
UPDATE ORDER_STG X
SET X.CUSTOM_3_TEXT = rec.aggid
WHERE X.ordr_id = REC.ordr_ID ;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_SQLCODE := SQLCODE;
V_SQLERRM := SQLERRM;
dbms_output.put_line('There is other problem');
raise_application_error(-20002, substr(V_SQLERRM,1,512));
rollback;
return;
END UPD_AGG_ORDER_STG;
Thanks,
PRS
January 14, 2008 - 3:30 pm UTC
first, remove - erase - delete - get rid of that when others
it does NOTHING. It does actually less than something, it removes information from the caller.
second, then erase all of the procedural code and replace it with a single MERGE or UPDATE statement
merge into (thing you are updating)
using (thing you fetch from)
on (the key to put them together)
when matched the update set ....;
I can't use merge
PRS, January 14, 2008 - 5:56 pm UTC
Tom,
I can't use MERGE as it is selecting the record for order vent type zero for a given order id and then updates all records(i.e all events) of that order with that value.
I tried to do with the update in corelated subquery but there it does not help much in performance improvement.
Please let me know if anything else can be done. These table are having 15M to 20M rows per day.
January 15, 2008 - 7:05 am UTC
did you even try the merge or are you presuming you cannot use it???
you give me no tables and inserts to work with to demonstrate how to use merge.
but it will look something like this:
merge into order_stg x
using (
select a.ordr_id,a.rcv_desk_id rcvdesk,b.agg_id aggid,b.desk_id deskid
from order_stg a,desk_agg_map b
where a.rcv_desk_id = b.desk_id
and a.src_sys = 'AGO'
and a.ordr_evnt_num = 0
) REC
on
( rec.ordr_id = x.ordr_id )
when matched then update set custom_3_text = rec.aggid;
sort of like this:
merge into order_stg x
using (
select a.ordr_id,a.rcv_desk_id rcvdesk,b.agg_id aggid,b.desk_id deskid
from order_stg a,desk_agg_map b
where a.rcv_desk_id = b.desk_id
and a.src_sys = 'AGO'
and a.ordr_evnt_num = 0
) REC
on
( rec.ordr_id = x.ordr_id )
when matched then update set custom_3_text = rec.aggid;
We are on 9.2.07
PRS, January 15, 2008 - 10:46 am UTC
Tom,
Thanks for the response. But we are on 9.2.0.7 and that tell me that I have to have when not matched clause.
Thanks,
Prashant R Shah
January 15, 2008 - 12:59 pm UTC
then just add one
look, you are merging into a table from the same table - hence you will never hit the "when not matched" bit *ever* so, just add:
when not matched /* this never happens, please delete when we get current */
then insert (primary_key) values ( NULL );
where primary key is your primary key column...
It is impossible for this query:
select a.ordr_id,a.rcv_desk_id rcvdesk,b.agg_id aggid,b.desk_id deskid
from order_stg a,desk_agg_map b
where a.rcv_desk_id = b.desk_id
and a.src_sys = 'AGO'
and a.ordr_evnt_num = 0to generate a row that doesn't already exist in ORDER_STG
Thanks a Lot
PRS, January 15, 2008 - 1:50 pm UTC
Tom,
Thanks a lot.
PRS
CONVERSION
VINEELA, January 22, 2008 - 7:43 am UTC
oracle sql COLUMNS TO ROW CONVERSION
selecting rows..
Manoj Kaparwan, February 08, 2011 - 6:14 am UTC
Tom,
Thanks for your time.
mndba@ora10g> create table t1 as select * from all_users;
Table created.
mndba@ora10g> create table t2 as select * from all_objects;
Table created.
Using Java we run the querries of the following nature and data is stored into flat files.
mndba@ora10g> select * from t1 where username =?
mndba@ora10g> select * from t2 where owner in ( select username from t1 where username = ?);
Question/s
a) How can we avoid running the subquery in the second query as we have alreday computed the same in first query ( making use of that would be more effiecint here?)
b) I have given as an example for 02 level query.. ( t1 and t2 ..) in real life we may have to read the data upto level t5
i.e. read all related rows from t3 computed from t2 or t1 ( depending upon the relation) in previous steps...likewise.
February 10, 2011 - 3:34 pm UTC
a) that is as efficient as it gets - you are doing it right.
b) that is the way it works, separate queries are just that - separate queries. Unless you can write it as a single query - you do what you are doing.
In 11g, you could use the result_cache hint to have the result sets "cached" - but in general, what you are doing is fine.
selecting rows..
Manoj Kaparwan, February 10, 2011 - 10:30 pm UTC
Many thanks Tom.
regards
selecting rows..
Manoj Kaparwan, February 28, 2011 - 10:31 pm UTC
Tom
Continuing above question..
as I said we have the querries of the following nature.
select * from t1 where username =?
select * from t2 where owner in ( select username from t1 where username = ?);
on t3 we will have subquerries relating t2 and t1.. and likewise.
something like...
select * from t3 where id in ( select id from t2 where id in ( select id from t1) )
Now optmising these queries.
how can I
a) Avoid Subquerries ?
b) IN may be better than EXISTS and vice versa ?
Since these would be dynamic querries , how can we frame optmise querries based on the data it finds during runtime.
regards
March 01, 2011 - 8:59 am UTC
a) why? they are quite natural in SQL and they do what you need them to do.
could we turn them into joins? sure, but the result sql would be ugly. And besides, the optimizer will do it for us if it makes sense.
b) in and exists are semantically equivalent and the optimizer is aware of that and will rewrite the query as appropriate.
see
http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html
analytic functions totally rule, don't they.....
Suresh A, March 03, 2011 - 2:21 pm UTC
Hi Tom,
This question is related to the first Question in this series
I have an audit trail table which contains the following values
Id Date ProjectType
-------------- --------- ----------
5969 08-MAY-10
5969 23-MAY-10 TargetDate
5969 07-JUN-10 TargetDate
5969 22-JUN-10 TargetDate
5969 05-NOV-10 TargetDate
5969 16-NOV-10 Velocity
5969 18-NOV-10 Velocity
5969 23-NOV-10 Velocity
5969 23-NOV-10
5969 23-NOV-10 TargetDate
5969 23-NOV-10 TargetDate
5969 23-NOV-10 TargetDate
5969 23-NOV-10 Velocity
5969 23-NOV-10 Velocity
5969 24-NOV-10 Velocity
5969 24-NOV-10 Velocity
5969 24-NOV-10 Velocity
5970 08-MAY-10
5970 23-MAY-10 TargetDate
5970 07-JUN-10 TargetDate
5970 22-JUN-10 TargetDate
5970 05-NOV-10 TargetDate
5970 16-NOV-10 Velocity
5970 18-NOV-10 Velocity
5970 23-NOV-10 Velocity
5970 23-NOV-10
5970 23-NOV-10 TargetDate
5970 23-NOV-10 TargetDate
5970 23-NOV-10 TargetDate
5970 23-NOV-10 Velocity
5970 23-NOV-10 Velocity
5970 24-NOV-10 Velocity
5970 24-NOV-10 Velocity
5970 24-NOV-10 Velocity
I would like to get the following o/p
Id Date ProjectType
-------------- --------- ----------
5969 08-MAY-10
5969 23-MAY-10 TargetDate
5969 16-NOV-10 Velocity
5969 23-NOV-10
5969 23-NOV-10 TargetDate
5969 23-NOV-10 Velocity
5970 08-MAY-10
5970 23-MAY-10 TargetDate
5970 16-NOV-10 Velocity
5970 23-NOV-10
5970 23-NOV-10 TargetDate
5970 23-NOV-10 Velocity
I need the o/p by removing the duplicate in a series for each Id. (primary key is Id + Date)
How we can achieve this using analytic functions ?
I am using oracle 8i
thanks in advance...
March 03, 2011 - 7:07 pm UTC
not even going to look to see if this is possible since I don't see a create table nor insert into table statements to set up the example.
Also, do you have a case whereby you would/could have:
Id Date ProjectType
-------------- --------- ----------
5969 08-MAY-10
5969 23-MAY-10 TargetDate
5969 07-JUN-10 TargetDate
5969 22-JUN-10 TargetDate
5969 05-NOV-10 TargetDate
5969 16-NOV-10 Velocity
5969 18-NOV-10 Velocity
5969 23-NOV-10 Velocity
5969 23-MAY-11 TargetDate
5969 07-JUN-11 TargetDate
5969 22-JUN-11 TargetDate
where the output would be 4 records for that id, with target date twice?
Date Range
A Reader, March 09, 2011 - 7:09 am UTC
Hi Tom,
I need to write a query to retrive a date range (previous Monday - Sunday) for a given date.
For e.g. if it is today i.e. 09-Mar-2011(Wednesday), the query should return 28-Feb-2011(Monday) & 06-Feb-2011(Sunday).
If it is 06-Mar-2011(Sunday), the query should return 28-Feb-2011(Monday) & 06-Feb-2011(Sunday).
Could you please help.
March 09, 2011 - 9:42 am UTC
ops$tkyte%ORA11GR2> select dt, next_day( dt-14, 'mon' ), next_day( dt-14, 'sun' )
2 from (
3 select sysdate+level-1 dt
4 from dual
5 connect by level <= 10
6 )
7 /
DT NEXT_DAY(DT-14, NEXT_DAY(DT-14,
--------------- --------------- ---------------
Wed 09-mar-2011 Mon 28-feb-2011 Sun 27-feb-2011
Thu 10-mar-2011 Mon 28-feb-2011 Sun 27-feb-2011
Fri 11-mar-2011 Mon 28-feb-2011 Sun 27-feb-2011
Sat 12-mar-2011 Mon 28-feb-2011 Sun 27-feb-2011
Sun 13-mar-2011 Mon 28-feb-2011 Sun 06-mar-2011
Mon 14-mar-2011 Mon 07-mar-2011 Sun 06-mar-2011
Tue 15-mar-2011 Mon 07-mar-2011 Sun 06-mar-2011
Wed 16-mar-2011 Mon 07-mar-2011 Sun 06-mar-2011
Thu 17-mar-2011 Mon 07-mar-2011 Sun 06-mar-2011
Fri 18-mar-2011 Mon 07-mar-2011 Sun 06-mar-2011
10 rows selected.
Date Range
A Reader, March 09, 2011 - 10:45 am UTC
Thanks Tom.
Maybe I haven't explained the requirement well. The query should return Monday to Sunday of the previous week.
In other words, the output should be as below:
DT NEXT_DAY(DT-14, NEXT_DAY(DT-14,
--------------- --------------- ---------------
Mon 07-mar-2011 Mon 28-feb-2011 Sun 06-feb-2011
Tue 08-mar-2011 Mon 28-feb-2011 Sun 06-feb-2011
Wed 09-mar-2011 Mon 28-feb-2011 Sun 06-feb-2011
Thu 10-mar-2011 Mon 28-feb-2011 Sun 06-feb-2011
Fri 11-mar-2011 Mon 28-feb-2011 Sun 06-mar-2011
Sat 12-mar-2011 Mon 28-feb-2011 Sun 06-mar-2011
Regards
March 09, 2011 - 12:46 pm UTC
I'm very very very confused.
You wrote:
For e.g. if it is today i.e. 09-Mar-2011(Wednesday), the query should return 28-Feb-2011(Monday) &
06-Feb-2011(Sunday).
If it is 06-Mar-2011(Sunday), the query should return 28-Feb-2011(Monday) & 06-Feb-2011(Sunday).
well, I did that. Did you really mean:
if today is 9-mar-2011, I want a SET of rows representing all of the days from 28-feb-2011 to 06-feb-2011.
NOT just the two scalar values 28-feb-2011 & 06-feb-2011?
ops$tkyte%ORA11GR2> select trunc(sysdate),
2 next_day( trunc(sysdate)-14, 'mon') +level-1
3 from dual
4 connect by level <= 7;
TRUNC(SYSDATE) NEXT_DAY(TRUNC(
--------------- ---------------
Wed 09-mar-2011 Mon 28-feb-2011
Wed 09-mar-2011 Tue 01-mar-2011
Wed 09-mar-2011 Wed 02-mar-2011
Wed 09-mar-2011 Thu 03-mar-2011
Wed 09-mar-2011 Fri 04-mar-2011
Wed 09-mar-2011 Sat 05-mar-2011
Wed 09-mar-2011 Sun 06-mar-2011
7 rows selected.
Date Range - typo?
Ditto, March 09, 2011 - 2:49 pm UTC
re: Date Range discussion with "A Reader", from Mar 9.
He writes:
"I need to write a query to retrive a date range (previous Monday - Sunday) for a given date."
So based on this, Mar 9 input, should result in range of Feb 28 (Mon) - Mar 6 (Sun).
However, he also writes:
"09-Mar-2011(Wednesday), the query should return 28-Feb-2011(Monday) &
06-Feb-2011(Sunday)."
Feb 6!?!? Huh .. I think he means Mar 6 ... ??
If so ... I think a quick mod to Tom's original query gives the answer:
SQL > l
1 select dt, next_day( dt-14, 'mon' ), next_day( dt-14, 'mon' ) + 6
2 from (
3 select sysdate+level-1 dt
4 from dual
5 connect by level <= 10
6* )
SQL > /
------more------
DT NEXT_DAY(DT NEXT_DAY(DT
----------- ----------- -----------
09-MAR-2011 28-FEB-2011 06-MAR-2011
10-MAR-2011 28-FEB-2011 06-MAR-2011
11-MAR-2011 28-FEB-2011 06-MAR-2011
12-MAR-2011 28-FEB-2011 06-MAR-2011
13-MAR-2011 28-FEB-2011 06-MAR-2011
14-MAR-2011 07-MAR-2011 13-MAR-2011
15-MAR-2011 07-MAR-2011 13-MAR-2011
16-MAR-2011 07-MAR-2011 13-MAR-2011
17-MAR-2011 07-MAR-2011 13-MAR-2011
18-MAR-2011 07-MAR-2011 13-MAR-2011
10 rows selected.
Maybe? Maybe not ... :)
Date Range
A reader, March 10, 2011 - 4:05 am UTC
Thanks Ditto for pointing out, it was indeed a typo. Thanks to both of you, I have got the answer now.
kumar, April 12, 2011 - 9:34 am UTC
Hi,
i am trying to write a query in sql server. if anybody know plz help me.
table has 3 columns(name,dept,id).write a query delete name having id value is 'emp' keep in mind if any dept has one employee and having id value is 'emp' then that should not be deleted.
April 13, 2011 - 9:36 am UTC
i am trying to write a query in sql server
Hmm, you might possibly be confused then - the domain here is "oracle.com"....
this smells just like homework. Homework is meant to be done by the student.
pc, April 14, 2011 - 7:14 am UTC
Hi,
I have one doubt.
HOW TO CONNECT FROM SQL SERVER 2008 TO DB2 UDB ?
April 14, 2011 - 10:14 am UTC
ASK MICROSOFT - THEY PROBABLY KNOW SINCE THEY WROTE SQL SERVER.
You can delete this if you don't have time
PRS, October 22, 2014 - 6:56 pm UTC
I just needed a help and had to come back to this site again. Below query runs for 2 Hrs. Volumes are 50M for order and 15 M for execution. Any help in tuning this is appreciated. If no time, it's ok.
with i0 as (
select distinct a.alert_track, a.scrty_intrl_id, a.trdr_agg_unit from kdd_mnr.FUT_SPFNG_ALERT_HILGT_TMP a
where alert_track in (2,4,6,8)
),
i1 as (
select /*+ index(o1, ORDR_SCRTYID_IDX) */ s.scrty_intrl_id, s.trdr_agg_unit, o1.orig_id, o1.ordr_plcmt_dt, o1.ordr_plcmt_tm new_tm, o1.ordr_seq_id, o1.ordr_orig_unit_qt, o1.cncld_ordr_dt cxl_dt, o1.cncld_ordr_tm cxl_tm,
sum(e1.last_exctn_unit_qt) over (partition by e1.ordr_seq_id) fill_qty
from business.ordr o1, business.execution e1,
(select clndr_dt from mantas.kdd_cal where clndr_nm = 'SYSCAL' and bus_day_age between 1 and 5 and bus_day_fl = 'Y') no_of_days, i0 s
where o1.scrty_intrl_id = s.scrty_intrl_id and o1.cncld_ordr_dt is not null and o1.ordr_plcmt_dt = no_of_days.clndr_dt
and o1.last_sbmsn_chnl_tx in ('CQG','TB','CFOX','ULLink','CME','TT','COMET','MANUAL')
and o1.ordr_seq_id = e1.ordr_seq_id(+)
),
i2 as (
select scrty_intrl_id, orig_id, ordr_plcmt_dt
from i1
where (nvl(fill_qty,0) = 0 or ((nvl(fill_qty,0)*100)/ordr_orig_unit_qt <= 75))
and (f_duration_date_time((select kc.clndr_dt from MANTAS.kdd_cal kc where kc.clndr_day_age = 0 and kc.clndr_nm = 'SYSCAL'), '000000000', cxl_dt, cxl_tm|| '000', 'mmm')/1000) -
(f_duration_date_time((select kc.clndr_dt from MANTAS.kdd_cal kc where kc.clndr_day_age = 0 and kc.clndr_nm = 'SYSCAL'), '000000000', ordr_plcmt_dt, new_tm|| '000', 'mmm')/1000) <= 5
),
i3 as (
select o.scrty_intrl_id, s.trdr_agg_unit, o.ordr_plcmt_dt, count(*) total_cxl_orders
from i2 o, i0 s
where o.scrty_intrl_id = s.scrty_intrl_id
and ((o.orig_id in
(select emp_intrl_id from kdd_mnr.rltd_user where active_flag = 'Y' and (scnro_id is null or (scnro_id = 200000002 and track is null) or (scnro_id = 200000002 and track = 'SEQ-A'))
and rltd_trdr_grp = s.trdr_agg_unit))
OR orig_id = s.trdr_agg_unit )
group by o.scrty_intrl_id, s.trdr_agg_unit, o.ordr_plcmt_dt
),
i4 as (
select /*+ index(o1, ORDR_SCRTYID_IDX) */ s.scrty_intrl_id, s.trdr_agg_unit, o1.ordr_plcmt_dt, o1.ordr_seq_id
from business.ordr o1, i0 s, (select clndr_dt from mantas.kdd_cal where clndr_nm = 'SYSCAL' and bus_day_age between 1 and 5 and bus_day_fl = 'Y') no_of_days
where o1.scrty_intrl_id = s.scrty_intrl_id
and ((o1.orig_id in
(select emp_intrl_id from kdd_mnr.rltd_user where active_flag = 'Y' and (scnro_id is null or (scnro_id = 200000002 and track is null) or (scnro_id = 200000002 and track = 'SEQ-A'))
and rltd_trdr_grp = s.trdr_agg_unit ))
OR o1.orig_id = s.trdr_agg_unit
)
and o1.last_sbmsn_chnl_tx in ('CQG','TB','CFOX','ULLink','CME','TT','COMET','MANUAL')
and o1.ordr_plcmt_dt = no_of_days.clndr_dt
),
i5 as (
select scrty_intrl_id, trdr_agg_unit, ordr_plcmt_dt, count(*) total_orders
from i4
group by scrty_intrl_id, trdr_agg_unit, ordr_plcmt_dt
)
select a1.scrty_intrl_id, a1.trdr_agg_unit, a1.ordr_plcmt_dt, a1.total_orders, c1.total_cxl_orders
from i3 c1, i5 a1
where a1.scrty_intrl_id = c1.scrty_intrl_id (+) and a1.trdr_agg_unit = c1.trdr_agg_unit (+) and a1.ordr_plcmt_dt = c1.ordr_plcmt_dt (+)