Drill Through - ERROR [HY000] [Oracle][ODBC][Ora]ORA-00920: invalid relational operator?

Jodie · 2025-07-24T10:49:58+00:00

There was an error rendering this rich post.

PLEASE HELP

I've spent weeks trying to solve this drill through issue.

I'm trying to Drill through a dataview using the following entities:

Month
Reporting Cost centre
Account

Originally i could get the drill through to produce results for reporting cost centre and Account correctly but not filtered correctly on the right month. It would seem the way the month entity is brought into the cube is quite convoluted.

This is what i get from the error log:

ERROR [HY000] [Oracle][ODBC][Ora]ORA-00920: invalid relational operator?

SELECT * FROM ( select ?CASE WHEN TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) < 10 THEN TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR)) ELSE TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR) + 1) END || LPAD(TO_CHAR(MOD(TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) + 2, 12) + 1), 2, '0') AS MONTH, ?? CODA."OAS_DOCLINE".el2 AS "Reporting Cost Centre", ?? CODA."OAS_DOCLINE".el3 AS Account, ?? CODA."OAS_DOCHEAD".yr,?? CODA."OAS_DOCHEAD".period,?? ltrim(CODA."OAS_DOCHEAD".docnum),?? CODA."OAS_DOCHEAD".docdate,?? CODA."OAS_DOCHEAD".doccode,?? CODA."OAS_DOCHEAD".origuser,?? CODA."OAS_DOCLINE".descr,?? CODA."OAS_DOCLINE".valuehome,?? CODA."OAS_DOCLINE".ref2,?? CODA."OAS_DOCLINE".ref3,?? CODA."OAS_DOCLINE".ref4,?? CODA."OAS_DOCLINE".ref5,?? CODA."OAS_DOCLINE".ref6??FROM?? CODA."OAS_COMPANY" INNER JOIN CODA."OAS_DOCHEAD" ON (CODA."OAS_COMPANY".code=CODA."OAS_DOCHEAD".cmpcode)?? INNER JOIN CODA."OAS_DOCLINE" ON (CODA."OAS_DOCHEAD".cmpcode=CODA."OAS_DOCLINE".cmpcode and CODA."OAS_DOCHEAD".doccode=CODA."OAS_DOCLINE".doccode and CODA."OAS_DOCHEAD".docnum=CODA."OAS_DOCLINE".docnum)?? WHERE CASE WHEN TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) < 10 THEN TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR)) ELSE TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR) + 1) END || LPAD(TO_CHAR(MOD(TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) + 2, 12) + 1), 2, '0') IN (?, ?) AND CODA.OAS_DOCLINE.EL2, = ??? AND CODA.OAS_DOCLINE.EL3, = ??? ) WHERE ROWNUM <= 10000

If i run the same query in SQL developer it works as expected and the only difference is part of the where clause where instead of the convoluted way of looking for "Month" i'm searching on Year and Period.

