RE: The With Clause (Oracle)

RE: The With Clause (Oracle)

 

  

Ed

>> "1. What is this "with" clause in Oracle? Ok."
Well, "with" clause is NOT LIKE a temporary table. It is in-line view with no physical contents unlike temporary tables.

Anand Prakash

-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x73880988.[Email address protected]
Sent: Friday, October 06, 2006 3:34 AM
To: LazyDBA Discussion
Subject: RE: The With Clause (Oracle)


Ahhhhh, yes! Let me see! Aye cha! Hehe.


1. What is this "with" clause in Oracle? Ok.

2. What version was it first used in Oracle? (What version - 9.0, 9.1,
9.2)

3. What was the first database to use the "with"? (Oracle, SQL Server,
DB2)

4. Is it a command or function?

6. Is it straight SQL or PL-SQL?

7. What are the advantages of using it? Ok.

8. What are the disadvantages of using it? Where are the
disadvantages?



-----Original Message-----
From: Ashutosh Wankhade
[mailto:oracledba-ezmlmshield-x23081528.[Email address protected]
Sent: Friday, October 06, 2006 6:27 AM
To: LazyDBA Discussion
Subject: RE: The With Clause (Oracle)


1. What is this "with" clause in Oracle?
It is like a temporary table to hold the values of the select query.
Like

WITH temp AS (SELECT * FROM EMP) SELECT * FROM temp;
Will give you the same output as SELECT * FROM EMP;
It can handy in sinarios where we want to fetch data from two or more
tables which is to be used for other manipulations.

Let's have something in detail about WITH clause.

What was the first database to use the "with"?
With temp as query which is a oracle 9 Feature .

Starting with a simple Example how this query works.

1. With temp as (select * from emp ) select * from temp;

2.with temp as (select empno from emp) select empno from temp;



3.With Department as (select deptno Dmp from emp union select Deptno Dmp
from Dept) select Dmp from Department;



This temp table can be used as a temp Variable to retrive the data of
one or more tables.
See one complex query which executes in interation to find out the
position of Commas in a string.

Create table test2 (test1 varchar2(100);

insert into table2 values('adfafasf,adfasfads,adfad,gdf,dfgfg');

commit;

4.with iter as
(
select level pos
from dual
connect by level <= 100
)
select text1, 'comma found at '|| to_char(pos) result from (select
test2.text1, iter.pos, substr(test2.text1,iter.pos,1) c
from test2, iter
where iter.pos <= length(test2.text1)
) x
where c = ','
order by 1
/


Thanks and Regards

Ashutosh

-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x93374382.[Email address protected]

Sent: Friday, October 06, 2006 3:37 PM
To: LazyDBA Discussion
Subject: The With Clause (Oracle)

To All,
Well, well, well! It's good to be back! Can you hear me now? What's
in your wallet? Hehe. It's Friday! It's quiz time! Can you win the
Coke? Hehe.

1. What is this "with" clause in Oracle?

2. What version was it first used in Oracle?

3. What was the first database to use the "with"?

4. Is it a command or function?

5. Is it straight SQL or PL-SQL?

4. What are the advantages of using it?

5. What are the disadvantages of using it?


My young, junior DBAs loved this question! I sure do miss them! Let me
end now before tears startup!



Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply use the link below. Thank you in
advance for completing the survey.



http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



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

Tech Mahindra, formerly Mahindra-British Telecom.


Disclaimer:

This message and the information contained herein is proprietary and
confidential and subject to the Tech Mahindra policy statement, you may
review at <a
href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahind
ra.com/Disclaimer.html</a> externally and <a
href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahind
ra.com/Disclaimer.html</a> internally within Tech Mahindra.

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


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
==============================================================================


Oracle LazyDBA home page