Cloud NVL function for dates giving error while executing quick report
While using nvl for dates in cloud quick report and if no values given for dates IFS Apps used to fetch the default values provided Whereas in cloud this is not working. It throws the following error.
User who tested this has sent the following information
“ I wanted to test the quick report today and I receive a Server Error when using the same parameters as in your screenshot. I have also tested it yesterday, then I didn’t receive the Server Error in the morning. In the afternoon I was getting it also.
Strange thing is that when I only use the Start_Date or End_Date as parameter the Server Error does not show.”
Page 1 / 1
Hi,
Not sure which track of IFS Cloud you are referring to but I checked in 21R2 and it works. In the example below, I left blank at the prompt ‘Mydate’ and selected ‘Execute’ and it fetched rows matching for the date 13/03/2002 without any errors.
select * from PURCHASE_ORDER t where t.WANTED_RECEIPT_DATE = NVL('&MyDate',TO_DATE('13/03/2002', 'DD/MM/YYYY'))
If you are using Context Substitution Variable like #TODAY# etc. as the default values, then it will not work. Context Substitution Variable are not supported in Date/ DateTime/ Number fields in Aurean Client due to a limitation in the Client Framework. Using Context Substitution Variable might generate a server error.
Regards,
Chanaka
Hi Chanaka
Thanks for the reply. I am not using context substitute variables at all. I am just using simple nvl such as below
I even tried oracle COALESCE function , but that also did not help.
WHERE company = nvl('&COMPANY', Company) and HR_BP = nvl('&HR_BP', HR_BP) AND trunc(start_date) > = nvl(to_date('&START_DATE', 'DD/MM/YYYY'), trunc(start_date)) AND trunc(end_date) < = nvl(to_date('&END_DATE', 'DD/MM/YYYY'), trunc(end_date)) ORDER BY EMPLOYEE_ID
Below is the cloud version I am working
Hi,
I just tried this and it works. As before I left blank in “MyDate” parameter and clicked ‘Execute” and it returned data.
select * from ARCHIVE t where t.report_id = nvl('&ReportID', t.report_id) and trunc(t.exec_time) >= nvl(to_date('&Mydate', 'DD/MM/YYYY'), trunc(t.exec_time)) order by t.result_key
But I’m not sure if this is a version issue. Maybe there is something wrong in the query.
Regards,
Chanaka
Hi Chanaka
My query matches yours. I observed some inconsistency in displaying data with the NVL date functionalities. sometimes it displays data by taking the defaults and some times does not.
Btw the version you are running is slightly different from the customer version as can be seen above.
Hi,
Yes, I’m testing this in a central environment which is 21R2 SU11. I executed the report several times but didn’t noticed anything. Maybe you should try to default to a hardcoded value rather than the column value.
