Skip to main content

Hi,

In Quick Report, I have to get data FromDate to ToDate using a DateTime column

I used this:

WHERE o.REAL_FINISHED BETWEEN TO_DATE('&From_dd/mm/yyyy','dd/mm/yyyy') AND TO_DATE('&To_dd/mm/yyyy','dd/mm/yyyy')

 

I’m getting when we go to view report 

 

It works fine 

When I export data I’m getting this error:  Excel Plugin Error: Undefined bind variable 'from_dd' in Excel file

Any idea how to sort this issue?

I think its the “/” characters in your parameter name causing the issue. Try removing that.

Hi @Rusiru 

It works now 

Is it possible to add comments by default when we press View Report?

I want to provide date format information.

Thanks,

Narsi.

Hi @Rusiru 

It works now 

Is it possible to add comments by default when we press View Report?

I want to provide date format information.

Thanks,

Narsi.

You add comments in the report definition, like below.

 

Yes, there is a dedicated box for comments when you created the SQL quick report. Hope this is what you are expecting.

 

 

Thank you so much @Rusiru and @anmise 

I would use the following, this allows the user to select it via a date selector.

trunc(REAL_FINISHED) between NVL(&ABSENCE_PERIOD.DATE_FROM,TRUNC(sysdate)) and  nvl(NVL(&ABSENCE_PERIOD.DATE_TO,&ABSENCE_PERIOD.DATE_FROM),trunc(sysdate))

Thanks @Jur 

Hi @Jur @Rusiru @anmise 

Is it possible to default/pre-populate values on the date parameter fields when we press “View Report”?

I mean date paramer fields would be populated automatically when we press “View Report”

“Date From” would be the first day of last week and “Date To” would be today’s date.

For example:

Today’s Date: 17-08-2020

From Date: 10-08-2020

To Date: 17-08-2020

 

Thanks,

Narsi.

I would use the following, this allows the user to select it via a date selector.

trunc(REAL_FINISHED) between NVL(&ABSENCE_PERIOD.DATE_FROM,TRUNC(sysdate)) and  nvl(NVL(&ABSENCE_PERIOD.DATE_TO,&ABSENCE_PERIOD.DATE_FROM),trunc(sysdate))

If you're going to nest NVL functions, it might be better to use COALESCE. (As a bonus, COALESCE is ANSI SQL and not Oracle-specific, so developers coming from other relational database systems may understand it.)

TRUNC(real_finished) BETWEEN COALESCE(&absence_period.date_from,
                                      TRUNC(SYSDATE))
                         AND COALESCE(&absence_period.date_to,
                                      &absence_period.date_from,
                                      TRUNC(SYSDATE))

 

By applying a function to the view or table column before it’s compared to the user value, it forces the system to run a full scan and compute that function on every record. The database isn’t smart enough to work backwards, predict what the outcome of that function should be, and find records that satisfy the output condition. This means this query won’t take advantage of indexes if you have any (notwithstanding function indexes which aren’t often used).

If you happen to have an index on REAL_FINISHED, this next iteration of the predicate will take advantage of it. (Pick the variant you find more readable.)

real_finished BETWEEN COALESCE(&absence_period.date_from,
                               TRUNC(SYSDATE))
                  AND COALESCE(&absence_period.date_to,
                               &absence_period.date_from,
                               TRUNC(SYSDATE)) + (1 - (1 / (60 * 60 * 24)))

real_finished BETWEEN COALESCE(&absence_period.date_from,
                               TRUNC(SYSDATE))
                  AND COALESCE(&absence_period.date_to,
                               &absence_period.date_from,
                               TRUNC(SYSDATE)) + (86399 / 86400)

If you know your REAL_FINISHED is always truncated back to midnight, then that would make your query even simpler. I’m not familiar with this view, but you could look in your data to discover that.

SELECT *
  FROM some_view
 WHERE real_finished != TRUNC(real_finished);

Reply


Terms & ConditionsCookie settings