Home » Developer & Programmer » Reports & Discoverer » ORA - 933 (Oracle Reports 10g)
ORA - 933 [message #548051] Tue, 20 March 2012 00:33 Go to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
Following query is running fine when I run in the Toad, but when I paste this query to Oracle reports then it gives an error ORA - 933.
/* Formatted on 2012/03/20 08:23 (Formatter Plus v4.8.8) */
SELECT ROWNUM row_counter,
       CASE
          WHEN rm.qabila_id NOT IN ('38', '141')
             THEN    rm.name1
                  || ' '
                  || rm.name2
                  || ' '
                  || rm.name3
                  || ' '
                  || rm.name4
                  || ' '
                  || rm.name5
                  || ' '
                  || cq.qabila_name
          ELSE    rm.name1
               || ' '
               || rm.name2
               || ' '
               || rm.name3
               || ' '
               || rm.name4
               || ' '
               || rm.name5
       END person_name,
       rm.qid_no id_no,
       CASE
          WHEN rm.mobile_no IS NOT NULL AND rm.tel_no IS NOT NULL
             THEN rm.mobile_no || '/' || rm.tel_no
          WHEN rm.mobile_no IS NULL AND rm.tel_no IS NOT NULL
             THEN rm.tel_no
          WHEN rm.mobile_no IS NOT NULL AND rm.tel_no IS NULL
             THEN rm.mobile_no
          ELSE NULL
       END contact_no,
       rm.list_priority request_order, rm.attended attendence
  FROM jls_requests_master rm, cmn_qabilas cq
 WHERE rm.jalsa_srl = :jalsa_no
   AND rm.qabila_id = cq.qabila_id
   AND rm.list_no LIKE get_all (:list_no) 
   &sub_where_clause1
UNION
SELECT ROWNUM row_counter,
       CASE
          WHEN rm.qabila_id NOT IN ('38', '141')
             THEN    rm.name1
                  || ' '
                  || rm.name2
                  || ' '
                  || rm.name3
                  || ' '
                  || rm.name4
                  || ' '
                  || rm.name5
                  || ' '
                  || cq.qabila_name
          ELSE    rm.name1
               || ' '
               || rm.name2
               || ' '
               || rm.name3
               || ' '
               || rm.name4
               || ' '
               || rm.name5
       END person_name,
       rm.qid_no id_no,
       CASE
          WHEN rm.mobile_no IS NOT NULL AND rm.tel_no IS NOT NULL
             THEN rm.mobile_no || '/' || rm.tel_no
          WHEN rm.mobile_no IS NULL AND rm.tel_no IS NOT NULL
             THEN rm.tel_no
          WHEN rm.mobile_no IS NOT NULL AND rm.tel_no IS NULL
             THEN rm.mobile_no
          ELSE NULL
       END contact_no,
       rm.list_priority request_order, rm.attended attendence
  FROM jls_requests_archive rm, cmn_qabilas cq
 WHERE rm.jalsa_srl = :jalsa_no
   AND rm.qabila_id = cq.qabila_id
   AND rm.list_no LIKE get_all (:list_no) 
   &sub_where_clause1 
  &order_clause


I dont know what to do now as this is the best i can do. It will be great if somebody helps me in solving this error.

Thanks in advance.

Report message to a moderator

Re: ORA - 933 [message #548057 is a reply to message #548051] Tue, 20 March 2012 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Following query is running fine when I run in the To


1/ TOAD is a b..., though it away
2/ Use SQL*Plus, it will point you on the error if there is any.

As we have not your objects we can't do it for you.
So post the CREATE TABLE or other statements for your objects.

Regards
Michel

Report message to a moderator

Re: ORA - 933 [message #548062 is a reply to message #548057] Tue, 20 March 2012 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When does Reports return an error? As soon as you create a query, or when you run the report? If the latter, maybe your lexical parameters are evaluated to something "invalid". If that's so, where do you create these parameters? Display their values with SRW.MESSAGE so that you'd see what is going on.

Report message to a moderator

Re: ORA - 933 [message #548066 is a reply to message #548062] Tue, 20 March 2012 02:40 Go to previous message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
Littlefoot wrote on Tue, 20 March 2012 10:16
When does Reports return an error? As soon as you create a query, or when you run the report? If the latter, maybe your lexical parameters are evaluated to something "invalid". If that's so, where do you create these parameters? Display their values with SRW.MESSAGE so that you'd see what is going on.


Really I cant figure out the error itself. (ORA-00933 SQL command not properly ended)

BTW i could check the value of lexical parameters as it was giving error while writing the query not at the time of execution.

Anyways, I have solved the problem. just changing the name of one lexical parameter from &order_clause to &order_by. (The dumbest step I had ever taken.)

Thanks for replying ... Smile


Quote:
Use SQL*Plus, it will point you on the error if there is any.


I have also tried with SQL*PLUS but it gives no such error. BTW it is solved now.

Report message to a moderator

Previous Topic: Oracle apps Data does not show in Discoverer
Next Topic: Oracle reports 10g suddenly stopped working
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 02 09:18:21 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.