cancel
Showing results for 
Search instead for 
Did you mean: 

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

mohammed24
Participant
0 Kudos

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?

View Entire Topic
former_member620692
Active Contributor
0 Kudos
 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
Participant
0 Kudos

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?

former_member620692
Active Contributor
0 Kudos

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