Home » Developer & Programmer » Reports & Discoverer » ORA-00920 error in discoverer
ORA-00920 error in discoverer [message #327693] Tue, 17 June 2008 04:47 Go to next message
ar.forums
Messages: 7
Registered: June 2008
Location: Saudi Arabia
Junior Member
Hi,

I am learning discoverer. While executing the query i got an error saying "ORA-0092 - invalid sql query"

SELECT
S.user_je_source_name Source,
B.name Batch_Name,
B.default_effective_date Batch_Eff_date,
B.posted_date Batch_Posted_Date,
B.default_period_name Batch_Period,
B.je_batch_id Batch_Id,
B.running_total_accounted_dr B_TOT_DR,
B.running_total_accounted_cr B_TOT_CR,
D.je_header_id Header_id,
D.name Header_Name,
D.default_effective_date Header_Eff_Date,
C.user_je_category_name Category,
D.running_total_accounted_dr H_TOT_DR,
D.running_total_accounted_cr H_TOT_CR,
J.je_line_num Je_Line_Num,
decode(nvl(CC.code_combination_id, -1), -1, 'A',&FLEXDATA)
FLEXDATA_H,
J.effective_date Line_Effective_Date,
J.description Line_Description,
J.accounted_dr Line_Acc_Dr,
J.accounted_cr Line_Acc_Cr,
D.currency_code Currency_Code,
D.external_reference Header_Reference,
&POSTING_STATUS_SELECT Reference1_4,
nvl(J.stat_amount,0) Line_Stat_Amount,
GLL.description Batch_Type,
B.actual_flag Actual_Flag,
GLL2.meaning Journal_Type,
SOB.consolidation_sob_flag Cons_Sob_Flag,
j.attribute1,j.attribute2
FROM
gl_lookups GLL,
gl_je_sources S,
gl_je_categories C,
gl_je_lines J,
gl_code_combinations CC,
gl_je_headers D,
gl_je_batches B,
gl_lookups GLL2,
gl_sets_of_books SOB
WHERE
GLL.lookup_code = B.actual_flag AND
GLL.lookup_type = 'BATCH_TYPE' AND
GLL2.lookup_type = 'AB_JOURNAL_TYPE' AND
GLL2.lookup_code = B.average_journal_flag AND
SOB.set_of_books_id = :P_SET_OF_BOOKS_ID AND
S.je_source_name = D.je_source AND
C.je_category_name = D.je_category AND
J.code_combination_id = CC.code_combination_id(+) AND
J.je_header_id = D.je_header_id AND
&CURRENCY_WHERE AND
D.je_source = NVL(:P_JE_SOURCE_NAME, D.je_source) AND
D.je_batch_id = B.je_batch_id AND
&POSTING_STATUS_WHERE AND
B.name = NVL(:P_BATCH_NAME, B.name) AND
&PERIOD_WHERE AND
B.set_of_books_id = :P_SET_OF_BOOKS_ID
ORDER BY
S.user_je_source_name,
B.actual_flag,
B.name,
B.default_effective_date,
D.name,
J.je_line_num


Re: ORA-00920 error in discoverer [message #327714 is a reply to message #327693] Tue, 17 June 2008 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 19889
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The error doesn't seem to be related with Discoverer, but SQL use.
Oracle
ORA-00920 invalid relational operator

Cause: A search condition was entered with an invalid or missing relational operator.

Action: Include a valid relational operator such as =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition.


Please, read OraFAQ Forum Guide to learn how to properly format your code (i.e. use of [code] tags). It would improve code readability; the way it is now, it is kind of difficult to see what is going on.

Although I don't know Discoverer, query looks quite well. "&CURRENCY_WHERE" (and similar) look like lexical parameters which are used in Reports Builder. Check whether they are correctly evaluated.
Re: ORA-00920 error in discoverer [message #508533 is a reply to message #327693] Mon, 23 May 2011 00:48 Go to previous message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
Hi,

in your query is having lexical and bind parameters so you are getting the error message in discoverer you can not pass any variable,
in the place of bind and lexical parameters pass hard code values or convert into query only then it will work.
Previous Topic: How to change database log in at runtime report
Next Topic: Control pages dynamically before printing
Goto Forum:
  


Current Time: Sat Dec 20 10:00:44 CST 2014

Total time taken to generate the page: 0.09002 seconds