6 Replies Latest reply: Jan 24, 2013 4:14 PM by user13653962 RSS

    Decode in Apex

    user13653962
      how can i define the following condition in query definations tab to define query condition for the value which is linked from previous tab/report ,:P2_SR_BUSINESS_UNIT is the Item which are getting value from another report Value.


      BUSINESS_UNIT=DECODE(:P2_SR_BUSINESS_UNIT,'BS&I','Business Sales And Information','WS','Whole Sale')

      I am new in apex and havent had any chance to create a simple report.Version Application Express 4.1.1.00.23
        • 1. Re: Decode in Apex
          xarg-Xrc
          user13653962 wrote:
          how can i define the following condition in query definations tab to define query condition for the value which is linked from previous tab/report ,:P2_SR_BUSINESS_UNIT is the Item which are getting value from another report Value.


          BUSINESS_UNIT=DECODE(:P2_SR_BUSINESS_UNIT,'BS&I','Business Sales And Information','WS','Whole Sale')

          I am new in apex and havent had any chance to create a simple report.Version Application Express 4.1.1.00.23
          By using a case statement in your where clause like this:
          select 
               col1,
               col2 
          from 
               my_table
          where
               case 
                    when :P2_SR_BUSINESS_UNIT = 'BS&I' and BUSINESS_UNIT='Business Sales And Information' then 1
                    when :P2_SR_BUSINESS_UNIT = 'WS' and BUSINESS_UNIT='Whole Sale' then 1
                    ELSE 0
               END = 1
          • 2. Re: Decode in Apex
            user13653962
            the source query within Edit region is not editable , how can i edit it?
            • 3. Re: Decode in Apex
              VC
              Where are you editing it from?

              First edit the page and then edit the region
              • 4. Re: Decode in Apex
                user13653962
                VC wrote:
                Where are you editing it from?

                First edit the page and then edit the region
                Thanks for help , sorry i am lost , i have created this report via wizard and unable to understand yours workaround.
                • 5. Re: Decode in Apex
                  user13653962
                  yours worked , but i have following condition
                  case 
                            when :P2_SR_BUSINESS_UNIT = 'BS&I' then 'Business Sales And Information' else 'Others'          ELSE
                       END
                  it is not not working if there is an Ampersand sign '&' , the same when i run like
                  SR_BUS_UNIT = case when 1 = 1 then 'Business Support and Improvement' else 'Others' End 
                  in SQL we use set scan off to ignore '&' sign , how can we do the same in Apex.
                  • 6. Re: Decode in Apex
                    user13653962
                    it worked using htf.escape_sc('BS&I')

                    Thanks all of you guys help.