6 Replies Latest reply: Apr 8, 2013 8:54 AM by William Phelps RSS

    UCM search v/s Query search

    Chely
      I have executed a query thorough SQl smthing like:

      SELECT ddocname, revisions.did, revisions.drevlabel, revisions.drevisionid, revisions.drevrankFROM revisions, docmeta

      WHERE revisions.did = docmeta.did and UPPER(dDocType) LIKE UPPER('Records') AND dDocType IS NOT NULL AND UPPER(dSecurityGroup) LIKE UPPER('HROps%') AND dSecurityGroup IS NOT NULL AND dInDate BETWEEN '01-Mar-13 01:00:00' AND '31-Mar-13 11:59:59' AND UPPER(xhr_file_section) LIKE UPPER('10') AND xhr_file_section IS NOT NULL AND UPPER(xLibrary) LIKE UPPER('HR_OPS') AND xLibrary IS NOT NULL and drevrank > 0;


      It gave me near about 2410 records from database.

      But when i executed the standard UCM search with the same criterias it gave me near about 2390.


      Why there is a difference between two, i need the same resule from both cases. Please share if u have any idea.
        • 1. Re: UCM search v/s Query search
          Srinath Menon-Oracle
          Hi ,

          Can you capture the exact query from UCM search (using systemdatabase tracing) and then check / compare with the SQL query and see what is the difference .

          Thanks,
          Srinath
          • 2. Re: UCM search v/s Query search
            Chely
            Thanks for the reply...


            Could you please tell me the exact location, from which i can fetch the query fired by UCM.
            • 3. Re: UCM search v/s Query search
              Srinath Menon-Oracle
              Hi ,

              Go to Administration - system audit information - Tracing Sections - Active Sections .

              There remove all the entries and add systemdatabase* and enable Full Verbose Option . Update the change .

              On top - View server output - clear the logs

              Run the query from UCM and then go to View server logs - there you will see the exact query which was executed by UCM .

              Thanks,
              Srinath
              • 4. Re: UCM search v/s Query search
                Chely
                Thanks,
                M trying it and let u know soon...
                • 5. Re: UCM search v/s Query search
                  Chely
                  from ucm end i am getting this query which is completely different from my query , in fact table are different :


                  SELECT COUNT(*) FROM IdcColl2, DocMeta, RevClasses WHERE IdcColl2.dID=DocMeta.dID AND IdcColl2.dDocName =RevClasses.dDocName AND (((((UPPER(dDocType) LIKE UPPER('%Records%') AND dDocType IS NOT NULL AND UPPER(xLibrary) LIKE UPPER('%HR_OPS%') AND xLibrary IS NOT NULL AND UPPER(xhr_file_section) LIKE UPPER('%10%') AND xhr_file_section IS NOT NULL AND dInDate >= {ts '2013-03-01 00:00:00.000'} AND dInDate >= {ts '2013-03-31 00:00:00.000'})))))


                  It is giving me 11 records which is very less thasn the expected result.
                  • 6. Re: UCM search v/s Query search
                    William Phelps
                    Where do I start with the problems? Just a quick look reveals the following:

                    1 - The query shown that UCM is executing is a COUNT query, so technically you are comparing two different queries anyway.
                    2 - There is no security group in the UCM query.
                    3 -
                    UPPER(xhr_file_section) LIKE UPPER('10')
                    in the database query is not the same as
                    UPPER(xhr_file_section) LIKE UPPER('%10%')
                    in the UCM query. (The UCM query is really a substring query in that aspect.)
                    4 -
                    drevrank > 0
                    in the direct database query insures that you are selecting only those items that ARE NOT the latest revision. The Content Server query is ONLY going to look at the latest released revision where
                    drevrank = 0
                    I could also mention that the date values being used are not the same, but the scenario provided already has enough issues in why the results are not the same.