This discussion is archived
2 Replies Latest reply: Nov 18, 2012 10:43 PM by Sg049 RSS

Help needed in applying filter condition

user10566312 Newbie
Currently Being Moderated
I need to apply a filter condition depending on the database environment:
1. Only if the database name is UAT then tb1.CREATED_DT > 23-OCT-2012.
2. Only if the database name is OAT then tb1.application_no in ('1' ,'2')
3. If the database is some other database then don't apply these conditions.
  • 1. Re: Help needed in applying filter condition
    Manik Expert
    Currently Being Moderated
    we dont know what exactly you are trying to solve, are you appending these conditons dynamically?

    may be this inbuilt oracle package : UTL_INADDR can help you...

    Please post complete query / requirement.

    e.g.
    SELECT CASE
              WHEN UTL_INADDR.GET_HOST_NAME = 'xur0000dor' THEN 'UAT'
              WHEN UTL_INADDR.GET_HOST_NAME = 'xur0001dor' THEN 'OAT'
           END
      FROM DUAL;
    Cheers,
    Manik.
  • 2. Re: Help needed in applying filter condition
    Sg049 Explorer
    Currently Being Moderated
    Try this
    Select  tb1.*,tb2.* from tb1
    join tb2 
    on (tb1.col1=tb2.col1)
    join (select  case when global_name='UATDB' then 'UAT'
                             when global_name='QATDB' then 'QAT' 
                             else 'OTHERDB' end DB_NAME  from global_name) DB
    on 1=1
    where (DB.DB_NAME='UATDB' and tb1.CREATED_DT > 23-OCT-2012)
    or (DB.DB_NAME='QATDB' and tb1.application_no in ('1' ,'2'))
    or (DB.DB_NAME='OTHERDB');
     
    Note:Not tested the code

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points