Some ODBC providers do not support IF/THEN/ELSE in SQL statements. The SQL Interface Builder provides a syntax that is interpreted by the SQL Interface before executing the SQL in the database:
It can be used to get fields from two alternative sources. This is an example from the demo interface distributed with StarShip, Demo 1, under Documents > Header > SQL List > ship_addr.
In this demo database example, the Order table contains the Order Header information, including Customer Address fields. The CustomerAddress table is where additional addresses for a customer are stored.
The {Header.ShipAddressID} field in the example comes from the Key Provider SQL (Header.order_header) that is pulling the header row information from the Orders table. If the {Header.ShipAddressID} field (which links to Order.ShipAddressID field) contains any value other than 0, StarShip will run the SQL that retrieves the address information from the CustomerAddress table.
If the Order.ShipAddressID fields contains 0, this customer does not have additional addresses and the SQL that retrieves address info from the Order header table is used.
IF/THEN/ELSE is also used for Write-Back SQL statements. StarShip sends the Settings.UpdateFreightFlag (Internal Name WBSourceDocument.Settings.UpdateFreightFlag) in the write-back XML based on settings in the StarShip Client > Customize Interface. The flag can be set by default or based on freight rules, from a user prompt or write-back settings from Customize Interface in the StarShip client. You should then test this flag and write SQL statements that update freight in your financial system conditionally based on this flag.
This is a sample SQL statement under Documents > Write Back > Document, also from the demo interface, Demo 1.
Microsoft Access Example :
{IF} {WBSourceDocument.Settings.UpdateFreightFlag} == 'Y' {THEN} UPDATE tblMyTable
SET FlatRateShipCost = {WBSourceDocument.Shipment.Freight}
WHERE OrderId=VAL({WBSourceDocument.Document Key});
{ENDIF}
Note :