Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
QueryExpression Help RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    How would you add the following filter from the SQL query into QueryExpression?

    select contactid from filteredcontact as c with (nolock)
where emailaddress1 is null and statecodename = 'Active'
and (select top 1 emailaddress1 from filteredcontact as c2 with (nolock) where c2.emailaddress1 is not null and c2.statecodename = 'Active' and
c2.parentcustomerid = c.parentcustomerid) >= '2'

    specifically this bit

    (select top 1 emailaddress1 from filteredcontact as c2 with (nolock) where c2.emailaddress1 is not null and c2.statecodename = 'Active' and
    c2.parentcustomerid = c.parentcustomerid) >= '2'

    Thanks

    Pete

    Pete

    Thursday, June 26, 2014 9:18 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    can you explain what are you trying to achieve with this query?

    My blog: www.crmanswers.net - Rockstar 365 Profile

    Thursday, June 26, 2014 9:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    this query shows all contacts that are part of an account that has 2 or more contacts. Very good reason for returning these records but I need to recreate it in queryexpression first :)

    Pete

    Thursday, June 26, 2014 2:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I don't think your SQL query does what you want it to - you seem to be comparing an email address against the string '2', rather than counting anything

    You can't do this in one QueryExpression. Probably your best option if using CRM queries is:

    1. Use a FetchExpression that returns accounts with a count of the number of contacts they have
    2. Interate through these accounts to find those that have 2 or more contacts (you can't filter these in the query, as Fetch has no equivalent of a Having clause)
    3. Query the contacts that belong to these accounts. If the number of matching accounts is less than ~1000 you can do this in one query with a large IN expression, but if there are more than these you could hit a SQL limit in the number of parameters in one query, so you'd need separate queries

    Alternatively, if you have CRM OnPremise, then you could use the System.Data classes to query the CRM filtered views

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, June 27, 2014 12:06 PM
    Moderator