View Using Connect BY

From: mike <hillmw_at_charter.net>
Date: 23 Mar 2006 11:02:58 -0800
Message-ID: <1143140578.051158.246120@v46g2000cwv.googlegroups.com>

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 Received on Thu Mar 23 2006 - 13:02:58 CST