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

.

Relevant Pages

  • Re: On ADSI and LDAP
    ... the problem is how can I retrieve the value for myuser using the ... would be more efficient to use ADO to query AD for the attributes values. ... For more on using ADO, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ...
    (microsoft.public.scripting.vbscript)
  • Re: Internet class differences...
    ... The database and web interface to the server is produced by a different ... Initially a WebClient class was used. ... This worked fine for submitting the query. ... retrieve the results but, instead, is sent to a login page from which it ...
    (microsoft.public.dotnet.general)
  • Re: Column Order Problem
    ... I understand the reasons for not using the BLOB data types but as I ... >> I'm working on required extending an existing query to retrieve some ... The query is written correctly and returns the ...
    (microsoft.public.data.ado)
  • Re: Returning only a subset of groups in AD
    ... Output.WriteLine "There are no members in this group." ... I assume that ADSICommand is an ADO command object, ... value assigned to the CommandText property, which is the ADO query. ... ' Comma delimited list of attribute values to retrieve. ...
    (microsoft.public.scripting.vbscript)
  • Re: VBScript INT Type Mismatch
    ... Here is how I am trying to retrieve the ... The script errors out on that exact line with: ... If I run the query in Query Analyzer it returns a numeric value. ... Dim strConnect, adoRecordset, strQuery, strGroupLocation, intLocation ...
    (microsoft.public.windows.server.scripting)