Hi Russell
The answer depends on which connector type you are using.
In general, Inaport does not actually interpret the SQL at all - it passes the SQL through to the underlying database. The means, for example, if you are querying SQL Server, you can do any SQL that SQL server supports.
With Excel, the SQL does have some unusual requirements: in particular, a worksheet is surrounded by the backward quote character '`' and ends with a '$', so you have:
Code:select field1, field2 from `worksheet$`
One enhancement Inaport does give you is that #fieldname in a SQL select will be replaced with the value of the field. So if you have a field called 'myfield' with a value of 'abc', you can do this:
Code:dbselect("T", "select field1 from table where field2 = '#myfield' ")
The #myfield will be replaced with the value of the field, so the SQL that gets passed to the target is:
Code:select fiedl1 from table where field2 = 'abc'
Microsoft CRMThe MSCRM web service API does not actually support SQL. Under the hood, Inaport translates what you enter as a SQL statement into a web service call.
The implication is that you can only use relatively simple SQL SELECT statements: single table (no joins), no aliases.
The
where clause supports =, <>, like, not like, null, not null, and parentheses.
So some sample SQL statements would be:
Code:select field1, field2 from table where field3 = 'aaa'
select * from table where (field1 = 'aaa') and (field2 not like 'bb%')
HTH. Let me know if you have any other questions.