prasadvvd_at_googlemail.com wrote:
> Hi Guys,
>
> I have following query
> SELECT cust.cust_id
> , bcus.bcus_bus_id bus_id
> , ROLE.role_abbr ROLE
> , TO_CHAR(bcus.bcus_bus_start_date, 'DD/MM/YYYY')
> start_date
> FROM bcus
> , cust
> , cust_addr
> , bcus_addr
> , ttl
> , ctyp
> , suf
> , ROLE
> WHERE bcus.bcus_cust_id = cust.cust_id
> AND cust_addr.addr_cust_id = cust.cust_id
> AND cust_addr.addr_adrt_code = c_address_type_home
> AND bcus_addr.addr_bcus_id(+) = bcus.bcus_id
> AND bcus_addr.addr_adrt_code(+) =
> c_address_type_main_business
> AND cust.cust_ttl_code = ttl.ttl_code(+)
> AND cust.cust_ctyp_code = ctyp.ctyp_code
> AND cust.cust_suf_code = suf.suf_code(+)
> AND bcus.bcus_role_code = ROLE.role_code
> AND ROLE.role_abbr IN (v_role,v_role_sps, ROLE.role_abbr)
> AND bcus.bcus_bus_id = p_bus_id AND
> (bcus_delete_ind IS NULL
> OR
> bcus_delete_ind = 'Y' AND bcus_end_date_ind = 'Y')
> ORDER BY bcus_bus_id, bcus_bus_start_date desc;
>
> my question in the IN Clause above ROLE.role_abbr IN
> (v_role,v_role_sps, ROLE.role_abbr)
>
> I need to retrieve values like
> if v_role is null then retrive all the roles (i.e. from ROLE.role_abbr)
> else v_role has got a value retrieve the same
> and if v_role is 'HK' retrieve values for 'SPS as well..
>
> can anybody give suggestions on this
> we are using oracle 8.
>
> Thanks,
> vvdp
The point of your question is unclear and your version of Oracle not
just unsupported but Jurassic. Seems you should spend more time
getting to 10g than writing new code in dinosaur.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jul 03 2006 - 11:01:08 CDT
