Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 4 - FetchXml Query RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    Hey ,

    I am using CRM 4.

    I need to build the following sql query as FetchXML.

     

    Code Snippet

     

    SELECT * From dbo.FilteredContact Where

    NOT Exists(select * from dbo.FilteredNew_Codes

    where FilteredNew_Codes.new_contactnameid = FilteredContact.contactid

    AND FilteredNew_Codes.new_codeidname = 'NAB')

     

     

    I need to build a savedquery from that FetchXML.

    Anyone has any idea how to do it ?

     

    10x

    Ido

    Monday, January 19, 2009 10:48 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    You can't do this type of query in FetchXML. As a general rule, any SQL query that can only be written using subqueries cannot be implemented via FetchXML.

     

    Unfortunately there's no obvious way around this. You could write a plugin on the Execute method to intercept the results of a query, and change what is returned to the user, but it's fairly complex, and I've not found a good way to manage the paging numbers in this way

     

    Monday, January 19, 2009 11:44 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You can't do this type of query in FetchXML. As a general rule, any SQL query that can only be written using subqueries cannot be implemented via FetchXML.

     

    Unfortunately there's no obvious way around this. You could write a plugin on the Execute method to intercept the results of a query, and change what is returned to the user, but it's fairly complex, and I've not found a good way to manage the paging numbers in this way

     

    Monday, January 19, 2009 11:44 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    why not

     

    SELECT * From dbo.FilteredContact

    JOIN dbo.FilteredNew_Codes

    ON FilteredNew_Codes.new_contactnameid <> FilteredContact.contactid

    WHERE FilteredNew_Codes.new_codeidname = 'NAB')

     

    I know it's one of those ugly double triangle joins and your dba won't like it but Microsoft leaves you no choice.  You can always hope the optimizer converts the query execution to something tighter.

    Tuesday, January 20, 2009 11:08 PM