classic report searching in oracle apex using page item values

2
votes

I am displaying a table from my database as a classic report in my oracle apex, Now I would like to add search criteria for this classic report and created a select list, two text fields and a go button till this all went correctly. Here, I would like to display the classic report in the page though these values are not set. How can I make adjustments in the classic reports query so that if these values are not set I should show all the rows from the DB table else show according to the search criteria.

The source of my report is as follows:

select b.*
from "#OWNER#"."Books"  b
where i_type = 'CU'  
   and state = :P46_STATE 
   and city = :P46_CITY 
   and name = :P46_NAME

:P46_state is a select list, :P46_city, :P46_name are the text fields from the search criteria if these values are not set have to show full report/ and if set have to show as per the search.

Thanks in advance.

2
sqlplsqloracle-apex

2 Answers

-1
votes

you don't need the concatenation - that's screwing you up

SO

and (city LIKE '%'||:P46_CITY||'%' OR :P46_CITY IS NULL)

becomes

and (city LIKE '%:P46_CITY%' OR :P46_CITY IS NULL)

1
votes
select b.*
from "#OWNER#"."Books"  b
where i_type = 'CU'  
   and (state = :P46_STATE OR :P46_STATE IS NULL)
   and (city = :P46_CITY OR :P46_CITY IS NULL)
   and (name = :P46_NAME OR :P46_NAME IS NULL)

If you want the searches to do keyword searching on CITY, you can use LIKE:

select b.*
from "#OWNER#"."Books"  b
where i_type = 'CU'  
   and (state = :P46_STATE OR :P46_STATE IS NULL)
   and (city LIKE '%'||:P46_CITY||'%' OR :P46_CITY IS NULL)
   and (name = :P46_NAME OR :P46_NAME IS NULL)

Or, you can use INSTR:

select b.*
from "#OWNER#"."Books"  b
where i_type = 'CU'  
   and (state = :P46_STATE OR :P46_STATE IS NULL)
   and (INSTR(city, :P46_CITY) > 0 OR :P46_CITY IS NULL)
   and (name = :P46_NAME OR :P46_NAME IS NULL)