2 Replies Latest reply: Nov 19, 2012 12:43 AM by Sg049 RSS

    Help needed in applying filter condition

    user10566312
      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
          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
            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