Openfire ORA-00907: Missing right parenthesis

Question

I have the following problem retrieving messages from the OpenFire Monitoring Service plugin. I discovered that the error is due to an incorrect query in the database but I still cannot detect the error for which the query is not working correctly.

SELECT
fromjid,
fromjidresource,
tojid,
tojidresource,
sentdate,
body,
stanza,
messageid,
barejid
FROM
(
SELECT
  DISTINCT ofmessagearchive.fromjid,
  ofmessagearchive.fromjidresource,
  ofmessagearchive.tojid,
  ofmessagearchive.tojidresource,
  ofmessagearchive.sentdate,
  ofmessagearchive.body,
  ofmessagearchive.stanza,
  ofmessagearchive.messageid,
  ofconparticipant.barejid
FROM
  ofmessagearchive
  INNER JOIN ofconparticipant ON ofmessagearchive.conversationid = 
ofconparticipant.conversationid
WHERE
  (
    ofmessagearchive.stanza IS NOT NULL
    OR ofmessagearchive.body IS NOT NULL
  )
  AND ofmessagearchive.messageid IS NOT NULL
  AND ofmessagearchive.sentdate >= 0
  AND ofmessagearchive.sentdate <= 1602748770287
  AND ofconparticipant.barejid = 'usuario3@192.168.0.79'
  AND (
    ofmessagearchive.tojid = 'usuario4@192.168.0.79'
    OR ofmessagearchive.fromjid = 'usuario3@192.168.0.79'
  )
ORDER BY
  ofmessagearchive.sentdate DESC
LIMIT
  100
 ) AS part
ORDER BY
sentdate

I get an error when doing the following query

ORA-00907: missing right parenthesis

Command line error:32 Column: 9

Answer 1

1. There is no LIMIT keyword available in Oracle and if you are using Oracle 12c you can use FETCH FIRST 100 ROWS ONLY instead of it.

2. You cannot use AS to give alias to the sub query and it is not recognised by Oracle. So either you can remove the alias completely as you are not using it anywhere or just remove the AS and keep the alias name part only which should be fine.

Here is a good SO link about the Oracle limiting result set and you can always look into other sites available such as Oracle base or the official document as well. For 11g solution you have to use row_number