Re: View Using Connect BY

On 23 Mar 2006 11:02:58 -0800, "mike" <hillmw_at_charter.net> wrote:

>I have a view that is defined by a number of joins and it is good to
>provide that view to the users because it makes it simple to select
>from.
>
>However, I would like to select from that view records that meet
>certain criteria using a connect statement.
>
>My sql would be:
>
>select fld1, fld2, fld3
>from rpt_4
>where fld1 in
>(
> select distinct num
> from orgs
> where struct_id in
> (select struct_id
> from struct
> where chd_struct_id = 138
> start with par_struct_id = 0
> connect by prior chd_struct_id=par_struct_id
> )
>)
>
>This works fine, but I was trying to make it simpler but using some
>kind of function to select from. Something like:
>
>select fld1, fld2, fld3
>from rpt_4
>where fld1 in get_num(138)
>
>get_num() would be a function. I can't to use a function here.
>
>Any ideas that would help is appreciated.
>
>Mike

First of all: the distinct in the outer subquery is redundant. A set is always distinct, so in the context of a subquery Oracle performs an automatic sort-unique.
Secondly, you can user 'parametrized views', if you set up your own context and use the sys_context function to retrieve the value 138. I found this info on (of course) http://asktom.oracle.com

--
Sybrand Bakker, Senior Oracle DBA