Thanks!
Mike, May 28, 2002 - 2:22 pm UTC
I set cusor_sharing = force for the account that runs this and I have watched the parse stats during the run and I don't seem to be suffering the soft parse hit. Although I didn't know that about REF Cursors not being cached.
As for ROWID (duh?). Thanks, I didn't even think about that. Thanks again.
May 28, 2002 - 7:55 pm UTC
Oh you are -- every row returned is doing a soft parse! If you return 1000 records -- you'll have done 1,000 soft parses. It is there.
how to unpivot this
A reader, April 23, 2003 - 1:55 pm UTC
in some example on this site you have
pivoted a table and got this result
STATUS 0 1 2 3 4 5 6 7 8+
====================================================
FINAL ACTION 0 0 1 0 0 0 0 0 1
how can I unpivot it in a single sql stmt ?
2.) how can I get
1
13
10
any static values ( that i give)
using select stmt..
eg.
select t from dual
which should give me
1
13
10
in column not in row with out using physical table
April 23, 2003 - 7:52 pm UTC
if there 9 columns to "unpivot" then
select c1,
decode( r, 1, value1, 2, value2, 3, value3, ...., 9, value9 )
from T, /* table with 9 columns to unpivot */
(select rownum r from all_objects where rownum <= 9 )
2) eh? why 1, 13, 10.
(dual is by the way -- a physical honest to goodness table -- at least as of 2003)
but -- search this site for str2table, you'll see how
select * from TABLE( cast(str2table('1,13,10') as myTableType) );
can work for you.
can't fine it
A reader, April 24, 2003 - 12:34 pm UTC
I searched for str2table got 7 results and
all of them are saying search for str2table
i did not find the def. ?
can you give me link ?
or put it in asktom.oracle.com/~tkyte
April 24, 2003 - 12:40 pm UTC
thanks,
A reader, April 24, 2003 - 1:14 pm UTC
That's because I searched, as you've said, for
**str2table**
and you searced on (str2tbl) may be...
Thanks, any way
April 24, 2003 - 1:36 pm UTC
nope, searched for str2table - just did it again to test.
How to do this - columns to rows
subramanian, July 22, 2003 - 8:51 pm UTC
I have a table with 5 columns.
say trans_dt, col1, col2, col3, col4.
The first column is of date type and the rest are number types.
Normal select would give the following results.
Date displayed in dd.mm.yyyy format.
Trans_dt col1 col2 col3 col4
-------- ------ ----- ------ --------
31.03.2000 100 200 300 400
31.03.2001 500 600 700 800
31.03.2002 400 300 200 900
31.03.2003 500 100 300 200
Could you give me a query to display the
results in the format given below
trans_dt trans_dt trans_dt
col1
col2
col3
col4
July 23, 2003 - 7:44 am UTC
that is generally the domain of a reporting tool. However, if you KNOW the range of trans_dt, you can do it. the following walks through a transpose and pivot:
ops$tkyte@ORA920LAP> select * from t;
TRANS_DT C1 C2 C3 C4
--------- ---------- ---------- ---------- ----------
23-JUL-03 432 3823 159 7175
23-JUL-02 9356 2635 5324 6586
23-JUL-01 7073 279 3574 4816
23-JUL-00 1510 6416 5987 9003
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trans_dt,
2 'c' || r c,
3 decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
4 from t,
5 (select rownum r
6 from all_objects
7 where rownum <= 4)
8 /
TRANS_DT C C_VAL
--------- ----- ----------
23-JUL-03 c1 432
23-JUL-02 c1 9356
23-JUL-01 c1 7073
23-JUL-00 c1 1510
23-JUL-03 c2 3823
23-JUL-02 c2 2635
23-JUL-01 c2 279
23-JUL-00 c2 6416
23-JUL-03 c3 159
23-JUL-02 c3 5324
23-JUL-01 c3 3574
23-JUL-00 c3 5987
23-JUL-03 c4 7175
23-JUL-02 c4 6586
23-JUL-01 c4 4816
23-JUL-00 c4 9003
16 rows selected.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select c,
2 ( decode( trans_dt, to_date('23-jul-2003'), c_val ) ) c2003,
3 ( decode( trans_dt, to_date('23-jul-2002'), c_val ) ) c2002,
4 ( decode( trans_dt, to_date('23-jul-2001'), c_val ) ) c2001,
5 ( decode( trans_dt, to_date('23-jul-2000'), c_val ) ) c2000
6 from (
7 select trans_dt,
8 'c' || r c,
9 decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
10 from t,
11 (select rownum r
12 from all_objects
13 where rownum <= 4)
14 )
15 /
C C2003 C2002 C2001 C2000
----- ---------- ---------- ---------- ----------
c1 432
c1 9356
c1 7073
c1 1510
c2 3823
c2 2635
c2 279
c2 6416
c3 159
c3 5324
c3 3574
c3 5987
c4 7175
c4 6586
c4 4816
c4 9003
16 rows selected.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select c,
2 max( decode( trans_dt, to_date('23-jul-2003'), c_val ) ) c2003,
3 max( decode( trans_dt, to_date('23-jul-2002'), c_val ) ) c2002,
4 max( decode( trans_dt, to_date('23-jul-2001'), c_val ) ) c2001,
5 max( decode( trans_dt, to_date('23-jul-2000'), c_val ) ) c2000
6 from (
7 select trans_dt,
8 'c' || r c,
9 decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
10 from t,
11 (select rownum r
12 from all_objects
13 where rownum <= 4)
14 )
15 group by c
16 /
C C2003 C2002 C2001 C2000
----- ---------- ---------- ---------- ----------
c1 432 9356 7073 1510
c2 3823 2635 279 6416
c3 159 5324 3574 5987
c4 7175 6586 4816 9003
ops$tkyte@ORA920LAP>
Subramanian, July 23, 2003 - 8:37 pm UTC
Thank you very much.
This is what I wanted.
great answer
Alessandro, December 14, 2004 - 9:57 am UTC
pivot a result set
Gowri, May 24, 2006 - 3:59 am UTC
Its really helpful me in getting the desired result set in time.
Slightly different situation
Geetha, May 25, 2006 - 2:22 pm UTC
I have a different situation greatly appreciate your help in advance.
I have an ID
12345
67890
66666
ID has many licenses in different states
12345 WA 12222 1/1/2000
12345 WA 32222 1/1/2001
12345 AK 51111 1/1/1999
67890 WA 61111 1/1/2000
67890 ID 71111 1/1/2001
66666 WA 70000 1/1/2000
66666 WA 90000 1/1/2001
66666 AK 80000 1/1/2002
The rules I have are as follows,
I need to fetch 2 licenses only.
The precendence is all WA states comes first.
I need the latest dated license numbers.
The result needed is in the following format...
12345 WA 32222 1/1/2001 WA 12222 1/1/2000
67890 WA 61111 1/1/2000 ID 71111 1/1/2001
66666 WA 90000 1/1/2001 AK 80000 1/1/2002
-- Note even though 66666 has 2 WA records, AK should be displayed because that has the latest date.
Is this even doable in one SQL statement?
May 25, 2006 - 5:47 pm UTC
yes, this is doable in a single sql statement....
absolutely doable....
and I'd even show you how to use row_number() over (partition by id order by case when state = 'WA' then 1 else 2 end, date_col DESC ) to do it
if I only had a table and some inserts...
but - maybe that is enough hints to get you started on your own..
Wonderful...
Geetha, May 30, 2006 - 2:10 pm UTC
I have got the desired result. Let me know if I got it right or is there a better way to do it.
In realtime this would be performed for 70,000 records (IDs) am hoping it would not affect performance?
Thanks a lot for the wonderful response. After I got the "doable" from you (THE GURU himself) is when I pursued this further. :)
Here is the script...
drop table aa
/
drop table ab
/
create table aa (id number)
/
insert into aa values (12345)
/
insert into aa values (67890)
/
insert into aa values (66666)
/
create table ab (id number, state varchar2(2), license number, dt date)
/
insert into ab values (12345, 'WA', 12222, to_date('1/1/2000','mm/dd/yyyy'))
/
insert into ab values (12345, 'WA', 32222, to_date('1/1/2001','mm/dd/yyyy'))
/
insert into ab values (12345, 'AK', 51111, to_date('1/1/1999','mm/dd/yyyy'))
/
insert into ab values (67890, 'WA', 61111, to_date('1/1/2000','mm/dd/yyyy'))
/
insert into ab values (67890, 'ID', 71111, to_date('1/1/2001','mm/dd/yyyy'))
/
insert into ab values (66666, 'WA', 70000, to_date('1/1/2000','mm/dd/yyyy'))
/
insert into ab values (66666, 'WA', 90000, to_date('1/1/2001','mm/dd/yyyy'))
/
insert into ab values (66666, 'AK', 80000, to_date('1/1/2002','mm/dd/yyyy'))
/
commit
/
select aa_t.id,
max(aa_t.first_state) first_state,
max(aa_t.first_license) first_license,
max(aa_t.first_dt) first_dt,
max(aa_t.second_state) second_state,
max(aa_t.second_license) second_license,
max(aa_t.second_dt) second_dt
from
(select aa.id,
decode(ab_t.orderby, 1, ab_t.state) first_state,
decode(ab_t.orderby, 1, ab_t.license) first_license,
decode(ab_t.orderby, 1, ab_t.dt) first_dt,
decode(ab_t.orderby, 2, ab_t.state) second_state,
decode(ab_t.orderby, 2, ab_t.license) second_license,
decode(ab_t.orderby, 2, ab_t.dt) second_dt
from aa,
(select ab.id,
ab.state,
ab.license,
ab.dt,
row_number()
over (partition by
ab.id order by
dt desc) orderby
from ab
where state = 'WA') ab_t
where ab_t.orderby in (1, 2)
and aa.id = ab_t.id)aa_t
group by aa_t.id
/
ID FI FIRST_LICENSE FIRST_DT SE SECOND_LICENSE SECOND_DT
---------- -- ------------- --------- -- -------------- ---------
12345 WA 32222 01-JAN-01 WA 12222 01-JAN-00
66666 WA 90000 01-JAN-01 WA 70000 01-JAN-00
67890 WA 61111 01-JAN-00
May 30, 2006 - 6:43 pm UTC
70,000 records is pretty small - subsecond? maybe not, maybe yes, but pretty fast.
This is the right way, the way I was alluding to (you could, but don't have to, remove a layer of inline views by doing max( decode( ... )) )
row_number() applied against 'WA' only ...
Gabe, May 31, 2006 - 12:04 am UTC
What happened to ... <quote>Note even though 66666 has 2 WA records, AK should be displayed because that has the latest date</quote>?
Don't think all is done here.
What about this ?
Michel Cadot, May 31, 2006 - 2:58 am UTC
If i clearly understand the problem, you always have the last license then all the WA ones from the last one to the first one and then the other licenses in the same order.
Finally you display it with WA first even if it is not the last date.
This can be done with:
SQL> with
2 step1 as ( -- number the rows
3 select id, state, license, dt,
4 row_number()
5 over (partition by id order by dt desc) rndt,
6 row_number()
7 over (partition by id
8 order by decode(state,'WA',1,2), dt desc) rnst
9 from ab
10 ),
11 step2 as ( -- keep only potential ones and number them
12 select id, state, license, dt,
13 case
14 when rndt = 1 and state = 'WA' then 1
15 when rndt = 1 and state != 'WA' then 2
16 when lag(state) over (partition by id order by rndt)
17 = 'WA'
18 then rndt
19 else rndt-1
20 end rn
21 from step1
22 where rndt = 1 or rnst <= 2
23 )
24 select id,
25 max(decode(rn,1,state)) first_state,
26 max(decode(rn,1,license)) first_license,
27 max(decode(rn,1,dt)) first_dt,
28 max(decode(rn,2,state)) second_state,
29 max(decode(rn,2,license)) second_license,
30 max(decode(rn,2,dt)) second_dt
31 from step2
32 where rn <= 2 -- only the two first rows per id
33 group by id
34 order by id
35 /
ID FI FIRST_LICENSE FIRST_DT SE SECOND_LICENSE SECOND_DT
---------- -- ------------- --------- -- -------------- ---------
12345 WA 32222 01-JAN-01 WA 12222 01-JAN-00
66666 WA 90000 01-JAN-01 AK 80000 01-JAN-02
67890 WA 61111 01-JAN-00 ID 71111 01-JAN-01
Regards
Michel
Absolutely...
Geetha, May 31, 2006 - 2:03 pm UTC
Gabe, you are right. I had a different set of requirements to begin with the one exactly answered by Michael. (That was very cool Michael)
Now the requirements have changed (!@#$ happens all the time, don't they) they want only 2 latest dated WA licenses.
Thanks Tom, Gabe and Michael for the wonderful help.
comma separated columns,
A reader, June 09, 2006 - 1:19 pm UTC
I have a table (say XYZ) that stores the column names of other tables (say ABC,PQR,EFG)
select * from xyz;
TABLE_NAME COLUMN_NAMES
---------- -----------
ABC C1,C2
PQR C1,C2,C3
EFG C2
Our purpose is to create the primary key on the tables ABC,PQR,EFG for those columns listed in the COLUMN_NAMES attribute in XYZ table.
The column_names is comma separated.
Prior to creating the primary key we have to check for duplicates (if it exists delete the duplicate records), check for null values (delete the record if it has null value) and then create a primary key.
I am able to check for the duplicate records dynamically in my pl/sql program but I am stuck on how to figure out whether there is a null value in any of the column names.
Your help is highly appreciated.
thanks,
June 09, 2006 - 1:36 pm UTC
can we fix your data model and correct this before it gets too widely used????
This data model is so very flawed in so many ways.
(and you know the add primary key command can find your records in error using "exceptions into"
less code = less bugs, more time to fix your data model!
more code = more bugs, less time to do the important stuff!
and I'm seriously curious as to how you know automagically what record to delete upon finding a duplicate primary key - if it is "any of them", then why not delete everything? Your data is already rather "random")
Very useful
Vinayak Pai, July 03, 2006 - 4:21 am UTC
I wrote the code to understand your query. At first shot I did not understand your query ;)
The way you gor r is really cool :)
CREATE TABLE t
(trans_dt date,c1 number,c2 number ,c3 number ,c4 number)
INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-03',432,3823,159,7175);
INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-02',9356,2635,5324,6586);
INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-01',7073,279,3574,4816);
INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-00',1510,6416,5987,9003);
select trans_dt,
'c' || r c,
decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
from t,
(select rownum r
from all_objects
where rownum <= 4)
Reader, January 23, 2009 - 1:00 pm UTC
create table tst
(first_nm varchar2(20)
,last_nm varchar2(20)
);
insert into tst
values
('system1','redhat');
insert into tst
values
('system2','ubuntu');
insert into tst
values
('system3','mac');
commit;
SQL> select * from tst;
FIRST_NM LAST_NM
-------------------- --------------------
system1 redhat
system2 ubuntu
system3 mac
I want to display as follows:
values
------------------------
system1 |system2 |system3
redhat |ubuntu |mac
can you please let me know how to write query for this?
January 23, 2009 - 2:06 pm UTC
ops$tkyte%ORA10GR2> select decode( col, 1, fname, 2, lname ) data
2 from (
3 select substr(sys_connect_by_path(first_nm,'|'),2) fname,
4 substr(sys_connect_by_path(last_nm,'|'),2) lname
5 from (
6 select first_nm, last_nm, row_number() over (order by first_nm) rn, count(*) over () cnt
7 from tst
8 )
9 where rn = cnt
10 start with rn = 1
11 connect by prior rn+1 = rn
12 ),
13 (select 1 col from dual union all select 2 col from dual)
14 /
DATA
------------------------------
system1|system2|system3
redhat|ubuntu|mac
Reader, January 25, 2009 - 9:11 am UTC
Tom,
In regards to the above question, can you please let me know if the query can be written without using sys_connect_by_path function?
January 25, 2009 - 11:31 am UTC
yes, it can.
I know of at least two or three more approaches. One assumes you know the number of rows in the table upfront - the other relies on your ability to install a small bit of plsql.
search site for
pivot
or search site for
stragg
Reader, April 13, 2009 - 2:47 pm UTC
create table tab_tst
(val varchar2(20)
,min_price number
,max_price number
,ts_created timestamp(6)
);
insert into tab_tst
values
('ABC',100,200,to_timestamp('20090401 9:00:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('ABC',200,300,to_timestamp('20090401 9:20:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 9:30:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 15:00:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 15:15:00.000','yyyymmdd hh24:mi:ss.ff'))
insert into tab_tst
values
('PQR',400,500,to_timestamp('20090401 9:30:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('PQR',500,600,to_timestamp('20090401 12:00:00.000','yyyymmdd hh24:mi:ss.ff'));
insert into tab_tst
values
('PQR',600,800,to_timestamp('20090401 13:00:00.000','yyyymmdd hh24:mi:ss.ff'));
commit;
I have to get results like this:
dt VAL STR sl_no
20090401 ABC 09:00|ABC|100|200&09:20|ABC|200|300&09:300|ABC|300|400& 0
20090401 ABC 15:00|ABC|300|400&15:15|300|400& 1
20090401 PQR 09:30|PQR|400|500&12:00|500|600&13:00|PQR|600|800 0
Each record for ABC can have 3 rows combined by pipe and seperated by ampersand with sl_no starting with 0. Can you tell how to arrive at this?
April 13, 2009 - 5:43 pm UTC
you have not described this output sufficiently for anyone to make heads or tails of it.
You need many more words here.
it seems you might want to pivot on the hour? I'm guessing, totally.
or maybe, group every three records together? I don't know.
what the heck is sl_no and where did it come from. It just sort of appears in the output? Or are we supposed to assign an increasing sl_no to each output record with the same "val"?
Reader, April 13, 2009 - 5:47 pm UTC
Hello Tom,
Sorry for not sending the output correctly.
I need to sort the data by hour:minute and then group 3 records into one. I need to add an
additional column in the end once the grouping is done starting with 0 which is the sl_no.
April 14, 2009 - 9:26 am UTC
it is not that you did not send the output correctly, it is that the question - as phrased - had lots of ambiguity to it, anyone that answered would be GUESSING as to what you really want.
and it is still ambiguous. Ok - we sort by hour:minute (really or do we sort by timestamp - including the DAY???? you have a date in the output...).
Ok, we group three records into one. But, you have this DAY field in the output. Do you mean to use a key of YYYYMMDD + VAL - do we partition the data by those two elements - or is it just by VAL. If just by VAL, how do we derive the DT field in the output???????
Not sure what you want - really. Phrasing requirements, explaining precisely what you need to do, is 99% of what our jobs are. Writing code - EASY. Getting specifications you can write code from - darn near impossible sometimes.
I'll guess - you mean to break the data up by trunc(ts_created,'dd') and val.
Then, order the data within each group by ts_created from small to big.
Then taking each set of records three at a time in each group - pivot them.
If that is not what you want, you can figure out what to do from the following - since you would never use code you don't understand, you'll understand what it is doing - observe the technique and then you can apply that technique to solve your problem - if I guessed wrong.
ops$tkyte%ORA10GR2> select to_char(dt,'yyyymmdd'),
2 val,
3 ts0||'|'||val||'|'||min_price0||'|'||max_price0||'&' ||
4 case when ts1 is not null then ts1||'|'||val||'|'||min_price1||'|'||max_price1||'&' end ||
5 case when ts2 is not null then ts2||'|'||val||'|'||min_price2||'|'||max_price2||'&' end str,
6 sl_no
7 from (
8 select dt,
9 val,
10 max(decode(rn2,0,min_price)) min_price0,
11 max(decode(rn2,0,max_price)) max_price0,
12 max(decode(rn2,0,ts)) ts0,
13 max(decode(rn2,1,min_price)) min_price1,
14 max(decode(rn2,1,max_price)) max_price1,
15 max(decode(rn2,1,ts)) ts1,
16 max(decode(rn2,2,min_price)) min_price2,
17 max(decode(rn2,2,max_price)) max_price2,
18 max(decode(rn2,2,ts)) ts2,
19 sl_no
20 from (
21 select val, min_price, max_price, to_char(ts_created,'hh24:mi') ts, trunc(ts_created,'dd') dt,
22 row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created) rn,
23 mod(row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created)-1,3) rn2,
24 trunc((row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created)-0.1)/3) sl_no
25 from tab_tst
26 )
27 group by dt, val, sl_no
28 )
29 order by dt, val, sl_no
30 /
TO_CHAR( VAL STR SL_NO
-------- -------------------- --------------------------------------------------------- ----------
20090401 ABC 09:00|ABC|100|200&09:20|ABC|200|300&09:30|ABC|300|400& 0
20090401 ABC 15:00|ABC|300|400&15:15|ABC|300|400& 1
20090401 PQR 09:30|PQR|400|500&12:00|PQR|500|600&13:00|PQR|600|800& 0
For the same scenario please let me know the sql if the rows count changes dynmically
A reader, October 28, 2012 - 2:24 pm UTC
select * from t;
TRANS_DT C1 C2 C3 C4
--------- ---------- ---------- ---------- ----------
23-JUL-03 432 3823 159 7175
23-JUL-02 9356 2635 5324 6586
23-JUL-01 7073 279 3574 4816
23-JUL-00 1510 6416 5987 9003
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trans_dt,
2 'c' || r c,
3 decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
4 from t,
5 (select rownum r
6 from all_objects
7 where rownum <= 4)
8 /
TRANS_DT C C_VAL
--------- ----- ----------
23-JUL-03 c1 432
23-JUL-02 c1 9356
23-JUL-01 c1 7073
23-JUL-00 c1 1510
23-JUL-03 c2 3823
23-JUL-02 c2 2635
23-JUL-01 c2 279
23-JUL-00 c2 6416
23-JUL-03 c3 159
23-JUL-02 c3 5324
23-JUL-01 c3 3574
23-JUL-00 c3 5987
23-JUL-03 c4 7175
23-JUL-02 c4 6586
23-JUL-01 c4 4816
23-JUL-00 c4 9003
16 rows selected.
For the same scenario please let me know the sql if the rows count changes dynmically
October 28, 2012 - 11:17 pm UTC
I don't know what you mean, it doesn't matter how many rows you have in T?