An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error

Q - When I try to access the NOTE_ORD_VIEW in the PSQL control center, I get this error: "[LNA][Pervasive][ODBC Engine Interface]Expression evaluation error."  See sample screen below.


It does not display any results, but it does display the create statement for the view.

A - "Expression Evaluation Error" typically points to a data issue. After spending some time investigating your data, we found that the cause of the error is due to some garbage records in the NOTE.BTR table. To find these garbage records, you can go to Global Setup -> Utilities -> Print or Purge Orphan Amigos, then choose the following parameters: 

    Type of Amigo to Purge N
    Master File CPORDHDR
    Purge? N 

Then you will get a list of orphan CPORDHDR notes as in the screen below:


The three records that caused the problem are the ones that have the reference number = "00test." We presume these three records are left over from your FedEx person when he/she did the integration test with Elliott.

Why would these three records would be an issue? The SQL statement we used to create this view is as follows:
    Create View NOTE_ORD_VIEW As
    Select NOTES.*, CPORDHDR.* From NOTES, CPORDHDR
    Where NOTE_FILE_NAME = 'CPORDHDR'
    And ORDER_NO = CONVERT(SUBSTRING(NOTE_FILE_REF_NO,1,6),SQL_NUMERIC);

We need to convert the NOTE's reference field to a numeric format in order to join with CPORDHDR's ORDER_NO. Otherwise, you will get an error on data type mismatch. As the PSQL engine tries to convert these records' reference fields from a string value to numeric, it can't do it because "00test" is not numeric, and hence you get the referenced error.

You can resolve this problem by running the "Print or Purge Orphan Amigos" utility above, and answer "Y" to "Purge?"  After that, you will be able to run this view without the error. 

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master

Feedback and Knowledge Base