How to Use ORDERBY in the flexible serach query (UNION)

[mohammed24]

Hi Experts, I am trying to create flexible search query something as below :

 SELECT uniontable.PK FROM
 (
    {{
       SELECT {p:PK} AS PK FROM {Product AS p}
       WHERE {p.code} = ?code
    }}
    UNION ALL
    {{
       SELECT {p:PK} AS PK FROM {Product AS p}
       WHERE {p.code} = ?code
    }}
 ) uniontable

Can anyone throw some light on how to use "ORDER BY " creation time of product keyword in above query?

[arvind-kumar_avinash · Accepted solution]

 SELECT uniontable.PK, uniontable.creationtime FROM
  (
     {{
        SELECT {p:PK} AS PK, {creationtime} AS creationtime FROM {Product AS p}
        WHERE {p.code} = ?code
     }}
     UNION ALL
     {{
        SELECT {p:PK} AS PK, {creationtime} AS creationtime FROM {Product AS p}
        WHERE {p.code} = ?code
     }}
  ) uniontable ORDERR BY uniontable.creationtime

[mohammed24 · Accepted solution]

This query is working fine in mysql but when i use the same in oracle it throws below error :

 Exception message: SAP DBTech JDBC: [260]: invalid column name: not included in SELECT DISTINCT column list: UNIONTABLE.CREATIONTIME: line 5 col 22 (at pos 2009)

Do we i need to use different query in oracle db?

[arvind-kumar_avinash · Accepted solution]

It may be a problem because of the case sensitivity of the column name. Please check the case of the column name in your database and use the same case. Alternatively, you can try using ORDERR BY uniontable."creationtime"

If it still doesn't work, please try using an alias as follows:

 SELECT uniontable.PK, uniontable.creationtime AS TIMECREATED FROM
   (
      {{
         SELECT {p:PK} AS PK, {creationtime} AS creationtime FROM {Product AS p}
         WHERE {p.code} = ?code
      }}
      UNION ALL
      {{
         SELECT {p:PK} AS PK, {creationtime} AS creationtime FROM {Product AS p}
         WHERE {p.code} = ?code
      }}
   ) uniontable ORDERR BY TIMECREATED