Brilliant!!!
A reader, May 06, 2003 - 8:10 pm UTC
Superb solution.. Tom you are a magician!!!
A reader, May 06, 2003 - 9:10 pm UTC
Splendid
reader, May 07, 2003 - 2:29 am UTC
Hey Tom,
Good day I have been reading the posting on this site quite frequently, most of the things you tell are just superb is it all by practice and do you recommend some book or document for tricks like these. Again you are simply GREAT.....
Sami, May 07, 2003 - 4:35 am UTC
Beautiful!!
A reader, May 07, 2003 - 8:47 am UTC
Cardinality
A reader, December 28, 2004 - 2:46 pm UTC
select 'A' code from dual UNION ALL
select 'B' code from dual UNION ALL
select 'C' code from dual
9iR2:
When doing this for larger datasets and as part of a more complex query, I find that the CBO assumes a cardinality of 24K for the above query (8K for each of the selects from dual). This is probably related to my 8K blocksize.
Since 3 << 24000, this throws off the overall query plan and results in a suboptimal query.
Doesnt the CBO know that DUAL is a special 1x1 table so the cardinality is always 1 and not 8k?
How can I workaround this?
Thanks
December 28, 2004 - 3:41 pm UTC
there is the option to gather stats on dual or, use your own table "mydual" (as an IOT it would be great, less LIO -- that dual union all does lots of work under the covers) or use the cardinality hint.
(or 10g where this will be "fast dual" instead of just "dual")
see </code>
https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html <code>
query plans with temporary tables
DUAL
A reader, December 28, 2004 - 4:10 pm UTC
"Doesnt the CBO know that DUAL is a special 1x1 table so the cardinality is always 1 and not 8k?"
In
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1562813956388 <code>
you said that "The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan"
That doesnt seem to be true, right?
December 28, 2004 - 6:10 pm UTC
the examples there showed that deep down, it knew. I was doing RBO examples as well. It is true -- but the card= isn't computed that way.
ops$tkyte@ORA9IR2> select /*+ all_rows */ * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=17 Card=8168 Bytes=16336)
1 0
TABLE ACCESS (FULL) OF 'DUAL' (Cost=17 Card=8168 Bytes=16336)
but at other levels "it is" -- as demonstrated
Deep down?
A reader, December 28, 2004 - 7:41 pm UTC
"the examples there showed that deep down, it knew"
Huh? Which example shows that "deep down, it knew"?
That thread talks about the "fast dual" in 10g as "an optimization that turns an access against dual into a function call really. It need not goto the database data *at all*"
But in 9iR2, CBO seems to default to card=8K for a 8K blocksize database for any access of DUAL?
So, at what "other levels" does the CBO in 9iR2 show a card=1?
December 28, 2004 - 7:44 pm UTC
click link above -- shows a select * from dual returns a single row, regardless of how many are there.
I said above "but not for card="
How about this soln ?
A reader, December 28, 2004 - 11:28 pm UTC
Guru,
Any thoughts on the follwing soln for the Original post.( will work only in 9i)
SQL> create table temp_row ( pk number , sum_a number ,sum_b number ,sum_c number) ;
Table created.
SQL> create table temp_more_rows ( pk number , code varchar2(1) , sum_code number) ;
Table created.
SQL> insert into temp_row select object_id , object_id + 5 ,object_id + 10 ,object_id +15 from user_objects where rownum < 11 ;
10 rows created.
SQL> commit ;
SQL>INSERT ALL
INTO temp_more_rows
VALUES (pk, 'A', sum_a)
INTO temp_more_rows
VALUES (pk, 'B', sum_b)
INTO temp_more_rows
VALUES (pk, 'C', sum_c)
SELECT *
FROM temp_row;
30 rows created.
SQL> COMMIT ;
Hows that ?Will it be more efficient or better than the decode soln for 50 million rows if it were 9i?.
Thanx ..Always learning new things from you.
December 29, 2004 - 9:55 am UTC
absolutely, in 9i with the new (well, now old) multi-table insert that would be the natural way to do it.
INSERT ALL vs. MERGE
A reader, December 29, 2004 - 10:28 am UTC
Yes, using INSERT ALL to insert one row N times in another table is a good idea, but I really want to "merge" into my target table, so I cant use this.
I am using Tom's original idea to do
merge into target
using (select .. from t,<3 row table>)
...
i.e. exploding T to 3T and merging into my target table.
Thanks
Variation on this
A reader, January 02, 2005 - 3:59 pm UTC
create table t (
acct_no int,
trade_date date,
settle_date date,
enter_date date,
amt int);
I want to rearrange this row as (max 3) rows as follows
acct_no,the_date,trade_amt,enter_amt,settle_amt
If all the 3 dates are the same for a row, then the output would contain only 1 row
If all 3 are different, then there should be 3 rows in the output with each date in the "the_date" column and the same amount in all 3 amt columns.
Help? Thanks
January 02, 2005 - 7:59 pm UTC
what "row", you say "i want to rearrange this row as (max 3) rows"
what "row" -- I see a create table, I see no tuples.
this is so vague as to be "not understandable"
If you mean:
I would like to select from this set the set of rows that have
a) the max trade_date or
b) the max settle_date or
c) the max enter_date
removing duplicates (and understanding the size of this set could be much much larger than "3" since 5000 record could have the max trade_date)
then you want something like
select distinct *
from (select * from t where trade_date = (select max(trade_date) from t)
union all
select * from t where settle_date = (select max(settle_date) from t)
union all
select * from t where enter_date = (select max(enter_date) from t)
)
Variation on this
A reader, January 02, 2005 - 9:01 pm UTC
create table t (
acct_no int,
trade_date date,
settle_date date,
enter_date date,
amt int);
alter session set nls_date_format='mm/dd/yyyy';
insert into t values (1,'1/1/2004','1/2/2004','1/3/2004',1);
I want the 1 row about to generate 3 rows (since all 3 dates are different). These 3 rows would look like
acct_no,the_date,trade_amt,enter_amt,settle_amt
1,1/1/2004,1,1,1
1,1/2/2004,1,1,1
1,1/3/2004,1,1,1
For a row like
insert into t values (2,'1/1/2004','1/1/2004','1/3/2004',2);
I want 2 rows output
2,1/1/2004,2,2,2
2,1/3/2004,2,2,2
But if acct_no=1 were to have rows like
insert into t values (1,'1/1/2004','1/2/2004','1/3/2004',1);
insert into t values (1,'1/2/2004','1/3/2004','1/4/2004',2);
these rows would "combine" and the output should be
acct_no,the_date,trade_amt,enter_amt,settle_amt
1,1/1/2004,1,1,1
1,1/2/2004,3,3,3 -- 1 + 2
1,1/3/2004,3,3,3 -- 1 + 2
1,1/4/2004,2,2,2
Thanks
January 02, 2005 - 10:38 pm UTC
so, basically -- table T, join to a 3 row table (to transpose, turn the three dates into three rows)
for any given original row ONLY KEEP the first occurrence of a date (any row of the three is ok to keep, just keep one of them)
we can do that with some analytics. Add rowid as a unique key (don't see one so it'll have to do). cartesian product the rows with select 1 from dual union all select 2 from dual ... to get three rows.
Now, partition that result set by ROWID, DATE and assign row_number..
Only keep row_numbers() = 1 (first one we see)
Then sum...
easy:
ops$tkyte@ORA9IR2> alter session set nls_date_format='mm/dd/yyyy';
Session altered.
ops$tkyte@ORA9IR2> insert into t values (1,'1/1/2004','1/2/2004','1/3/2004',1);
1 row created.
ops$tkyte@ORA9IR2> insert into t values (1,'1/2/2004','1/3/2004','1/4/2004',2);
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select acct_no, dt, sum(amt), sum(amt), sum(amt)
2 from (
3 select rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) dt, acct_no, amt,
4 row_number()
5 over (partition by rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) order
6 by rid ) rn
7 from (select rowid rid, t.* from t ) T,
8 (select 1 r from dual union all select 2 from dual union all select 3 from dual ) d
9 )
10 where rn = 1
11 group by acct_no, dt
12 /
ACCT_NO DT SUM(AMT) SUM(AMT) SUM(AMT)
---------- ---------- ---------- ---------- ----------
1 01/01/2004 1 1 1
1 01/02/2004 3 3 3
1 01/03/2004 3 3 3
1 01/04/2004 2 2 2
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t;
2 rows deleted.
ops$tkyte@ORA9IR2> insert into t values (2,'1/1/2004','1/1/2004','1/3/2004',2);
1 row created.
ops$tkyte@ORA9IR2> select acct_no, dt, sum(amt), sum(amt), sum(amt)
2 from (
3 select rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) dt, acct_no, amt,
4 row_number()
5 over (partition by rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) order
6 by rid ) rn
7 from (select rowid rid, t.* from t ) T,
8 (select 1 r from dual union all select 2 from dual union all select 3 from dual ) d
9 )
10 where rn = 1
11 group by acct_no, dt
12 /
ACCT_NO DT SUM(AMT) SUM(AMT) SUM(AMT)
---------- ---------- ---------- ---------- ----------
2 01/01/2004 2 2 2
2 01/03/2004 2 2 2
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t;
1 row deleted.
ops$tkyte@ORA9IR2> insert into t values (1,'1/1/2004','1/2/2004','1/3/2004',1);
1 row created.
ops$tkyte@ORA9IR2> select acct_no, dt, sum(amt), sum(amt), sum(amt)
2 from (
3 select rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) dt, acct_no, amt,
4 row_number()
5 over (partition by rid, decode( r, 1, trade_date, 2, settle_date, 3, enter_date ) order
6 by rid ) rn
7 from (select rowid rid, t.* from t ) T,
8 (select 1 r from dual union all select 2 from dual union all select 3 from dual ) d
9 )
10 where rn = 1
11 group by acct_no, dt
12 /
ACCT_NO DT SUM(AMT) SUM(AMT) SUM(AMT)
---------- ---------- ---------- ---------- ----------
1 01/01/2004 1 1 1
1 01/02/2004 1 1 1
1 01/03/2004 1 1 1
What I need
A reader, January 03, 2005 - 1:25 pm UTC
What I need (I think) is
CREATE OR REPLACE VIEW v AS
SELECT
acct_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END my_date,
rec_type,
SUM (amt) amt
FROM t, (SELECT 's' rec_type FROM dual
UNION ALL
SELECT 'e' FROM dual
UNION ALL
SELECT 't' FROM dual)
GROUP BY
acct_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END,
rec_type;
select acct_no,my_date,
sum(decode(rec_type,'s',amt,0)) settle_amt,
sum(decode(rec_type,'e',amt,0)) enter_amt,
sum(decode(rec_type,'t',amt,0)) trade_amt
from v
group by acct_no,my_date;
But this basically turns 1 row into 3 and then back into 1 again. So the end result is the same "grain" as what I start with. Thats why I asked you if there was any other way to "rearrange" the row the way I want?
January 03, 2005 - 10:23 pm UTC
umm, i did the work above ?
A reader, January 03, 2005 - 10:44 pm UTC
Doh, you are right, of course. I was hoping there was a way to not turn column->row->column but I guess not given my requirements. Thanks
Materialized View?
A reader, January 04, 2005 - 3:28 pm UTC
create table t (
acct_no int,
sec_no int,
trade_date date,
settle_date date,
enter_date date,
amt int);
Suppose I add "sec_no" to the table as above and include it along with acct_no in the group by.
Now, I want to get summaries from the final view by either acct_no or sec_no. i.e. rollup one of the 2. The data volume is large so the rollup takes too long at run-time.
Is there a way to define a MV such that query you suggested would use this MV when doing either of the rollups? Would the DECODE() stuff preclude using a fast (on commit) refresh on the MV?
Thanks
January 05, 2005 - 8:59 am UTC
you could aggregate by acct_no and sec_no and then just aggregate at run time the MV by acct_no or by sec_no
or you could simply have two MV's
Or you could use group by rollup()
MV
A reader, January 05, 2005 - 9:39 am UTC
I am new to this MV stuff and GROUP BY ROLLUP stuff, I read thru the docs but didnt quite grok it.
Could you please elaborate on each of the options you suggest?
To recap, here is what I have
create table t (
acct_no int,
sec_no int,
trade_date date,
settle_date date,
enter_date date,
amt int);
CREATE OR REPLACE VIEW v AS
SELECT
acct_no,
sec_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END my_date,
rec_type,
SUM (amt) amt
FROM t, (SELECT 's' rec_type FROM dual
UNION ALL
SELECT 'e' FROM dual
UNION ALL
SELECT 't' FROM dual)
GROUP BY
acct_no,
sec_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END,
rec_type;
I need to use the above view and aggregate on 3 levels
1. acct_no,sec_no
2. acct_no
3. sec_no
using the following
select acct_no,sec_no,my_date,
sum(decode(rec_type,'s',amt,0)) settle_amt,
sum(decode(rec_type,'e',amt,0)) enter_amt,
sum(decode(rec_type,'t',amt,0)) trade_amt
from v
group by acct_no,sec_no,my_date;
Are MVs the right answer here, how can I use them? If I cant use fast refresh then MVs are out since the data volume is simply too large.
Thanks for any help
Thanks
January 05, 2005 - 10:48 am UTC
if you need aggregated data to two different levels (group by a, group by b) - you can simply use two MV's (that should be relatively obvious -- two creates, different aggregates)
to play with the rollup, simply;
group by rollup( a,b,c )
and add "grouping(a), grouping(b), grouping(c)" to the select list so you can get an idea what it is doing.
perhaps grouping sets is even more appropriate:
scott@ORA9IR2> select grouping(job), grouping(deptno),
2 job, deptno, sum(sal)
3 from emp
4 group by grouping sets( (job),(deptno) )
5 /
GROUPING(JOB) GROUPING(DEPTNO) JOB DEPTNO SUM(SAL)
------------- ---------------- --------- ---------- ----------
0 1 ANALYST 6000
0 1 CLERK 4150
0 1 MANAGER 8275
0 1 PRESIDENT 5000
0 1 SALESMAN 5600
1 0 10 8750
1 0 20 10875
1 0 30 9400
8 rows selected.
check out the data warehousing guide -- you'll find useful stuff in there.
CUBE and ROLLUP
A reader, January 05, 2005 - 5:31 pm UTC
I am having trouble with the cube/rollup/grouping sets extensions
My GROUP BY is
group by
acct_num,
sec_num,
case rec_type
when 's' then settle_date
when 'e' then enter_date
end,
rec_type
For a specified acct_num & sec_num, this returns 1 row
1. Now when I change the GROUP BY above to GROUP BY ROLLUP, I expected more rows, as successive levels are rolled up. But the output was still 1 row
2. When I did GROUP BY CUBE, I did get 2 rows. But only the rec_type was rolled up, not the rest of the columns. Shouldnt I get more rows?
Thanks
January 05, 2005 - 8:08 pm UTC
simple example please -- make it short, concise, yet 100% complete -- so we can just cut and paste and run it, really really small -- but 100% complete.
(like I try to give to you)
A reader, January 05, 2005 - 8:29 pm UTC
create table tt (
acct_no int,
sec_no int,
trade_date date,
settle_date date,
enter_date date,
amt int);
alter session set nls_date_format='mm/dd/yyyy';
insert into tt values (1,1,'1/1/2004','1/1/2004','1/1/2004',1);
select * from (
SELECT
acct_no,
sec_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END my_date,
rec_type,
SUM (amt) amt
FROM tt, (SELECT 's' rec_type FROM dual
UNION ALL
SELECT 'e' FROM dual
UNION ALL
SELECT 't' FROM dual)
GROUP BY
(acct_no,
sec_no,
CASE rec_type
WHEN 's' THEN settle_date
WHEN 'e' THEN enter_date
WHEN 't' THEN trade_date
END,
rec_type)
) where acct_no=1 and sec_no=1 and my_date='1/1/2004'
ACCT_NO SEC_NO MY_DATE R AMT
--------- ---------- ---------- - ----------
1 1 01/01/2004 e 1
1 1 01/01/2004 s 1
1 1 01/01/2004 t 1
SQL> select * from (
2 SELECT
3 acct_no,
4 sec_no,
5 CASE rec_type
6 WHEN 's' THEN settle_date
7 WHEN 'e' THEN enter_date
8 WHEN 't' THEN trade_date
9 END my_date,
10 rec_type,
11 SUM (amt) amt
12 FROM tt, (SELECT 's' rec_type FROM dual
13 UNION ALL
14 SELECT 'e' FROM dual
15 UNION ALL
16 SELECT 't' FROM dual)
17 GROUP BY cube
18 (acct_no,
19 sec_no,
20 CASE rec_type
21 WHEN 's' THEN settle_date
22 WHEN 'e' THEN enter_date
23 WHEN 't' THEN trade_date
24 END,
25 rec_type)
26 ) where acct_no=1 and sec_no=1 and my_date='1/1/2004'
27 /
ACCT_NO SEC_NO MY_DATE R AMT
---------- ---------- ---------- - ----------
1 1 01/01/2004 e 1
1 1 01/01/2004 s 1
1 1 01/01/2004 t 1
1 1 01/01/2004 3
SQL> select * from (
2 SELECT
3 acct_no,
4 sec_no,
5 CASE rec_type
6 WHEN 's' THEN settle_date
7 WHEN 'e' THEN enter_date
8 WHEN 't' THEN trade_date
9 END my_date,
10 rec_type,
11 SUM (amt) amt
12 FROM tt, (SELECT 's' rec_type FROM dual
13 UNION ALL
14 SELECT 'e' FROM dual
15 UNION ALL
16 SELECT 't' FROM dual)
17 GROUP BY rollup
18 (acct_no,
19 sec_no,
20 CASE rec_type
21 WHEN 's' THEN settle_date
22 WHEN 'e' THEN enter_date
23 WHEN 't' THEN trade_date
24 END,
25 rec_type)
26 ) where acct_no=1 and sec_no=1 and my_date='1/1/2004'
27 /
ACCT_NO SEC_NO MY_DATE R AMT
---------- ---------- ---------- - ----------
1 1 01/01/2004 e 1
1 1 01/01/2004 s 1
1 1 01/01/2004 t 1
1 1 01/01/2004 3
1. Why does the CUBE and ROLLUP give the same results?
2. Why dont they rollup to higher levels i.e. just acct_no, acct_no+sec_no? Why is just the 'rec_type' being squished away?
Thanks
January 05, 2005 - 8:40 pm UTC
1) cube and rollup are totally different -- cube gives every dimension, rollup rolls up
cube(a,b) will give you aggregates (a,b), (a), (b), ()
rollup(a,b) will give you aggreates (a,b), (a), ()
2) why the where clause that removes rows?
where acct_no=1 and sec_no=1 and my_date='1/1/2004'
why grouping rolling up by acct_no or sec_no (which would mean the other is NULL) if you are going to where on them?
pl/sql block updation
NLPrasad, January 06, 2005 - 7:02 am UTC
I wrote pl/sql block. I got the following result.
declare
cursor c1 is SELECT claimno, LFUSERID FROM D_CLAIM
WHERE
lactioncode like 'S_W%'
and rownum < 11;
cursor c2 is (SELECT 'HARSHA' name FROM DUAL
union
select 'RAJESHRE' from dual
union
select 'VARUN' from dual);
c_rec c1%rowtype;
c_rec1 c2%rowtype;
begin
open c1;
loop
fetch c1 into c_rec;
exit when c1%notfound;
open c2;
loop
fetch c2 into c_rec1;
exit when c2%notfound;
dbms_output.put_line( c_rec.claimno || ' - ' ||
c_rec1.name);
end loop;
close c2;
end loop;
close c1;
end;
for this bolck the result as follows.. here the name repeate all the 10 rows
334471168700 - HARSHA
334471168700 - RAJESHRE
334471168700 - VARUN
334475785910 - HARSHA
334475785910 - RAJESHRE
334475785910 - VARUN
334271162130 - HARSHA
334271162130 - RAJESHRE
334271162130 - VARUN
334240401300 - HARSHA
334240401300 - RAJESHRE
334240401300 - VARUN
400604237770 - HARSHA
400604237770 - RAJESHRE
400604237770 - VARUN
334659644260 - HARSHA
334659644260 - RAJESHRE
334659644260 - VARUN
3345C9900840 - HARSHA
3345C9900840 - RAJESHRE
3345C9900840 - VARUN
334271112280 - HARSHA
334271112280 - RAJESHRE
334271112280 - VARUN
334516310341 - HARSHA
334516310341 - RAJESHRE
334516310341 - VARUN
321671602980 - HARSHA
321671602980 - RAJESHRE
321671602980 - VARUN
but I want like this. one name for one id.. and so on.
334471168700 - HARSHA
334475785910 - RAJESHRE
z334271162130 - VARUN
334240401300 - HARSHA
400604237770 - HARSHA
334659644260 - RAJESHRE
3345C9900840 - HARSHA
334271112280 - HARSHA
334516310341 - VARUN
321671602980 - HARSHA
thanks
January 06, 2005 - 10:57 am UTC
sorry, but that is so convuluted -- not really sure what to say
other than "join", let the DATABASE JOIN, databases are BEST at doing joins, you are not.
you desired output seems extremely "not logical". no idea how you derived it.
pl/sql block
NLP, January 07, 2005 - 2:29 am UTC
I wrote pl/sql block.
declare
cursor c1 is SELECT claimno, LFUSERID FROM D_CLAIM
WHERE
lactioncode like 'S_W%'
and rownum < 11;
cursor c2 is (SELECT 'HARSHA' name FROM DUAL
union
select 'RAJESHRE' from dual
union
select 'VARUN' from dual);
c_rec c1%rowtype;
c_rec1 c2%rowtype;
begin
open c1;
loop
fetch c1 into c_rec;
exit when c1%notfound;
open c2;
loop
fetch c2 into c_rec1;
exit when c2%notfound;
dbms_output.put_line( c_rec.claimno || ' - ' ||
c_rec1.name);
end loop;
close c2;
end loop;
close c1;
end;
The above Pl/SQL bock,I got the following result.
Here the each calim name gives three names. as follows..
334471168700 - HARSHA
334471168700 - RAJESHRE
334471168700 - VARUN
334475785910 - HARSHA
334475785910 - RAJESHRE
334475785910 - VARUN
334271162130 - HARSHA
334271162130 - RAJESHRE
334271162130 - VARUN
334240401300 - HARSHA
334240401300 - RAJESHRE
334240401300 - VARUN
400604237770 - HARSHA
400604237770 - RAJESHRE
400604237770 - VARUN
334659644260 - HARSHA
334659644260 - RAJESHRE
334659644260 - VARUN
3345C9900840 - HARSHA
3345C9900840 - RAJESHRE
3345C9900840 - VARUN
334271112280 - HARSHA
334271112280 - RAJESHRE
334271112280 - VARUN
334516310341 - HARSHA
334516310341 - RAJESHRE
334516310341 - VARUN
321671602980 - HARSHA
321671602980 - RAJESHRE
321671602980 - VARUN
but , the three names repate for these 10 claimno repatively. Like in the following.
334471168700 - HARSHA
334475785910 - RAJESHRE
334271162130 - VARUN
334240401300 - HARSHA
400604237770 - RAJESHRE
334659644260 - VARUN
3345C9900840 - HARSHA
334271112280 - RAJESHRE
334516310341 - VARUN
321671602980 - HARSHA
thanks
January 07, 2005 - 9:12 am UTC
why would my response change from above?
Union
Vithal, February 07, 2005 - 4:22 am UTC
Tom,
I want to make a query which will give me the data as
Empno | ename |sal | sales_acount | Group
100 VMK 100 10001 A3
using 3 tab
1) Employee
2) Sales
3) Goup_dec
But there is no relation between these three tab
I want combind three query
1) select empno,ename from emp;
2) select sales_acount from sales;
3) select group from group_dec;
Can u sagest me how to get this information
The final output should come like....
Empno | ename |sal | sales_acount | Group
100 VMK 100 10001 A3
Thanks
February 07, 2005 - 5:01 am UTC
hmmm, i want data from three tables that are wholly unrelated.
I don't know how to even approach answering that.
And the question to the answer is...
Billy, February 07, 2005 - 5:16 am UTC
Tom said:
> hmmm, i want data from three tables that are wholly
> unrelated. I don't know how to even approach answering
> that
Sheez Tom, you're either getting old, or have mislaid your copy of The DBA From Hell manual. ;-)
Answer: To join 3 tables with nothing in common and that aree totally unrelated, you will use.. CARTESIAN JOINS!!
PS. Sometimes such an answer is better, as after hours of frustration struggling with performance and ploughing through meaningless results, it forces them to re-evaulate the question. Er.. I hope. Even if not, it still gives me the satisfation to watch them bleed (their choice). What!? Me, a twisted DBA/developer? Never! :-)
February 07, 2005 - 5:38 am UTC
oh, i know it was a cartesian join, i'm just afraid to tell them to do it ;)
Making up data
A reader, March 03, 2005 - 8:43 pm UTC
I have a table like
create table t
(
acctno int,
the_date date,
balance int
);
It is "sparse" i.e. every date doesnt have a balance for every account.
How can I write a query that given any 2 dates will show a "balance" for all calendar dates between those 2 dates? If there is no balance for a given date, use the previous day's balance. Finally, after making up this intermediate data, get the average balance over the number of days between the 2 specified dates.
Help? Thanks
March 03, 2005 - 9:28 pm UTC
version?
easier/more performant in 10g
but doable in 9i and before
Making up data
A reader, March 03, 2005 - 9:44 pm UTC
Version 9.2.0.6
March 04, 2005 - 7:55 am UTC
my assumption:
T is big....
A months worth of data is small relative
the_date,acctno are indexed and "where the_date between :x and :y" would tend to use the index.
if there was no balanace on :x for an acctno, you would want to "reach back" and find it.
We'll start with this data:
ops$tkyte@ORA10G> select * from t;
ACCTNO THE_DATE BALANCE
---------- --------- ----------
1 24-NOV-04 50
1 04-MAR-05 100
1 19-MAR-05 200
2 16-AUG-04 25
2 09-MAR-05 100
2 19-MAR-05 300
6 rows selected.
<b>and use these dates as the report window, the month of march in this case:</b>
ops$tkyte@ORA10G> variable startdt varchar2(20)
ops$tkyte@ORA10G> variable enddt varchar2(20)
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec :startdt := to_char(trunc(sysdate,'mm'),'yyyymmdd');
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> exec :enddt := to_char(last_day(sysdate),'yyyymmdd');
PL/SQL procedure successfully completed.
<b>this is the 9i query, we need to
a) generate a set of dates to cover the period of interest, the with dates does that.
b) get a set of unique acctno's -- you can use any query you want to accomplish that, I used the set of distinct acctno's from t above -- if you have a better table (an accounts table for example), use it
c) cartesian product the dates with the acctno's, we now have a complete set of dates/accounts -- we can outer join to this.
We are now at "DATES_BY_ACCT" in the query:
</b>
ops$tkyte@ORA10G> with
2 dates
3 as
4 (select to_date(:startdt,'yyyymmdd')+rownum-1 dt
5 from dual
6 connect by 1=1
7 and level <=
8 to_date(:enddt,'yyyymmdd' )
9 -to_date(:startdt,'yyyymmdd')+1 ),
10 accts
11 as
12 (select distinct acctno
13 from t),
14 dates_by_acct
15 as
16 (select *
17 from dates, accts)
<b>this is where the real query begins now, you'll probably want to read from the inside out so:
a) lines 38, 54 -- outer join T to DATES_BY_ACCT to get the balances filled in when they exist. Also, the case statement on line 40 says "if no balance on the start date, please find the max date for this account such that the date is less than the start date -- we'll need to pick up that balance in a minute"
b) lines 27, 36 -- pick up the balance (if you have an index on (acctno,the_date) and an index on (the_date,acctno) -- yes, both ways, this'll be fairly effcient)
c) lines 18, 26 -- employ a carry down -- get the max DT/BALANCE and substr the balance back out</b>
18 select acctno,
19 dt,
20 to_number( substr(
21 max( case when balance is not null
22 then to_char(dt,'yyyymmdd') || balance
23 end )
24 over (partition by acctno
25 order by dt), 9) ) balance
26 from (
27 select acctno,
28 dt,
29 case when the_date_to_look_for is not null
30 then (select balance
31 from t t3
32 where t3.acctno = x.acctno
33 and t3.the_date =
34 x.the_date_to_look_for)
35 else balance
36 end balance
37 from (
38 select dates_by_acct.acctno,
39 dates_by_acct.dt,
40 case when t.balance is null
41 and dates_by_acct.dt =
42 to_date(:startdt,'yyyymmdd')
43 then (select max(the_date)
44 from t t2
45 where t2.acctno = dates_by_acct.acctno
46 and t2.the_date <
47 to_date(:startdt,'yyyymmdd') )
48 end the_date_to_look_for,
49 balance
50 from dates_by_acct left outer join t
51 on (dates_by_acct.acctno = t.acctno
52 and
53 dates_by_acct.dt = t.the_date)
54 ) x
55 ) y
56 order by acctno, dt
57 /
ACCTNO DT BALANCE
---------- --------- ----------
1 01-MAR-05 50
1 02-MAR-05 50
1 03-MAR-05 50
1 04-MAR-05 100
...
1 17-MAR-05 100
1 18-MAR-05 100
1 19-MAR-05 200
...
1 31-MAR-05 200
2 01-MAR-05 25
...
2 07-MAR-05 25
2 08-MAR-05 25
2 09-MAR-05 100
...
2 18-MAR-05 100
2 19-MAR-05 300
...
2 31-MAR-05 300
62 rows selected.
<b>Now, in 10g we have two new things
a) partitioned outer joins
b) IGNORE NULLS
that can be used to simplify this. We still need the dates to outer join to, but we don't need the set of accts, nor do we cartesian product -- we "outer join to T for each partition of T by acctno"
Also, the carry down becomes much easier with LAST_VALUE(balance IGNORE NULLS)</b>
ops$tkyte@ORA10G> with
2 dates
3 as
4 (select to_date(:startdt,'yyyymmdd')+rownum-1 dt
5 from dual
6 connect by 1=1
7 and level <=
8 to_date(:enddt,'yyyymmdd' )
9 -to_date(:startdt,'yyyymmdd')+1 )
10 select acctno,
11 dt,
12 last_value(balance ignore nulls)
13 over (partition by acctno
14 order by dt) balance
15 from (
16 select acctno,
17 dt,
18 case when the_date_to_look_for is not null
19 then (select balance
20 from t t3
21 where t3.acctno = x.acctno
22 and t3.the_date =
23 x.the_date_to_look_for)
24 else balance
25 end balance
26 from (
27 select t.acctno,
28 dates.dt,
29 case when t.balance is null
30 and dates.dt = to_date(:startdt,'yyyymmdd')
31 then (select max(the_date)
32 from t t2
33 where t2.acctno = t.acctno
34 and t2.the_date <
35 to_date(:startdt,'yyyymmdd') )
36 end the_date_to_look_for,
37 balance
38 from dates left outer join t partition by (acctno)
39 on (dates.dt = t.the_date)
40 ) x
41 ) y
42 order by acctno, dt
43 /
ACCTNO DT BALANCE
---------- --------- ----------
1 01-MAR-05 50
1 02-MAR-05 50
1 03-MAR-05 50
1 04-MAR-05 100
1 05-MAR-05 100
1 06-MAR-05 100
1 07-MAR-05 100
1 08-MAR-05 100
1 09-MAR-05 100
1 10-MAR-05 100
1 11-MAR-05 100
1 12-MAR-05 100
1 13-MAR-05 100
1 14-MAR-05 100
1 15-MAR-05 100
1 16-MAR-05 100
1 17-MAR-05 100
1 18-MAR-05 100
1 19-MAR-05 200
1 20-MAR-05 200
1 21-MAR-05 200
1 22-MAR-05 200
1 23-MAR-05 200
1 24-MAR-05 200
1 25-MAR-05 200
1 26-MAR-05 200
1 27-MAR-05 200
1 28-MAR-05 200
1 29-MAR-05 200
1 30-MAR-05 200
1 31-MAR-05 200
2 01-MAR-05 25
2 02-MAR-05 25
2 03-MAR-05 25
2 04-MAR-05 25
2 05-MAR-05 25
2 06-MAR-05 25
2 07-MAR-05 25
2 08-MAR-05 25
2 09-MAR-05 100
2 10-MAR-05 100
2 11-MAR-05 100
2 12-MAR-05 100
2 13-MAR-05 100
2 14-MAR-05 100
2 15-MAR-05 100
2 16-MAR-05 100
2 17-MAR-05 100
2 18-MAR-05 100
2 19-MAR-05 300
2 20-MAR-05 300
2 21-MAR-05 300
2 22-MAR-05 300
2 23-MAR-05 300
2 24-MAR-05 300
2 25-MAR-05 300
2 26-MAR-05 300
2 27-MAR-05 300
2 28-MAR-05 300
2 29-MAR-05 300
2 30-MAR-05 300
2 31-MAR-05 300
62 rows selected.
<b>Now, if my assumptions are not 'true', we might do this differently -- if the index to access by "the_date" would not be used, if this is going to full scan, I might do this differently</b>
Making up data
A reader, March 04, 2005 - 11:40 am UTC
create table t
(
acctno int,
the_date date,
balance int
);
I think I misspoke. Let me put it this way. If the account has a balance for that day, the table will have it. The only 2 cases the table wont have a record for that account is
a) when it is not a business day or
b) the account doesnt have a balance.
For (a), I just want to get the previous business day's balance. If no record for the previous business day, its the same as (b)
For (b), return 0
Is your answer still the same?
It seems awfully complex! Cant we join with a calendar table with a business_day=1 flag to indicate business days or something and use that in the above mix? [I have such a table handy]
Thanks
March 04, 2005 - 1:16 pm UTC
what if the account has a balance last week, but your range of dates is yesterday, today and tomorrow
is it still zero?
Making up data
A reader, March 04, 2005 - 1:34 pm UTC
Yes, its still zero. If the table doesnt have a record for that account & date, make up a 0 row. Otherwise, get it from the table.
Thanks
March 04, 2005 - 2:03 pm UTC
ops$tkyte@ORA9IR2> with
2 dates
3 as
4 (select to_date(:startdt,'yyyymmdd')+rownum-1 dt
5 from dual
6 connect by 1=1
7 and level <=
8 to_date(:enddt,'yyyymmdd' )
9 -to_date(:startdt,'yyyymmdd')+1 ),
10 accts
11 as
12 (select distinct acctno
13 from t),
14 dates_by_acct
15 as
16 (select *
17 from dates, accts)
18 select acctno,
19 dt,
20 nvl(
21 to_number( substr(
22 max( case when balance is not null
23 then to_char(dt,'yyyymmdd') || balance
24 end )
25 over (partition by acctno
26 order by dt), 9) ), 0 ) balance
27 from (
28 select dates_by_acct.acctno,
29 dates_by_acct.dt,
30 balance
31 from dates_by_acct left outer join t
32 on (dates_by_acct.acctno = t.acctno
33 and
34 dates_by_acct.dt = t.the_date)
35 ) x
36 order by acctno, dt
37 /
ACCTNO DT BALANCE
---------- --------- ----------
1 01-MAR-05 0
1 02-MAR-05 0
1 03-MAR-05 0
1 04-MAR-05 100
1 05-MAR-05 100
1 06-MAR-05 100
......
Making up data
A reader, March 04, 2005 - 3:02 pm UTC
Thanks
1. My real table t has many more columns so I guess I would need to get them all in the select distinct in lines 10-13?
2. What if I want
(ACCTNO,DT,BALANCE) as a view where I can plugin the 2 dates of interest?
Thanks
March 04, 2005 - 6:14 pm UTC
1) no, lines 28 and 16 maybe -- but not the set of distinct ACCTNO, that is the "key" -- acctno/date
2) that would be a parameterized view, if you search for that term on this site, you'll get some ideas how that can sort of be done.
how to do this strictly in PL/SQL block
Raul, January 16, 2007 - 2:08 pm UTC
suppose i have a table
flow_schedules ( batch_id, schedule_number, hanger_type, component_details )
the data in the table is like
100 1 2 1111-1112-1113-1114-1115
100 2 2 1001-1002-1003-1004-1005
i need to convert the data like this
100 1 2 1111
100 1 2 1112
100 1 2 1113
100 1 2 1114
100 1 2 1115
100 2 2 1001
100 2 2 1002
100 2 2 1003
100 2 2 1004
100 2 2 1005
i need to achieve this strictly in a pl/sql block
as i was doing it in a single select statement