ORACLE 8 sql query

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 Received on Wed Jun 28 2006 - 05:50:48 CDT