Welcome! The Community Feedback space is intended for discussions about the Oracle Community website, not for product-specific questions.
There are tips on how to find the correct space on the Getting Started page, which also has instructions on how to change your display name so you're not just a number.
In the ACTIONS box on the top right of your post, you should be able to click on "Move", and then specify the correct space - such as Oracle Application Express (APEX) - so the right experts can see your question.
Thanks and good luck!
You need to make sure that your item values are being commited to your session for that to work. The easiest way to accomplish this would be to create an onChange dynamic action that submits your page.
Hope this helps
Well it depends how you're triggering your report to refresh, but at the very least make sure all page items used in your report query are listed in the 'Page Items To Submit' attribute for the report.
Also, dates in page items are stored as text representations not as date types. Therefore if you compare them to data in a table which IS held in a date type, you need to use something like to_date() to convert them as part of the comparison. Otherwise your results might be... unexpected.
I have a submit button that has been set to submit the parameters so that it refreshes the report.
Yes the dates are stored in as date data type in the table ..... how do I use the TO_DATE function on the query above. .... I have tried some examples I saw online but none seem to be working.
As others correctly commented the problem is probably that you compared string values and not date values.
There are several very common mistakes you should try to avoid.
1) For example if you use month names like "JAN" then you also need to make sure the nls_date_language is set correctly. Not every language abbreviates the first month as "JAN". I prefer to avoid those language dependencies. For example by using "01". But since the value for the page items also needs to change, this format mask also needs to be added to the page item in apex.
2) OR has a lower priority than AND. So your type_of_account filter ONLY works with the ready_date check, but not for the start_date/end_date check. Or to say it differently, if start_date is null AND end_date is null then the type_of_acount can be anything. Is this what you want?
3) A LIKE comparison usually can not use an index. An equalilty comparison can use an index. And additionally the optimizer can give a much better guess about the number of rowsd that this filter will use. Which will lead to better and more stable execution plans.
You should check if the filter Type_of_account LIKE '%XXX%' is need, or if you change that into an equality check.
Here is how to modify the query (the blank before the items as P3_TOA needs to be removed. I only typed them, because otherwise the forum replaces that with an emoji .
select deal_id, Client_name,Client_Account,Type_of_account ,ready_date
WHERE Type_of_account = : P3_TOA
AND (ready_date BETWEEN NVL(to_date(:P3_Start_Date,'DD-MM-RRRR'),to_date('01-01-1900'))
OR (:P3_Start_Date is null and :P3_End_Date is null)
I have tried your solution but it's still not sorting it out. .... I have found a way to do it in interactive report where I use date between function to help me select two different dates. how can I replicate that in my application.
If you are using an interactive report it won't let you sort your items in your report... when you add a "order by " clause it will fire out an error.
Unless...... you do something like this, and make sure that the default IR report is not sorted in any ways.
select field1, field2, etc...
from (select field1, field2, etc
where field_date between date1 and date2
order by field_Date desc)
In this way, in an interactive report you can get your default data sorted out by the field you want, but this sort gets automatically overwritten as soon as the user selects another way to sort the data out, or if they save a private report with a different way of sorting the data.
I understood the phrase "data not sorting out" to be referring to data not filtering. I believe it was not ment as an ordering issue.
@3829614 what you could do is to set a high debug level when running your working way using the interactive report.Then you will see in the debug, the exact query that apex executes against the database. This should include the between filter on the date column. You can consider to copy that logic to your own select.
Best way would be to setup a small test case on apex.oracle.com. The problem looks like it can be solved very easily, but there are a lot of tiny dependencies that are hard to guess. For example at the moment, you never showed that your data actually has values that do fit the multiple conditions.