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