Using Quick Report Parameters - Dates Between

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.

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

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!