Query Expression or Fetch Xml - Equivalent of NOT EXISTS SQL

(0) Share

Report

Posted on by Community Member

Hi, I am trying to translate the following SQL query into a query expression or a fetch XML. I know I can get the Raw data from the Opportunity Entity and use LINQ queries to filter out what I need but I would like to know if there is a way to get it done using a query expression or a fetch XML.

SELECT o.OpportunityId
FROM dbo.Opportunity o
WHERE NOT EXISTS
(
    SELECT cbo.New_OpportunityId
    FROM dbo.New_commission cbo
        INNER JOIN dbo.New_commissionreport cr
            ON cr.New_commissionreportId = cbo.New_CommissionReportId
    WHERE cbo.New_OpportunityId = o.opportunityid
          AND cbo.New_Type = 1 -- type new
          AND (cr.New_CommissionPeriodFrom
          BETWEEN '20181001' AND '20181031'
              )
);

Please advise.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

pavanmanideep 1,331 on at

Like (1)

Report

Hi Sridhar,

Please use the below tool to convert your SQL queries to Fetch XML

SQL TO FetchXML

However due to limitations of CRM Fetch XML, all the SQL query... Try to start with smaller query and start adding additional conditions while using this tool..

Was this reply helpful? Yes No
Suggested answer

Adrian Begovich 1,027 Moderator on at

Like (0)

Report

Hi SridharV86,

You cannot use the NOT EXISTS operator in FetchXML or QueryExpression.

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (1)

Report

I would suggest to try to build yoru query using advance find and then you can download the fetchxml.

us.hitachi-solutions.com/.../great-fetchxml-tool-for-microsoft-dynamics-crm

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for taking time to help. I could not build what I needed in Advanced Find or Fetch XML and ending up getting the raw data from Opportunity Entity and then applied filter.

Thanks,

Sridhar

Was this reply helpful? Yes No