1 Reply Latest reply: Dec 1, 2012 2:40 AM by fac586 RSS

    Apex Classic Report- PX_REPORT_SEARCH

    977351
      Hello,

      I'm trying to modify the query on my report. While the default search is quite useful i'd like to modify the query to sort the results based on a score (i've created a context index on a column). so instead of a:

      where (
      instr(upper("COLUMN_A"),upper(nvl(:P2_REPORT_SEARCH,"COLUMN_A"))) > 0 or
      instr(upper("COLUMN_B"),upper(nvl(:P2_REPORT_SEARCH,"COLUMN_B"))) > 0
      )

      use this:

      SELECT SCORE(1), column_a from test_table
      WHERE CONTAINS(column_b, 'text-to-search', 1) > 0
      ORDER BY SCORE(1) DESC;

      I've tried adding the P2_REPORT_SEARCH into the query, but i just can't seem to get it to work. apex keeps giving me errors, SQL workshop either gives me errors or retrieves no rows and I've tried lots of different variations of the query (as sql, as pl/sql) but no matter what I do I can't seem to pass the search term from P2_REPORT_SEARCH into the query.

      Any guidelines of what I'm missing?
        • 1. Re: Apex Classic Report- PX_REPORT_SEARCH
          fac586
          974348 wrote:
          Hello,
          Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already)), and update your forum profile with a real handle instead of "974348".

          When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

          <li>Full APEX version
          <li>Full DB/version/edition/host OS
          <li>Web server architecture (EPG, OHS or APEX listener/host OS)
          <li>Browser(s) and version(s) used
          <li>Theme
          <li>Template(s)
          <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

          With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
          I'm trying to modify the query on my report. While the default search is quite useful i'd like to modify the query to sort the results based on a score (i've created a context index on a column). so instead of a:
          where (
          instr(upper("COLUMN_A"),upper(nvl(:P2_REPORT_SEARCH,"COLUMN_A"))) > 0 or
          instr(upper("COLUMN_B"),upper(nvl(:P2_REPORT_SEARCH,"COLUMN_B"))) > 0 
          )
          use this:
          SELECT SCORE(1), column_a from test_table 
          WHERE CONTAINS(column_b, 'text-to-search', 1) > 0
          ORDER BY SCORE(1) DESC;
          Always post code wrapped in tags<tt>\
          ...\
          </tt> tags
          to preserve formatting and prevent it being mangled by the forum software.
          I've tried adding the P2_REPORT_SEARCH into the query, but i just can't seem to get it to work.
          What have you tried?
          apex keeps giving me errors, SQL workshop either gives me errors
          What errors? When?
          or retrieves no rows and I've tried lots of different variations of the query (as sql, as pl/sql) but no matter what I do I can't seem to pass the search term from P2_REPORT_SEARCH into the query.

          Any guidelines of what I'm missing?
          {message:id=9360002}

          Provide detailed steps to reproduce the problem (including all of the information detailed above), or reproduce it on apex.oracle.com and share a developer login to the workspace here.

          What happens when you use the following SQL report source:
          SELECT SCORE(1), column_a from test_table 
          WHERE CONTAINS(column_b, :P2_REPORT_SEARCH, 1) > 0
          ORDER BY SCORE(1) DESC;