SQL Variable Validation using SQL SELECT statement

I am in the process of configuring some software called AirLinc by CONNECT Inc. I am in the scripting stage and I am trying to validate my order number variable against my Navision Database using the SELECT statement. Here is what it looks like: Select| DSN:“Navision Database”, SQL:"SELECT DOCUMENT FROM PW_Trx_Header WHERE DOCUMENT = ?DOCUMENT Goto| Goto Label Item Label|INVALIDORDER Warning|Message:“Invalid Order ID” Press Any Key Goto| Goto Label ORDERID The problem that I am having is, no matter what I enter for ?DOCUMENT, I get the “Invalid Order ID” message. This happens even if I enter a order number that exists in the table referenced. Can anyone help me? Any information would be greatly appreciated.


Originally posted by support1 - 2005 Feb 23 : 16:23:13

Are you sure that this is not confusing the system ? I mean using the same value for a variable which is also a column in the target table ? wouldn’t it be better to use WHERE DOCUMENT = ?DOCID instead ? I mean I do not know the product you are using, but things like this are causing a lot of troubles as far as I know. Do you get any results if you omit the WHERE condition ?

If I remove the WHERE condition from the statement, then the order number that is entered will not be validated against the order numbers that exist. I also changed the variable name and am getting the same results. What I have found is that the results are not being written to the specifed results table. Can anyone explain why that might be??

Well all I can think of is that the primary key of the Navision sales header table is “Document Type,No.” so it might get confused if you use the number only. Try specifying a document type as well as a document number. It works the same way for the Purchase header table.