How to Investigate or Debug Report Desk Problem by Using SQL Statement
Released Date: 1/25/2025
Version: 8.6 & Higher
Report Desk is based on the PSQL relational engine. It sends a SQL statement to the PSQL engine to retrieve data. Sometimes, you don't get the result back as you expected, so what next?
For example, when you run the following report desk report, I/M -> Processing -> Inventory Transfer Processing -> List -> UDR Transfer Trx History Summary, you use the following parameters:
You expect there should be data in the date range above. But you get the following message "No qualifying records; report canceled" when you run it:
To debug this problem further, one easy way is to capture the SQL statement in the Elliott Report Desk to use and execute the same SQL statement in the PSQL Control Center in order to get more details. To do that, click on the "SQL" button in the bottom right corner:
Then you will see a window that shows the SQL statement that Elliott sends to the PSQL engine. You can choose "Test SQL" to get more details:
In the SQL Error in the Elliott window, you see there's an "Error in row" message and there is not enough information from other tabs to narrow down this error further:
When you click "OK," you get the following message showing 250 rows returned.
So the error is not really "No qualifying record." It is just "Error in row." To investigate this further, click OK back to the "SQL SELECT Statement" window and choose "Copy to Clipboard." Now bring up the PSQL Control Center and go to the Database that your Report Desk is running against. If you do not know that, you can navigate and run the following <ElliottRoot>\Bin86\EL860CF.EXE, then go to the database tab to find out.
In the PSQL (Zen) Control Center, expand the database on the left side navigation, then expand "Table" and double click on any table to bring up the screen shown below:
Replace the SQL statement with the one you just "Copied to Clipboard." See sample screen below:
On the menu, choose "SQL" -> "Execute in Grid," or you can click on the corresponding shortcut toolbar button. In this case, it does return data back in the grid. But as you scroll down the grid further, you get the message "[LNA][Zen][SQL Engine]Expression evaluation error."
At this moment, it still does not give us enough information to analyze this further.
Click OK and cancel the grid execution. Next, instead of sending output to the grid, you should choose "SQL" -> "Execute in Text," or you can click on the corresponding shortcut toolbar button. If you scroll the text window all the way to the end, you will see the message:
com.pervasive.psql.utilities.sqleditor.dm.model.SQLRuntimeException: [LNA][Zen][SQL Engine]Expression evaluation error.
Your screen will look like the following:
At the moment, the likely reason for this issue is because there's a record that contains invalid data for our SQL statement to execute. At this moment, it is beyond your ability to debug this problem further. Document this and send your support incident to Netcellent for further analysis.
Please also see the following related article:
EMK
Related Articles
How to Investigate or Debug Report Desk Problem by Using Registry Keys
Release Date: 01/25/2025 Version: 8.6 & Up Report Desk is based on the PSQL relational engine. It sends a SQL statement to PSQL engine to retrieve data. Sometimes, you don't get the result back that you expected, so what next? For example, when you ...Developing a New Elliott V8.6 Report Desk User Defined Report (UDR)
'Release Date: 6/20/2018 Version: 8.6 & Up This document is written for licensed Elliott Software developers. It is not relevant for regular Elliott users. Overview With proper licensing, Elliott developers can follow these three steps to create a ...Debug Libraries for Animating
Release Date: 12/7/2023 Versions: Elliott V8.5 and Up Rationale On occasion, the Netcellent developer will want to test a new version of a COBOL program without disturbing other users or developers sharing the same environment. With this update, the ...Data Structures for Report Desk Defaults and Enforcements
Release Date: 9/19/2022 Version 8.6 and Higher In Report Desk, users can save their input values, as well as output option in templates and re-use them in the future. On the first Report Desk screen, the system prompts you to input the selection of ...An Example of DDF Files in DATA Folder Causing Report Desk or Power Search Issue
Release Date: 03/14/2025 Version: 8.6 & Up Q - When I tried to print a UDR report (i.e., AP Distribution History Report) from company # 98, I got the following error message: Cannot find definition of table APDSTHST in DDFs. You probably are using ...