My Oracle Support Banner

[FMWGEN][SQLServer JDBC Driver][SQLServer] The ORDER BY Clause Is Invalid In Views, Inline Functions (Doc ID 1944617.1)

Last updated on NOVEMBER 08, 2019

Applies to:

Management Pack for Oracle GoldenGate - Version 12.1.3.0.0 and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Goal

Why OGG Monitor 12.1.3 Server with repository on SQL Server throws the following error:
------------------------
Internal Exception: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Error Code: 1033
Call: SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT t0.ID AS a1, t0.notice_date AS a2, t0.MESSAGE AS a3, t0.SEVERITY AS a4, t0.alert_id AS a5 FROM alerts t3, alert_templates t2, alert_subscriptions t1, alert_notices t0 WHERE (((t1.username = ?) AND (t3.obj_id = ?)) AND (((t3.ID = t0.alert_id) AND (t2.ID = t3.template_id)) AND (t1.template_id = t2.ID))) ORDER BY t0.notice_date DESC) a WHERE ROWNUM ?
bind => [4 parameters bound]
Query: ReadAllQuery(referenceClass=AlertNoticeEntity sql="SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT t0.ID AS a1, t0.notice_date AS a2, t0.MESSAGE AS a3, t0.SEVERITY AS a4, t0.alert_id AS a5 FROM alerts t3, alert_templates t2, alert_subscriptions t1, alert_notices t0 WHERE (((t1.username = ?) AND (t3.obj_id = ?)) AND (((t3.ID = t0.alert_id) AND (t2.ID = t3.template_id)) AND (t1.template_id = t2.ID))) ORDER BY
-----------------------
and how to fix?
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.