Receive 'Invalid Column Name' Message After Starting to Use Alphabetic Document Numbers

Receive 'Invalid Column Name' Message After Starting to Use Alphabetic Document Numbers

Release Date: 4/5/2019
Version: 8.5 & Up

Q - I just started to use alphabetic order numbers in Elliott 8.5, and my FedEx shipping manifest machine is giving me a message :

The Following Errors Occured While Tring to Process a SQL Request
SHIPPING ERROR NUMBER: 1526

SHIPPING ERROR MESSAGE:
Connectivity error: [Pervasive][ODBC Client Interface][LNA][PSQ:][SQL Engine]Invalid column name: 'AA0238'.

ODBC ERROR MESSAGE:
[Pervasive][ODBC Client Interface][LNA][PSQ:][SQL Engine]Invalid column name: 'AA0238'.  

ODBC SQL STATE: S0022

ODBC CONNECTION HANDLE: 1


A - This is because their applications treated Elliott’s order number as numeric in the past and used a SQL statement like the following to retrieve Elliott’s order data:
      SELECT * FROM CPORDHDR WHERE ORDER_NO = AA0238
If the order number is still numeric, then this would work. But since the ORDER_NO is alphabetic now, you will get an error message from PSQL like:
<<<<<<<<<<<<<<<<<<<<<<<<
SELECT * FROM CPORDHDR WHERE ORDER_NO = AA0238
[LNA][PSQL][SQL Engine]Error in expression: AA0238
[LNA][PSQL][SQL Engine]Invalid column name: 'AA0238'.
>>>>>>>>>>>>>>>>>>>>>>>>
To fix this error, you need to ask your FedEx technician to put the order number in single quotes like:
      SELECT * FROM CPORDHDR WHERE ORDER_NO = ‘AA0238’
This type of integration with Elliott is at the third party’s discretion, and we can’t really predict if they will continue to work when you start using alphabetic order numbers in 8.5. The only way to find out ahead of time is to test in a sample environment before you convert.

EMK


    • Related Articles

    • DN API (Document Number Handling)

      Release: 1/11/2021 Version: V8.5 and higher DN API: Document Number Increase, Roll Back or Validate In Elliott V8.5, the system supports alphabetic document numbers. The logic to sequentially assigning the next document number is complicated. For ...

    • Alpha Document Number Support (V8.5/V9.0)

      V8.5/V9.0 offers support for alpha numeric document numbers. This gives the user more flexibility on the number of alphabetic characters in next document number. For example, in the scenario of the following “Starting Invoice Number” in A/R Setup: ...

    • Change - Shipping Verification Box Edit Serial Numbers

      Release Date - 1/2/18 In Shipping Verification, when the user presses the F5 key to edit box information, items can be added and deleted to/from different boxes. For serialized items this means the deletion and addition of serial numbers in addition ...

    • Can You Explain How Elliott Stores Serial Numbers in Its Database?

      Q - Can you explain the various Lot/Serial files (IMLSHST, IMLSTRXS, IMSERREF, etc) - how/when they are populated/updated by the Elliott application and how they relate to other files? A - Here is a highlight of each one of the Elliott database ...

    • Receive 'Invalid Column Name FILLER_001' After Changing to New Database Connection

      Release Date: 04/05/2019 Version: 8.0 & Up Q - We recently changed our database connection from ELLIOTTDATA to ELI85DATA. After the change, our FedEx shipping manifest system is complaining of an 'Invalid Column Name FILLER_001' like below: ...