Dynamics Community Forum Thread Details

FetchXML filtered results does not match with what I expect

Hello,

I have this FetchXML:

  
    
    
    
    
    
      
        
        
          750010000
        
        
          750010000
          750010002
          750010003
        
        
          750010001
          750010003
          750010004
          750010005
        
      
      
        
          b429cc18-cd51-e711-810b-70106faa5611
          208276f0-e8a9-ec11-983f-0022489dc09e
        
        
          750010000
        
        
          750010000
          750010002
          750010003
        
        
          750010001
          750010003
          750010005
        
      
      
        
          f9aafaaf-238a-ea11-a812-000d3ab8de1e
          b429cc18-cd51-e711-810b-70106faa5611
          208276f0-e8a9-ec11-983f-0022489dc09e
        
        
          1
          750010003
        
        
          750010000
          750010001
        
        
          750010001
          750010002
          750010004
          750010005

The SQL Equivalent is:

SELECT TOP 50 if_contributordeliver,
              statuscode,
              if_vdnthinkfinalthink,
              if_pcstage
FROM   if_program
WHERE  ((if_contributordeveloper = 'f9aafaaf-238a-ea11-a812-000d3ab8de1e'
         AND statuscode IN ('750010000')
         AND if_vdnthinkfinalthink IN ('750010000', '750010002', '750010003')
         AND if_pcstage IN ('750010001', '750010003', '750010004', '750010005'))
        OR (if_contributordeveloper IN ('b429cc18-cd51-e711-810b-70106faa5611', '208276f0-e8a9-ec11-983f-0022489dc09e')
            AND statuscode IN ('750010000')
            AND if_vdnthinkfinalthink IN ('750010000', '750010002', '750010003')
            AND if_pcstage IN ('750010001', '750010003', '750010005'))
        OR (if_contributordeveloper NOT IN ('f9aafaaf-238a-ea11-a812-000d3ab8de1e', 'b429cc18-cd51-e711-810b-70106faa5611', '208276f0-e8a9-ec11-983f-0022489dc09e')
            AND statuscode IN ('1', '750010003')
            AND if_vdnthinkfinalthink IN ('750010000', '750010001')
            AND if_pcstage IN ('750010001', '750010002', '750010004', '750010005')));

The results of this fetchXML (tested in FetchXML Builder) :

5826.Screenshot_5F00_5.png

The problem here is that the GUID "b429cc18-cd51-e711-810b-70106faa5611" is included in the results but it doesn't make sense because the statuscode value is "750010003" and in the condition

    b429cc18-cd51-e711-810b-70106faa5611
    208276f0-e8a9-ec11-983f-0022489dc09e


    750010000

so this will be evaluated to false and also the last condition:

        
          f9aafaaf-238a-ea11-a812-000d3ab8de1e
          b429cc18-cd51-e711-810b-70106faa5611
          208276f0-e8a9-ec11-983f-0022489dc09e
        
        
          1
          750010003

This condition alse will be evaluated to false because I used "not-in".

So logically all the conditions are false so the record will not be shown in the results!!! but as you can see I get the record in the result set, why?, am I missing something here?

Quick Links

Leaderboard