Query Prompt Database Connection With Sql Expression 关注
G'day, I'm trying to generate a delivery label from connection to the accounting package (SAP B1) database via a cusom MS-SQL expression. I've had to generate the custom SQL statement to select the descrete delivery address for the customer, as many customers have multiple delivery locations.
What I'm having issues with is connecting the query prompt to the sales document so, it selects the correct details for the order to be despatched.
The query is:
SELECT T0.[DocNum], T1.[CardCode], T1.[CardName], T0.[NumAtCard], T2.[Address], T2.[StreetNo], T2.[Street], T2.[Block], T2.[City], T2.[State], T2.[ZipCode], T2.[County]
FROM ORDR T0
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
INNER JOIN CRD1 T2 ON T1.[CardCode] = T2.[CardCode]
WHERE T0.[ShipToCode] = T2.[Address]
So I need the query prompt to select the descrete T0.[DocNum] sales order record and I can't get it to work. I know I'm close, so please help with that last line of code. I know I need to link it in the WHERE clause and I've tried variations of the following theme:
WHERE T0.[ShipToCode] = T2.[Address] AND T0.[DocNum]='?QueryPrompt1'
but all I get is: IDdispatch error # 3079
So close, yet so far. What am I doing wrong?