Home » SQL & PL/SQL » SQL & PL/SQL » explain query
explain query [message #194934] Tue, 26 September 2006 04:35 Go to next message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,

can any one explain how the following query works in detail.

I know the output of the query but I can't understand how it works.

:text is a variable defined before the query is going to execute


var text varchar2(20);

exec :text:='example';

SELECT SUBSTR(txt, l, 1) x FROM ( SELECT :text txt , LEVEL l FROM dual CONNECT BY LEVEL <= LENGTH(:text) )


if i input the value of text as 'example' the result of the query will be

e
x
a
m
p
l
e

Re: explain query [message #194942 is a reply to message #194934] Tue, 26 September 2006 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Break the query down a bit at a time and you'll see how it works.
SELECT 'Example' txt , LEVEL l FROM dual CONNECT BY LEVEL <= LENGTH('Example');

"TXT"	"L"
"Example"	"1"
"Example"	"2"
"Example"	"3"
"Example"	"4"
"Example"	"5"
"Example"	"6"
"Example"	"7"


The outer query just gets the L'th letter from the string.
Re: explain query [message #194968 is a reply to message #194942] Tue, 26 September 2006 06:01 Go to previous message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,
Thank u for the response but I want to know how the psuedo column level works

Previous Topic: 01013: user requested cancel of current operation
Next Topic: Order of retreival from table
Goto Forum:
  


Current Time: Tue Dec 06 04:17:43 CST 2016

Total time taken to generate the page: 0.10357 seconds