Using Quick Report Parameters - Dates Between

It doesn’t answer your question, but have you considered just using a BETWEEN keyword, as in:

<datefield> BETWEEN &StartDate AND &EndDate

where StartDate and EndDate are expressions mapping to from and to fields? Or convert text to dates with TO_DATE() and just accept text entry?

Thanks,

Joe Kaufman

Right yes there are workarounds but I was really hoping to give our users the same behavior as most IFS screens.

Using &CUSTOMER_INFO.CREATION_DATE would do language translations for the pop up window as well as allow the user to enter dates in Date..Date format. 

Created: 10/12/2020..11/12/2020

Per IFS F1 Help documents shows that using the [---B-] allows between functions.

Sorry I guess creation date is a bad example for translations…

Did you try it with just a user-defined parameter name? Not sure if that even works with the flags… I can’t seem to find the help with a quick look to know if that is even a possibility. My point is, maybe field-name params and flags don’t work at the same time? At least not the B flag?

Thanks,

Joe Kaufman

I don’t think you can use LIKE the way you are doing it on a date. See this:

https://stackoverflow.com/questions/33151961/oracle-use-like-on-date

I have not tried “between” on a date field in an IEE search. If you do it and convert the query to “Advanced” and view the SQL, what does it look like?

Thanks,

Joe Kaufman

Hi @CodyWhite,

This is the correct way to specify the SQL statement.

SELECT customer_id,Name 
from customer_info
where creation_date like '&[---B-]CUSTOMER_INFO.CREATION_DATE'

I just tried your SQL statement in one of our reference environments and got the same error. When looking at where the exception is thrown using the debug console, I could see it was the Quick_Report_API.Get_Datatypes__ statement that fails.

-- Context: tbwQuickReport

DECLARE
   -- p0 -> __g_Bind.s[0]
   p0_ VARCHAR2(32000) := 'SELECT customer_id,Name from customer_info where creation_date like ''TO_DATE(''2021-07-15'',''YYYY-MM-DD-HH24.MI.SS'')''';

BEGIN
    IFSAPP.Log_SYS.Init_Debug_Session_('en');

p0_ := IFSAPP.Quick_Report_API.Get_Datatypes__( p0_ );

   ----------------------------------
   ---Dbms_Output Section---
   ----------------------------------
   Dbms_Output.Put_Line('p0_ -> __g_Bind.s[0]');
   Dbms_Output.Put_Line(p0_);
   ----------------------------------

END;

If you look at the SELECT statement in the DECLARE section, it is declared as a string(VARCHAR), which is then passed to the Get_Datatypes method.

In SQL, when there are single quotes inside a string, you need to escape them by using “double single quotes”. And in this case, there are some quotes missing inside the TO_DATE function. Correct SELECT statement for the above block should be like below.

p0_ VARCHAR2(32000) := 'SELECT customer_id,Name 
from customer_info
where creation_date 

like ''TO_DATE(''''2021-07-15'''',''''YYYY-MM-DD-HH24.MI.SS'''')''';

Note that I have added extra sets of single quotes inside the TO_DATE function. Once it is changed like this, I no longer get the error and the statement is executed successfully.

This appears to be a bug in the application in the way the SQL is generated. Please open a case and report to IFS.

Hope this helps!

Wow, great work! I had no idea how much cool stuff the Quick Report API is doing under the hood! I knew it helped with parameters and data types, but that’s just the tip of the iceberg!

Thanks,

Joe Kaufman