Advanced Find Operator
|
Fetch XML Operator
|
FetchXML Filter
|
SQL Where condition
|
Equal
|
eq
|
<condition attribute="firstname"
value="John" operator="eq"/>
|
WHERE firstname = 'John'
|
Not Equal
|
ne
|
<condition attribute="firstname"
value="John" operator="ne"/>
|
WHERE firstname != 'John'
|
Contains
|
like
|
<condition attribute="firstname"
value="John" operator="like"/>
|
WHERE firstname LIKE '%John%'
|
Does Not Contains
|
not-like
|
<condition attribute="firstname"
value="John" operator="not like"/>
|
WHERE firstname NOT LIKE '%John%'
|
Begins with
|
like
|
<condition attribute="firstname"
value="John%" operator="like"/>
|
WHERE firstname LIKE 'John%'
|
Does Not Begins with
|
not-like
|
<condition attribute="firstname"
value="John%" operator="not like"/>
|
WHERE firstname NOT LIKE 'John%'
|
Ends with
|
like
|
<condition attribute="firstname"
value="%John" operator="like"/>
|
WHERE firstname LIKE '%John'
|
Does Not End with
|
not-like
|
<condition attribute="firstname"
value="%John" operator="not like"/>
|
WHERE firstname NOT LIKE '%John'
|
Contains Data
|
not-null
|
<condition attribute="firstname"
operator="not-null"/>
|
WHERE firstname IS NOT NULL
|
Does Not Contains Data
|
null
|
<condition attribute="firstname"operator="null"/>
|
WHERE firstname IS NULL
|
Is Greater than
|
gt
|
<condition attribute="annualincome"
value="123" operator="gt"/>
|
WHERE AnnualIncome >123
|
Is Greater than or Equal To
|
ge
|
<condition attribute="annualincome"
value="123" operator="ge"/>
|
WHERE AnnualIncome >=123
|
Is Less than
|
lt
|
<condition attribute="annualincome"
value="123" operator="lt"/>
|
WHERE AnnualIncome <123 span="">
|
Is less than or Equal To
|
le
|
<condition attribute="annualincome"
value="123" operator="le"/>
|
WHERE AnnualIncome <=123
|
Equals Current User
|
eq-userid
|
<condition attribute="createdby"
operator="eq-userid"/>
|
WHERE CreatedBy ='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
|
Does Not Equal Current user
|
ne-userid
|
<condition attribute="createdby"
operator="ne-userid"/>
|
WHERE CreatedBy !='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
|
Equals Current User' Teams
|
eq-userteams
|
<condition attribute="ownerid"
operator="eq-userteams"/>
|
WHERE OwnerId IN ('7BC449A8-5A8F-E311-A63C-0050568C6D7D')
|
Equals Current user Or User's Teams
|
eq-useroruserteams
|
<condition attribute="ownerid"
operator="eq-useroruserteams"/>
|
WHERE OwnerId IN('7BC449A8-5A8F-E311-A63C-0050568C6D7D',' 09C5427D-88B7-E411-8BFB-0050568C6D7D')
|
On
|
on
|
<condition attribute="createdon"
value="2015-04-01" operator="on"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
|
On or After
|
on-or-after
|
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-after"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00'
|
On or Before
|
on-or-before
|
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-before"/>
|
WHERE CreatedOn < '2015-04-02 05:00:00'
|
Yesterday
|
yesterday
|
<condition attribute="createdon"operator="yesterday"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
|
Today
|
today
|
<condition attribute="createdon"operator="today"/>
|
WHERE CreatedOn >= '2015-04-02 05:00:00' AND CreatedOn < '2015-04-03 05:00:00'
|
Tomorrow
|
tomorrow
|
<condition attribute="createdon"operator="tomorrow"/>
|
WHERE CreatedOn >= '2015-04-03 05:00:00' AND CreatedOn < '2015-04-04 05:00:00'
|
Next 7 Days
|
next-seven-days
|
<condition attribute="createdon"operator="next-seven-days"/>
|
WHERE CreatedOn >= '2015-04-02 21:25:41.110' AND CreatedOn <'2015-04-10 05:00:00'
|
Last 7 Days
|
last-seven-days
|
<condition attribute="createdon"operator="last-seven-days"/>
|
WHERE CreatedOn >= '2015-03-26 05:00:00' AND CreatedOn <= '2015-04-02 21:25:41.110'
|
Next Week
|
next-week
|
<condition attribute="createdon"operator="next-week"/>
|
WHERE CreatedOn >= '2015-04-05 05:00:00' AND CreatedOn < '2015-04-12 05:00:00'
|
Last Week
|
last-week
|
<condition attribute="createdon"operator="last-week"/>
|
WHERE CreatedOn >= '2015-03-22 05:00:00' AND CreatedOn < '2015-03-29 05:00:00'
|
This Week
|
this-week
|
<condition attribute="createdon"operator="this-week"/>
|
WHERE CreatedOn >= '2015-03-29 05:00:00' AND CreatedOn < '2015-04-05 05:00:00'
|
Next Month
|
next-month
|
<condition attribute="createdon"operator="next-month"/>
|
WHERE CreatedOn >= '2015-05-01 05:00:00' AND CreatedOn < '2015-06-01 05:00:00'
|
Last Month
|
last-month
|
<condition attribute="createdon"operator="last-month"/>
|
WHERE CreatedOn >= '2015-03-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
This Month
|
this-month
|
<condition attribute="createdon"operator="this-month"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-05-01 05:00:00'
|
Next Year
|
next-year
|
<condition attribute="createdon"operator="next-year"/>
|
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
Last Year
|
last-year
|
<condition attribute="createdon"operator="last-year"/>
|
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
This Year
|
this-year
|
<condition attribute="createdon"operator="this-year"/>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
Last X Hours
|
last-x-hours
|
<condition attribute="createdon"
value="1" operator="last-x-hours"/>
|
WHERE CreatedOn >= '2015-04-02 20:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
|
Next X Hours
|
next-x-hours
|
<condition attribute="createdon"
value="1" operator="next-x-hours"/>
|
WHERE CreatedOn >= '2015-04-02 21:53:49.933' AND CreatedOn <'2015-04-02 23:00:00'
|
Last X Days
|
last-x-days
|
<condition attribute="createdon"
value="1" operator="last-x-days"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
|
Next X Days
|
next-x-days
|
<condition attribute="createdon"
value="1" operator="next-x-days"/>
|
WHERE CreatedOn >= '2015-04-03 19:48:07.257' AND CreatedOn <'2015-04-05 05:00:00'
|
Last X Weeks
|
last-x-weeks
|
<condition attribute="createdon"
value="1" operator="last-x-weeks"/>
|
WHERE CreatedOn >= '2015-03-27 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
|
Next X Weeks
|
next-x-weeks
|
<condition attribute="createdon"
value="1" operator="next-x-weeks"/>
|
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2015-04-11 05:00:00'
|
last X Months
|
last-x-months
|
<condition attribute="createdon"
value="1" operator="last-x-months"/>
|
WHERE CreatedOn >= '2015-03-03 06:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
|
Next X months
|
next-x-months
|
<condition attribute="createdon"
value="1" operator="next-x-months"/>
|
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2015-05-04 05:00:00'
|
Last X Years
|
last-x-years
|
<condition attribute="createdon"
value="1" operator="last-x-years"/>
|
WHERE CreatedOn >= '2014-04-03 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
|
Next X Years
|
next-x-years
|
<condition attribute="createdon"
value="1" operator="next-x-years"/>
|
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2016-04-04 05:00:00'
|
Any Time
|
not-null
|
<condition attribute="createdon"
operator="not-null"/>
|
WHERE CreatedOn IS NOT NULL
|
Older than x months
|
olderthan-x-months
|
<condition attribute="createdon"
value="1" operator="olderthan-x-months"/>
|
WHERE CreatedOn < '2015-03-03 06:00:00'
|
In Fiscal Year
|
in-fiscal-year
|
<condition attribute="createdon"
value="2015" operator="in-fiscal-year"/>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
In Fiscal Period
|
in-fiscal-period
|
<condition attribute="createdon"
value="1" operator="in-fiscal-period"/>
|
WHEREdbo.fn_GetFiscalPeriod('2014-01-01 00:00:00', 4, CreatedOn, '20') = 1
|
In Fiscal Period and Year
|
in-fiscal-period-and-year
|
<condition attribute="createdon="
operator="in-fiscal-period-and-year=">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
In or After Fiscal Period
|
in-or-after-fiscal-period-and-year
|
<condition attribute="createdon"
operator="in-or-after-fiscal-period-and-year">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE CreatedOn >= '2015-01-01 06:00:00'
|
In or Before Fiscal Period
|
in-or-before-fiscal-period-and-year
|
<condition attribute="createdon"
operator="in-or-before-fiscal-period-and-year">
<value>01</value>
<value>2015</value>
</condition>
|
WHERE CreatedOn < '2015-04-01 05:00:00'
|
Last Fiscal Year
|
last-fiscal-year
|
<condition attribute="createdon"operator="last-fiscal-year"/>
|
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
This Fiscal Year
|
this-fiscal-year
|
<condition attribute="createdon"operator="this-fiscal-year"/>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
|
Next Fiscal Year
|
next-fiscal-year
|
<condition attribute="createdon"operator="next-fiscal-year"/>
|
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
Last X Fiscal Year
|
last-x-fiscal-years
|
<condition attribute="createdon"
value="1" operator="last-x-fiscal-years"/>
|
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
|
Next X Fiscal Year
|
next-x-fiscal-years
|
<condition attribute="createdon"
value="1" operator="next-x-fiscal-years"/>
|
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
|
Last Fiscal Period
|
last-fiscal-period
|
<condition attribute="createdon"operator="last-fiscal-period"/>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
This Fiscal Period
|
this-fiscal-period
|
<condition attribute="createdon"operator="this-fiscal-period"/>
|
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-07-01 05:00:00'
|
Next Fiscal Period
|
next-fiscal-period
|
<condition attribute="createdon"operator="next-fiscal-period"/>
|
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'
|
Last X Fiscal Period
|
last-x-fiscal-periods
|
<condition attribute="createdon"
value="1" operator="last-x-fiscal-periods"/>
|
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
|
Next X Fiscal Period
|
next-x-fiscal-periods
|
<condition attribute="createdon"
value="1" operator="next-x-fiscal-periods"/>
|
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'
|
Friday, February 19, 2016
Fetch XML Condition to SQL WHERE
Subscribe to:
Post Comments (Atom)
Get files of last hour in Azure Data Factory
Case I have a Data Factory pipeline that should run each hour and collect all new files added to the data lake since the last run. What is...
-
I find that using workflow id might be problematic across deployment, so I wrote a function to retrieve workflow’s guid given the workflow...
-
Microsoft Dynamics CRM supports two types of parameters for a workflow activity. Input Parameters Output Parameters Input Paramete...
-
Calling unbound Dynamics CRM actions using the Dynamics 365 Web API We first need to create an unbound action. With unbound I mean “no...
No comments:
Post a Comment