CODEWITHSUNDEEP
sharepoint
Dinesh Kumar
Dinesh Kumar

Reputation: 13

Translating a SQL query to CAML query

I am new to CAML query and as much as I have expected it to be simple like SQL I have found it quite frustrating to deal with. I have a simple query SQL query I am trying to translate into CAML but do not receive the expected result.

Sql: select * from exchangeRates where (date='2016-12-01' and currency<>'DKK')

Or

(date='2016-11-25' and currency_Type = 'Manual')

Any help is appreciated. Thanks.

Upvotes: 0

Views: 718

Answers (1)

Thriggle
Thriggle

Reputation: 7059

Equals and Not Equals

Use the <Eq> element to find items where a field equals a specified value and the <Neq> element to find items where a field is not equal to a specified value.

<Eq>
    <FieldRef Name="Internal_Name_Of_Date_Field" />
    <Value Type="DateTime">2016-12-01</Value>
</Eq>

Joining Conditions to build Complex Conditionals

CAML uses <And> and <Or> elements to join conditions together within a <Where> clause.

<And>
    <Eq>
        ...
    </Eq>
    <Neq>
        ...
    </Neq>
</And>

Each <And> or <Or> element expects exactly two child nodes, which are the conditions being joined together.

A child node can be another <And> or <Or> element, allowing you to join nested conditions together for arbitrarily complex conditions.

<Or>
    <And>
       ...
    </And>
    <And>
        ...
    </And>
<Or>

Using Excel Formulas as Conceptual Guidelines

Sometimes it helps to think in terms of an Excel formula. The Excel functions OR() and AND() can take two parameters (conditions) to return a boolean value. CAML syntax and Excel syntax are thus pretty similar: CAML just uses XML elements instead of parentheses.

English: (A and B) or (C and D)

Excel: OR( AND(A, B), AND(C, D) )

CAML: <Or> <And> A B </And> <And> C D </And> </Or>

Example CAML

Without knowing what the internal names of your columns are, the <Where> element of your CAML query might look something like this:

<Where>
    <Or>
        <And>
            <Eq>
                <FieldRef Name="Internal_Name_Of_Date_Field" />
                <Value Type="DateTime">2016-12-01</Value>
            </Eq>
            <Neq>
                <FieldRef Name="Internal_Name_Of_Currency_Field" />
                <Value Type="Text">DKK</Value>
            </Neq>
        </And>
        <And>
            <Eq>
                <FieldRef Name="Internal_Name_Of_Date_Field" />
                <Value Type="DateTime">2016-11-25</Value>
            </Eq>
            <Eq>
                <FieldRef Name="Internal_Name_Of_Currency_Type_Field" />
                <Value Type="Text">Manual</Value>
            </Eq>
        </And>
    <Or>
</Where>

Upvotes: 1

Related Questions