SELECT
CASE WHEN TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) < 10 THEN TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR)) ELSE TO_CHAR(TO_NUMBER(CODA.OAS_DOCHEAD.YR) + 1) END || LPAD(TO_CHAR(MOD(TO_NUMBER(CODA.OAS_DOCHEAD.PERIOD) + 2, 12) + 1), 2, '0') AS MONTH,
CODA."OAS_DOCLINE".el2 AS "REPORTING COST CENTRE",--dbo_oas_element2.name,
CODA."OAS_DOCLINE".el3 AS Account,
--dbo_oas_element3.name,
CODA."OAS_DOCHEAD".yr,
CODA."OAS_DOCHEAD".period,
ltrim(CODA."OAS_DOCHEAD".docnum),
CODA."OAS_DOCHEAD".docdate,
CODA."OAS_DOCHEAD".doccode,
CODA."OAS_DOCHEAD".origuser,
CODA."OAS_DOCLINE".descr,
CODA."OAS_DOCLINE".valuehome,
CODA."OAS_DOCLINE".ref2,
CODA."OAS_DOCLINE".ref3,
CODA."OAS_DOCLINE".ref4,
CODA."OAS_DOCLINE".ref5,
CODA."OAS_DOCLINE".ref6
FROM
CODA."OAS_COMPANY" INNER JOIN CODA."OAS_DOCHEAD" ON (CODA."OAS_COMPANY".code=CODA."OAS_DOCHEAD".cmpcode)
INNER JOIN CODA."OAS_DOCLINE" ON (CODA."OAS_DOCHEAD".cmpcode=CODA."OAS_DOCLINE".cmpcode and CODA."OAS_DOCHEAD".doccode=CODA."OAS_DOCLINE".doccode and CODA."OAS_DOCHEAD".docnum=CODA."OAS_DOCLINE".docnum)
--LEFT OUTER JOIN CODA."OAS_ELEMENT" dbo_oas_element2 ON (CODA."OAS_DOCLINE".cmpcode=dbo_oas_element2.cmpcode and CODA."OAS_DOCLINE".el2=dbo_oas_element2.code AND dbo_oas_element2.elmlevel=2)
--LEFT OUTER JOIN CODA."OAS_ELEMENT" dbo_oas_element3 ON (CODA."OAS_DOCLINE".cmpcode=dbo_oas_element3.cmpcode and CODA."OAS_DOCLINE".el3=dbo_oas_element3.code AND dbo_oas_element3.elmlevel=3)

WHERE
(
CODA."OAS_DOCHEAD".yr = '2024'
AND
CODA."OAS_DOCHEAD".period = '12'
AND
CODA."OAS_DOCLINE".el2 = 'R3569'
AND
CODA."OAS_DOCLINE".el3 = '6211020'
)

Find more posts tagged with

Accepted answers

All comments

Helmut Heimann

Hi @Jodie ,

did you try to change the advanced parameters in the datareader

I strongly suspect it's got something to do with the date format.

Best,
Helmut

Bart Scott

Having complex queries within Drill Thru is not normally advised. It is best to put all the SQL logic into a view, and then utilize the view for the drill thru which then allows you to just map column to the entities and the additional desired fields to be returned. Board needs to add a where clause during execution which sometimes will not add correctly in a complex manual view setup.

If you do want to continue using the manual view setup, it is also advised to remove unneeded items like the commented out lines and the comments. The more straight forward the SQL query, the better it will work within Board.

Bart Scott
bart.scott@cfosolutions.com

Jodie

when i turn off the manual script the Options selected are "Convert" "Join" "Number"

It's greyed out when on manual. This is my first time doing a drill through so all advice is appreciated

Jodie

I don't entirely understand this. Ultimately i have a dataview that contains the management accounts and sometimes we need more info at transactional level so i need to be able to drill through to get this.

Bart Scott

Yes that is what Drill thru gives you. But what happens is the dataview sends the parameters to the Drill Thru setup, which then attempts to add a where clause filtering on what is passed from the Dataview into the SQL that is setup. We have experienced when the Drill Thru is formed manually with joins, case statements, comments, etc that Board cannot successfully adjust the Drill Thru query properly, which then causes a SQL error during Runtime (even though Browse may work correctly). What we normally recommend is to create an Oracle view, with the joins, transformations, comments, case statements, etc, so that within Board it is as simple as leaving it automatic and dragging the columns from the view to the appropriate Entities or additional members as desired. That puts the work on the Oracle side for running the query and the Board side can easily send the where clauses without issues.

Jodie

Is this where you do the drag and drop from the database field to the board entity? I have tried this also but get the error 00936 Missing expression

The only thing i can't drag and drop is the month - in the database there is a year and period column e.g. yr = 2024 and period = 12 which is equivalent to 202503. the only way to get this is by running the select case statement.

Jodie

I'm sorry i just don't understand what you mean by a view within oracle itself

Bart Scott

You would need to discuss with your IT area, or the DBA for Oracle about creating that query as a view within Oracle. It is an object within Oracle itself, and appears under the View list (instead of the table list).

Jodie

Ah i see what you mean yes i have seen that now come to think of it. Thanks for your help