Uploaded image for project: 'OpenMRS Core'
  1. OpenMRS Core
  2. TRUNK-2634

Concept Search Generates non-standard SQL

    XMLWordPrintable

Details

    Description

      The HibernateConceptDAO uses Criteria API for searching. It uses the DISTINCT, GROUP BY and ORDER BY clauses. The getConcepts method generates non-standard SQL that runs fine on MySQL, but does not work with other databases like PostgreSQL, SQL Server and Oracle.

      1.) The GROUP BY query in standard SQL requires Single-Value Rule, i.e. every column in the select-list must either be named in the GROUP BY clause or else be an argument to a set function. The concept search uses only one property in the grouping and this produces non-standard SQL

      2.) the ORDER BY clause requires that property for ordering should be defined in the select list, where as in the concept search we are using "weight" in ORDER BY and using "max(weight)" in SELECT. Thus, in the ORDER BY, we need to use "max(weight)" as well.

      Gliffy Diagrams

        Attachments

          Issue Links

            Activity

              People

                sunbiz Saptarshi Purkayastha
                sunbiz Saptarshi Purkayastha
                Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved: