Question

Cloud NVL function for dates giving error while executing quick report

  • 18 August 2022
  • 5 replies
  • 195 views
R
Rank
Userlevel 2
Badge +7

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.”

 

 

5 replies

ChanakaAmarasekara
Rank
Userlevel 7
Badge +15

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

 

 

- Chanaka Am -
R
Rank
Userlevel 2
Badge +7

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

 

ChanakaAmarasekara
Rank
Userlevel 7
Badge +15

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

 

My Cloud version details

IFS Aurena framework version: 21.2.11.20220804103251.0

IFS Aurena client version: 21.2.11.20220804103251.0

IFS OData provider version: 21.2.11.20220803141159.0

 

But I’m not sure if this is a version issue. Maybe there is something wrong in the query.

 

Regards,

Chanaka

- Chanaka Am -
R
Rank
Userlevel 2
Badge +7

 

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. 

 

ChanakaAmarasekara
Rank
Userlevel 7
Badge +15

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.

 

Regards,

- Chanaka Am -

Reply


Terms & ConditionsCookie settings