5 Replies Latest reply on Mar 31, 2011 12:27 PM by gwsmith

    OR operator doesn't work in query

    Apprentice

      In Incident, we have a Raise User (the person who called) and an End User (the person Raise User called about).  I'm trying to create a query that shows users their open Incidents, and they could be either the Raise User or the End User.  So in the query I've added criteria for when current user is either the Raise User or the End User.  The criteria screen looks like this:

       

      1-CriteriaList.jpg

      The problem is, even though I'm using OR on both criteria, the query's treating it like an AND.  It will only show an incident in the results if both the Raise User and End User are the current user.  I've removed all other criteria from the query.  I've even gone into the Advanced section and put "1 OR 2" and it still treats it like an AND.

        • 1. Re: OR operator doesn't work in query
          Apprentice

          I ran a trace on the SQL server and caught the SQL statement Service Desk is sending:

           

          exec sp_executesql N'SELECT TOP 15 T1.pm_colour,T2.im_id,T1.pm_title,T3.usr_guid,T3.usr_locations,T3.usr_deleted,T4.im_guid,T4.im_title,T4.im_deleted,T5.lc_guid,T5.lc_name,T5.lc_title,T1.pm_creation_date,T1.pm_raise_user_guid,T2.usr_enduser,T1.pm_guid,T1.pm_lifecycle_guid,T2.im_has_mismatch,T2.im_clock_stopped,T2.im_current_assignment_guid FROM pm_process T1 INNER JOIN im_incident T2 ON T2.pm_guid=T1.pm_guid LEFT OUTER JOIN usr_locations T3 ON (T2.usr_locations=T3.usr_guid) LEFT OUTER JOIN im_incident_category T4 ON (T2.im_category_guid=T4.im_guid) INNER JOIN lc_status T5 ON (T1.pm_status_guid=T5.lc_guid) WHERE (T1.pm_raise_user_guid=@0 AND T2.usr_enduser=@1) ORDER BY T1.pm_guid',N'@0 uniqueidentifier,@1 uniqueidentifier',@0='DE058170-BE53-4506-AFC9-ECDB7D7DCED1',@1='DE058170-BE53-4506-AFC9-ECDB7D7DCED1'

           

          Note the AND in the WHERE statement:  "WHERE (T1.pm_raise_user_guid=@0 AND T2.usr_enduser=@1)"  That's proof that it's not using the OR set on the query screen.  Is there some trick to using OR that I'm missing?

          • 2. Re: OR operator doesn't work in query
            Stu McNeill Employee

            Hi Grant,

             

            Are you saving the query and running it for real or using the "Test Query" option within Query Designer?  There is a known issue with OR criteria being treated as AND when you do the tests which has been logged as problem 3831, but running the query for real should be fine.

             

            Thanks

            • 3. Re: OR operator doesn't work in query
              Apprentice

              It looks like you have a similar bug in Web Access.  I first noticed the problem when end users were seeing closed Incidents in their list of open Incidents.  The query there might have been based on one created in Query Designer, but it would have been modified later in Web Access.  Please don't tell me you're not going to fix it until the next release, or unless enough people complain about it.  That's what I heard about my last Web Access bug.

               

              (I saved in Query Designer and, surprise, the query worked correctly.)

               

              I was wondering if having two relationships between User and Incident was part of the problem.  I haven't noticed any other queries failing.

              • 4. Re: OR operator doesn't work in query
                Stu McNeill Employee

                Hi Grant,

                 

                I'm glad that worked.  You're using version 7.4 so there shouldn't be any issues with OR criteria in Web Access.  However when you use a combination of OR and AND criteria you need to be careful at how they are organised.

                 

                For example if you create the query in Console (based loosely off your screenshot):

                 

                1. Creation User = me

                2. OR Raise User = me

                3. AND Status = Open

                 

                This will run as "1 OR 2 AND 3" but the grouping is open to interpretation.  Without any further configuration this example will actually group as "1 OR (2 AND 3)" which means you will get some results back at the wrong status.  For this to work as you'd expect you need to use the advanced criteria feature which allows you to write the logic to use, in this case "(1 OR 2) AND 3".  This is enabled at the bottom of the Criteria page in Query Designer.

                 

                This feature has not been directly ported to Web Access but there is a direct equivalent.  When you look at a Web Access query you can right-click on an existing filter create a sub-filter which is how the grouping is designed.  The filters for the same example as above would look like this:

                 

                | AND Status = Open

                | | ..... Creation User = me

                | | OR Raise User = me

                 

                For this one you would create the Status filter first, then right-click it and create the first sub-filter, then right-click that and create another (not sub) filter.

                 

                I hope that clears things up.  If I've totally missed the mark please could you post some screenshots of how the filters look at the top of your query in Web Access?

                 

                Thanks

                • 5. Re: OR operator doesn't work in query
                  Apprentice

                  Thanks.  I believe I have this working correctly now.  For the record, my query criteria in Web Access ended up looking like this:

                   

                  2-WebDeskFilter.jpg