Oracle APEX 4.2 Custom search in classic report

Question

I am using Oracle APEX 4.2. I want to implement a search function in my classic report. I have a working code that retrieve data and the search function works perfectly. But when I want to use an order by clause inside that code I get "no data found" how can I implement an order by clause into this code so my report will be sorted correctly and the search function will still work correctly.

Here is the working code below that allows you to search the classic report:

 declare
     a_query varchar2(5000);
     this_strin varchar2(50);
 begin
  a_query := 'select flight_nmbr, sequence_nmbr'||
         'from flights '||
         'where sequence_nmbr >= 0'||
         'and data_version_name = '''||
         :P3_DATA_VERSION || '''';
 if :P3_SEARCH_NUMBER is not null then
   if instr(:P3_SEARCH_NUMBER, '%') > 0 then
     this_strin := :P3_SEARCH_NUMBER;
   else
     this_strin := '%'||:P3_SEARCH_NUMBER||'%';
   end if;
    a_query := a_query||chr(10)||
            ' and flight_nmbr like '''|| upper(this_strin) ||'''';
   end if;
   return a_query;
  end;

Here is same piece of code that does says "no data found" when I add the order by clause to the query:

 declare
     a_query varchar2(5000);
     this_strin varchar2(50);
 begin
  a_query := 'select flight_nmbr, sequence_nmbr'||
         'from flights '||
         'where sequence_nmbr >= 0'||
         'and data_version_name = '''||
         'order by sequence_nmbr 1'|| ------------------Order by clause
         :P3_DATA_VERSION || '''';
 if :P3_SEARCH_NUMBER is not null then
   if instr(:P3_SEARCH_NUMBER, '%') > 0 then
     this_strin := :P3_SEARCH_NUMBER;
   else
     this_strin := '%'||:P3_SEARCH_NUMBER||'%';
   end if;
    a_query := a_query||chr(10)||
            ' and flight_nmbr like '''|| upper(this_strin) ||'''';
   end if;
   return a_query;
  end;

Answer 1

Not related to your question, but - why don't you switch to interactive report? It offers much more than a classic report, don't you think?

As of your problem: if you look at query that fails, its SELECT looks like this (I've removed single quotes):

and data_version_name =  ||
order by sequence_nmbr 1 || ------------------Order by clause
:P3_DATA_VERSION         || 
and flight_nmbr like upper(this_strin)

Either you didn't post actual code, or this is generally wrong - I hope you see what is wrong here. ORDER BY should be the last clause. Besides, what's that "1" doing at the end of the ORDER BY?

I'd suggest you to first display contents of the A_QUERY, check whether it works OK (in SQL*Plus, TOAD, SQL Developer or any other tool you use), and - once you're satisfied with it - let it work in Apex.

Answer 2

Obviously, your problem is here:

begin
  a_query := 'select flight_nmbr, sequence_nmbr'||
         'from flights '||
         'where sequence_nmbr >= 0'||
         'and data_version_name = '''||   -- double qoute!!!
         'order by sequence_nmbr 1'|| ------------------Order by clause
         :P3_DATA_VERSION || '''';

Due to that redundant double quote, your resulting query looks like this:

select flight_nmbr, sequence_nmbr
  from flights
 where sequence_nmbr >= 0
   and data_version_name = 'order by sequence_nmbr 1<value of P3_DATA_VERSION item>';

As soon as the table has no value order by sequence_nmbr 1 in the column data_version_name, the query returns no rows.

The second problem you will encounter when you'll fix this one is here:

if :P3_SEARCH_NUMBER is not null then

When this statement will be true, you will get a query where the condition and flight_nmbr like ... stands after the ORDER BY clause.

By the way, I would recommend you write a simple query for the report. Generating SQL dynamically leads to such errors and costs you a performance decrease also.