f



Several question about select.

Hello,
I want a solutions for a compicateds sql select statments.

The selects can use anything : views, stored procedures, analytic functions, 
etc...
(not use materialized view, unless it is neccessary).

question 1)
The selects can use anything : views, stored procedures, analytic functions, 
etc...
(not use materialized view, unless it is neccessary).
Suppose I have the folowing select :

select x from t

-- t is a table, and x is numeric column.

The select statement is fixed and cannot be change.
The only change that is allowed is to put a where clause at the end of the 
select.

I want that the where clause will influence the result of the select, like 
the following.

If I add :
where x = 1
The result will be : 1

If I add :
where x = 2
The result will be : 2

etc..

If I could do something like :
select rownum
from t
where rownum = 1

(the where statement works only for rownum = 1, and then the select return 
the value : 1).

The select may use an analitic function etc...
).

If there is any solution for above , I would like to know please + some code 
sample.

question 2)

I have a binary tree.
I want to search the tree in some kind of select statement (Left + Parent + 
Right).
The output should be the binary search of the tree, each row of the select 
return in the right order the search output.

I can use stored procedures, package etc...
but, at the end I want to use one single select statement for that.

How can I do that.
Need code sample, please.

Thanks :) 


0
Eitan
1/25/2006 7:38:19 PM
comp.database.oracle 721 articles. 0 followers. Post Follow

3 Replies
524 Views

Similar Articles

[PageSpeed] 39

Eitan wrote:
> Hello,
> I want a solutions for a compicateds sql select statments.
> 
> The selects can use anything : views, stored procedures, analytic functions, 
> etc...
> (not use materialized view, unless it is neccessary).

Lets stop right here. First I see you posted this to every usenet group
you could spell. That is not going to make you a single friend and an
apology is appropriate.

Second ... if you can use a Stored Procedure in a query I'd be
fascinated to see you syntax.

I'd suggest following your apology to all you post a single new query
asking for help in the appropriate group, c.d.o.server and actually
explain what it is you are doing (WITH CLARITY) and include your version
number. I'm not going to play guessing games with this.
-- 
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
0
DA
1/26/2006 9:08:57 PM
OK.
For the newsgroup - you right.
I realy don't know what correct newsgroup,
Now I sent only to comp.database.oracle.misc
I hop this right.

For anyone who like to know how I use stored proc / packages.
for mypackage :
package mypackage is
   function x(a in number) return number;
   pragma restrict_references(x,WNDS,WNPS);
end package;
....
package body mypackage is
  function  x(a in number) return number is
     res number;
     cursor c is
        select 1 from dual;
  begin
    res:= null;
 open c;
 fetch c into res;
 if res is null then
    res:= 0;
 end if;
 close c;
 return res;

  end;
end package

of course :
create public synonym mypackage  FOR myinstance.mypackage
and
grant execute on mypackage to use_role.

And in sql statement I do :
select mypackage.x(1) from dual;

I know also that package can return a table - I don't know how using it at
select statment
(maybe this is the solution - the result is a view with parameters).

The major problem is,
that I have a fixed select statement (which is built in one program)
and another program which uses the select statement as is,
but can put only a select statement at the end.
I don't have the source of the other program (that put the where statment).

but here is the major problem :
when I use the main select statement, I have also some packages, which I
have written to be used in the sql statement.
The package use some parameters (clients).
I cannot send the parameters just as they are, because, the parameter I sent
should be in the query.
and the only way to send parameters (the end user) is by a where statement.

I need some tricky way to solve the problem :

Select a as x, mypackage.fun1(x)
  from myTable

....
the above is fixed.
The user add :
where x = 123
....

and the select statment will be as I did :
Select a as x, mypackage.fun1(123)
  from myTable
  where x = 123

I didn't find any way to do so.

Need an example, or tricky way to do the above.

Thanks :)


0
Eitan
1/29/2006 8:24:12 AM
Eitan M wrote:

> For anyone who like to know how I use stored proc / packages.
> for mypackage :
> package mypackage is
>    function x(a in number) return number;
>    pragma restrict_references(x,WNDS,WNPS);
> end package;
> ...
> package body mypackage is
>   function  x(a in number) return number is
>      res number;
>      cursor c is
>         select 1 from dual;
>   begin
>     res:= null;
>  open c;
>  fetch c into res;
>  if res is null then
>     res:= 0;
>  end if;
>  close c;
>  return res;
> 
>   end;
> end package
> 
> of course :
> create public synonym mypackage  FOR myinstance.mypackage
> and
> grant execute on mypackage to use_role.
> 
> And in sql statement I do :
> select mypackage.x(1) from dual;
> 
> I know also that package can return a table - I don't know how using it at
> select statment
> (maybe this is the solution - the result is a view with parameters).
> 
> The major problem is,
> that I have a fixed select statement (which is built in one program)
> and another program which uses the select statement as is,
> but can put only a select statement at the end.
> I don't have the source of the other program (that put the where statment).
> 
> but here is the major problem :
> when I use the main select statement, I have also some packages, which I
> have written to be used in the sql statement.
> The package use some parameters (clients).
> I cannot send the parameters just as they are, because, the parameter I sent
> should be in the query.
> and the only way to send parameters (the end user) is by a where statement.
> 
> I need some tricky way to solve the problem :
> 
> Select a as x, mypackage.fun1(x)
>   from myTable
> 
> ...
> the above is fixed.
> The user add :
> where x = 123
> ...
> 
> and the select statment will be as I did :
> Select a as x, mypackage.fun1(123)
>   from myTable
>   where x = 123
> 
> I didn't find any way to do so.
> 
> Need an example, or tricky way to do the above.
> 
> Thanks :)

I have no idea what you are trying to do or what question you are 
asking. I also can't see any point in any of the code you provided.
For example look at this code you supplied:

 >function  x(a in number) return number is
 > res number;
 > cursor c is select 1 from dual;
 > begin
 >   res:= null;  -- this does precisely nothing.
 >   open c;
 >  fetch c into res;
 >  if res is null then
 >     res:= 0;
 >  end if;
 >  close c;
 >  return res;
 >
 >   end;

Let me rewrite your function for you in one line:
res := 1;
you pass in a parameter "a" and then ignore it.
you define a cursor and open it without purpose.
you have an IF for an impossible condition.

Do you have any qualifications to write PL/SQL?

If one of my students, on their midterm, wrote
this I would suggest they leave the program.
-- 
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
0
DA
1/29/2006 7:18:53 PM
Reply: