Lotus: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Lotus Origin=
=Lotus Origin=
==Sample Origin Filters==
==Sample Origin Filters==
===Important Note===
Starfish ETL generates the query sent to Lotus as: (Form = '*Table Selected*' AND *Criteria*).  If you select Accounts as the table and your Criteria is: CompanyName='Abbott' | CompanyName='Hogan' the final query would look like: (Form = 'Accounts' AND CompanyName='Abbott' | CompanyName='Hogan').  This will not work correctly because you have a & b | c.  You need a & (b | c).  So in some cases, you must put parenthesis around your Criteria so the final result would be: (Form = 'Accounts' AND (CompanyName='Abbott' | CompanyName='Hogan')).
===Basic Queries===
Field Equals Value:
Field Equals Value:
<syntaxhighlight lang="c">
<syntaxhighlight lang="c">
CompanyName='Abbott, Ltd.'
CompanyName='Abbott, Ltd.'
</syntaxhighlight>
</syntaxhighlight>
Lotus Origin with an AND:
Field Does NOT Equal Value:
<syntaxhighlight lang="c">
CompanyName!='Abbott, Ltd.'
</syntaxhighlight>
Lotus Origin using AND:
<syntaxhighlight lang="c">
<syntaxhighlight lang="c">
CompanyName='Abbott, Ltd.' & IENCLIENTSTATUS='Vendor'
CompanyName='Abbott, Ltd.' & IENCLIENTSTATUS='Vendor'
</syntaxhighlight>
</syntaxhighlight>
Lotus Origin with an OR:
Lotus Origin using OR:
<syntaxhighlight lang="c">
<syntaxhighlight lang="c">
CompanyName='Abbott, Ltd.' | CompanyName='Hogan Industries'
CompanyName='Abbott, Ltd.' | CompanyName='Hogan Industries'
</syntaxhighlight>
Lotus Origin using parenthesis ():
<syntaxhighlight lang="c">
CompanyName='Abbott, Ltd.' | (CompanyName='Hogan Industries' & OfficeStreet = '150 Webster Street')
</syntaxhighlight>
===Full-text Queries===
Full text Queries allow for much more complex queries such as Contains.  See: http://publib.boulder.ibm.com/infocenter/lqkrhelp/v8r0/index.jsp?topic=/com.ibm.lotus.quickr.admin.dom.doc/dev/qp_dev_api_fulltext_r.html.
Starfish will automatically detect if you use a question mark, ?, or an asterisk, *, in your Origin query.  If you use one of those characters, Full-text querying will be enabled.  Note the square brackets, [], around field names and the use of double quotes around values.  Parenthesis and &/| use is still the same.
In this query, we are looking for the values anywhere in the field.
<syntaxhighlight lang="c">
([CompanyName]="*Abbot*" | [CompanyName]="*Hogan*") & [CATEGORIES]="*Customer*"
</syntaxhighlight>
</syntaxhighlight>

Latest revision as of 19:59, 24 October 2014

Lotus Origin

Sample Origin Filters

Important Note

Starfish ETL generates the query sent to Lotus as: (Form = '*Table Selected*' AND *Criteria*). If you select Accounts as the table and your Criteria is: CompanyName='Abbott' | CompanyName='Hogan' the final query would look like: (Form = 'Accounts' AND CompanyName='Abbott' | CompanyName='Hogan'). This will not work correctly because you have a & b | c. You need a & (b | c). So in some cases, you must put parenthesis around your Criteria so the final result would be: (Form = 'Accounts' AND (CompanyName='Abbott' | CompanyName='Hogan')).

Basic Queries

Field Equals Value:

CompanyName='Abbott, Ltd.'

Field Does NOT Equal Value:

CompanyName!='Abbott, Ltd.'

Lotus Origin using AND:

CompanyName='Abbott, Ltd.' & IENCLIENTSTATUS='Vendor'

Lotus Origin using OR:

CompanyName='Abbott, Ltd.' | CompanyName='Hogan Industries'

Lotus Origin using parenthesis ():

CompanyName='Abbott, Ltd.' | (CompanyName='Hogan Industries' & OfficeStreet = '150 Webster Street')

Full-text Queries

Full text Queries allow for much more complex queries such as Contains. See: http://publib.boulder.ibm.com/infocenter/lqkrhelp/v8r0/index.jsp?topic=/com.ibm.lotus.quickr.admin.dom.doc/dev/qp_dev_api_fulltext_r.html.

Starfish will automatically detect if you use a question mark, ?, or an asterisk, *, in your Origin query. If you use one of those characters, Full-text querying will be enabled. Note the square brackets, [], around field names and the use of double quotes around values. Parenthesis and &/| use is still the same.

In this query, we are looking for the values anywhere in the field.

([CompanyName]="*Abbot*" | [CompanyName]="*Hogan*") & [CATEGORIES]="*Customer*"