Re: ORACLE 8 sql query

prasadvvd@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Record source at runtime
    ... Clause, but is in the data requested, so it will return the PKs ... retrieve indexes for Lastname and Firstname. ... had I written the query: ...
    (microsoft.public.access.formscoding)
  • Re: Search Firstname field AND Lastname field?
    ... last name field in my query? ... The WHERE clause is a Boolean logical expression; ... be true in order to retrieve a record, or use the OR operator to ...
    (microsoft.public.access.gettingstarted)
  • Re: Sort Problem when Using Make-Table Query
    ... The only sure way to retrieve the Records in a certain order is to use the ... the safest way is to use the ORDER BY clause. ... "Make-Table Query" in ascending order on a Number field. ... As a work around I ran a query to sort the table. ...
    (microsoft.public.access.queries)
  • ORACLE 8 sql query
    ... I have following query ... my question in the IN Clause above ROLE.role_abbr IN ... I need to retrieve values like ...
    (comp.databases.oracle.misc)
  • 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)