Home » Developer & Programmer » Reports & Discoverer » ORA-00933 SQL Command not ended properly
ORA-00933 SQL Command not ended properly [message #607680] Tue, 11 February 2014 01:27 Go to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,

ORA-00933 SQL Command not ended properly , select * from emp==> order by 8 asc.
My sql query statement.

select * from emp &lp_DEPTNO
Re: ORA-00933 SQL Command not ended properly [message #607681 is a reply to message #607680] Tue, 11 February 2014 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is &lp_DEPTNO?
Re: ORA-00933 SQL Command not ended properly [message #607682 is a reply to message #607681] Tue, 11 February 2014 01:44 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
&lp_DEPTNO is lexical parameter..
Re: ORA-00933 SQL Command not ended properly [message #607684 is a reply to message #607681] Tue, 11 February 2014 01:54 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
can i attach my report
Re: ORA-00933 SQL Command not ended properly [message #607686 is a reply to message #607684] Tue, 11 February 2014 01:56 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does &lp_DEPTNO get its value? Please, post that code (you can attach a report as well, but I won't download it. Someone else might, though).
Re: ORA-00933 SQL Command not ended properly [message #607687 is a reply to message #607686] Tue, 11 February 2014 02:00 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I have written BeforeReport trigger.

function BeforeReport return boolean is
begiN
  
    IF :P_DEPT=10 THEN
    	:lp_DEPTNO:='WHERE DEPTNO=10';
    ELSIF :P_DEPT =20 THEN
    	:LP_DEPTNO:='WHERE DEPTNO=20';
    ELSE
    	:LP_DEPTNO:=NULL;
    	END IF;
  
  return (TRUE);
end;
Re: ORA-00933 SQL Command not ended properly [message #607689 is a reply to message #607687] Tue, 11 February 2014 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where does You, in your first message
select * from emp==> order by 8 asc
come from?

P.S. Forgot to mention: maybe "Before report" trigger isn't the best choice for setting lexical parameters' values; I'd rather use "After parameter form" trigger.

[Updated on: Tue, 11 February 2014 02:07]

Report message to a moderator

Re: ORA-00933 SQL Command not ended properly [message #607690 is a reply to message #607689] Tue, 11 February 2014 02:08 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
It comes from, when i ran the form(i passed deptno=10)
Re: ORA-00933 SQL Command not ended properly [message #607693 is a reply to message #607690] Tue, 11 February 2014 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't understand the question. I asked where does
order by 8 asc
come from; how does this query look like? Something in this report evaluates to something that raises an error. For example:
select * 
from emp
where job = 'CLERK'
where deptno = 10         --> should be "and", not "where"
order by 8 asc
Re: ORA-00933 SQL Command not ended properly [message #607695 is a reply to message #607693] Tue, 11 February 2014 03:03 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
Please see the screen shot on this..

I have written at model
select * from emp &lp_DEPTNO

FUNCTION [color=red]Cf_empnoformula [/color]
RETURN NUMBER 
IS 
  v_empno NUMBER; 
BEGIN 
    :CP_ENAME := NULL; 

    SELECT mgr, 
           ename 
    INTO   v_empno, :CP_ENAME 
    FROM   emp 
    WHERE  empno = :EMPNO; 

    RETURN( v_empno ); 
EXCEPTION 
  WHEN no_data_found THEN 
             RETURN NULL; 
END; 


FUNCTION F_3formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    -- Automatically Generated from Reports Builder. 
    /*if (:CP_ENAME = 'KING') 
    then 
      srw.set_text_color('r50g0b0'); 
    end if; */ 
    IF :CP_ENAME = 'CLARK' THEN 
      RETURN ( TRUE ); 
    ELSE 
      RETURN( FALSE ); 
    END IF; 
END; 
  • Attachment: image1.png
    (Size: 68.92KB, Downloaded 37 times)

[Updated on: Tue, 11 February 2014 03:05]

Report message to a moderator

Re: ORA-00933 SQL Command not ended properly [message #607696 is a reply to message #607695] Tue, 11 February 2014 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
Originally you said your report ran a query that included an order by.
You then said the query was:
select * from emp &lp_DEPTNO 

You then posted the code that assigned a value to lp_DEPTNO. That code does not include an order by clause.
So based on what you've posted so far the resulting query does not contain an order by, so why do you think it does?
Re: ORA-00933 SQL Command not ended properly [message #607697 is a reply to message #607696] Tue, 11 February 2014 03:26 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I have written at the datamodel
select * from emp &lp_DEPTNO 


Create 2 parameters LP_DEPTNO & P_DEPT.

I got the out put where i passed in the P_DEPT parameter as 10. 1st i pass the deptno=10 in the lp_DEPTNO it got the error while i ran the report as like
select * from emp==> order by 8 asc.
why? Please explain.

./fa/11680/0/
  • Attachment: image1.png
    (Size: 35.09KB, Downloaded 148 times)

[Updated on: Tue, 11 February 2014 03:27]

Report message to a moderator

Re: ORA-00933 SQL Command not ended properly [message #607698 is a reply to message #607697] Tue, 11 February 2014 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Tue, 11 February 2014 09:26


I got the out put where i passed in the P_DEPT parameter as 10.

Do you mean the report worked when you specified p_dept as 10 and left lp_DEPTNO blank?

mist598 wrote on Tue, 11 February 2014 09:26

1st i pass the deptno=10 in the lp_DEPTNO it got the error

Does that mean you left p_dept blank and set lp_deptno to 'deptno=10'?

mist598 wrote on Tue, 11 February 2014 09:26

while i ran the report as like
select * from emp==> order by 8 asc.
why? Please explain.

The symbol ==> may mean something to you, but to us it just looks like invalid syntax.
Re: ORA-00933 SQL Command not ended properly [message #607699 is a reply to message #607698] Tue, 11 February 2014 03:43 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

1)Do you mean the report worked when you specified p_dept as 10 and left lp_DEPTNO blank?
2)Does that mean you left p_dept blank and set lp_deptno to 'deptno=10'?


Yes.

The Below query is correct right?

select * from emp &lp_DEPTNO 


Then why it shows an error as
select * from emp==> order by 8 asc.


Re: ORA-00933 SQL Command not ended properly [message #607700 is a reply to message #607699] Tue, 11 February 2014 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Tue, 11 February 2014 09:43
Quote:

1)Do you mean the report worked when you specified p_dept as 10 and left lp_DEPTNO blank?
2)Does that mean you left p_dept blank and set lp_deptno to 'deptno=10'?


Yes.


So to be absolutely clear, for 2 above, you entered the value 'deptno=10' in the parameter form for lp_deptno and not the value 'where deptno=10'?
And you left p_dept blank in the parameter form?

[quote title=mist598 wrote on Tue, 11 February 2014 09:43]Quote:


Then why it shows an error as
select * from emp==> order by 8 asc.




So the order by is appearing in the error message? Post a screen shot.
Re: ORA-00933 SQL Command not ended properly [message #607701 is a reply to message #607700] Tue, 11 February 2014 04:06 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I got error when i pass in the lp_deptno and left blank in the p_dept

./fa/11681/0/
  • Attachment: image1.png
    (Size: 33.98KB, Downloaded 124 times)
Re: ORA-00933 SQL Command not ended properly [message #607702 is a reply to message #607701] Tue, 11 February 2014 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I got error when i pass in the lp_deptno and left blank in the p_dept

Why do you do that? LP_DEPTNO is a lexical parameter, it is supposed to get its value through a trigger, based on what you enter into P_DEPT.

What did you enter into LP_DEPTNO, anyway?
Re: ORA-00933 SQL Command not ended properly [message #607703 is a reply to message #607702] Tue, 11 February 2014 04:15 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I passed in LP_DEPTNO =10 as well as P_DEPT=10 & i tried to pass in the both params as 10
Re: ORA-00933 SQL Command not ended properly [message #607704 is a reply to message #607703] Tue, 11 February 2014 04:21 Go to previous messageGo to next message
Littlefoot
Messages: 19341
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When entering values into parameters: forget that LP_DEPTNO even exists. Use P_DEPT only.

However, none of what you've written so far explains where ORDER BY 8 ASC comes from. Please, post a screenshot that precedes the one that you already posted, i.e. the one that shows what you entered as parameters' values which results in ORA-00933.

Also, try to recompile the report (Ctrl + Shift + K).
Re: ORA-00933 SQL Command not ended properly [message #607705 is a reply to message #607704] Tue, 11 February 2014 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
So it's not showing:
select * from emp==> order by 8 asc.

It's showing

select * from emp==> 10 order by 8 asc.

Which is to say that sql statement it's trying to execute is:

select * from emp 10 order by 8 asc.

Which is invalid sql. I'm not entirely sure where the 10 is appearing from, but I am sure you're ignoring your own code.
You have a BeforeReport trigger that sets lp_deptno based on p_dept. So supplying a value for lp_deptno in the parameter form is never going to work becuase you've specifically written code to overwrite it.
So stop trying to set that parameter in the parameter form and it should just work.
Re: ORA-00933 SQL Command not ended properly [message #607706 is a reply to message #607704] Tue, 11 February 2014 04:34 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
It works fine only when i pass 10 in the P_DEPT

./fa/11683/0/
  • Attachment: image1.png
    (Size: 20.98KB, Downloaded 127 times)
Re: ORA-00933 SQL Command not ended properly [message #607707 is a reply to message #607706] Tue, 11 February 2014 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
what happens if you pass 20 instead?
Re: ORA-00933 SQL Command not ended properly [message #607708 is a reply to message #607707] Tue, 11 February 2014 04:44 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
what happens if you pass 20 instead?

instead of 10 , it displays 20
select * from emp==> 20 order by 8 asc.
Re: ORA-00933 SQL Command not ended properly [message #607709 is a reply to message #607708] Tue, 11 February 2014 04:54 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
So if you pass:
p_dept: 10
lp_deptno: null

it works.

If you pass:
p_dept: 20
lp_deptno: null

You get:
select * from emp==> 20 order by 8 asc.

If you pass:
p_dept: null
lp_deptno: 'deptno=10'

You get:
select * from emp==> 10 order by 8 asc.

That makes absolutely no sense. There must be some code in the report that modifies lp_deptno other than what you've shown us. I can't open the report to check, so have a look.
Re: ORA-00933 SQL Command not ended properly [message #607710 is a reply to message #607709] Tue, 11 February 2014 05:08 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I have written in the Data model select * from emp &lp_DEPTNO

At Reports Trigger

1)BeforeReport
function BeforeReport return boolean is
begiN
  
    IF :P_DEPT=10 THEN
    	:lp_DEPTNO:='WHERE DEPTNO=10';
    ELSIF :P_DEPT =20 THEN
    	:LP_DEPTNO:='WHERE DEPTNO=20';
    ELSE
    	:LP_DEPTNO:=NULL;
    	END IF;
  
  return (TRUE);
end;


At Programs Unit.

1) Before report same as above
2)CF_EMPNOFormula
function CF_EMPNOFormula return Number iS
V_EMPNO NUMBER;
begin
		:CP_ENAME:=NULL;
  
  SELECT MGR,ENAME 
  INTO  V_EMPNO,:CP_ENAME
  FROM EMP WHERE EMPNO=:EMPNO;
  RETURN(V_EMPNO);
EXCEPTION
	WHEN NO_DATA_FOUND THEN RETURN NULL;  
end;


3)F_3FormatTrigger

function F_3FormatTrigger return boolean is
begin

  -- Automatically Generated from Reports Builder.
  /*if (:CP_ENAME = 'KING')
  then
    srw.set_text_color('r50g0b0');
  end if; */
  
  IF :CP_ENAME='CLARK' THEN
   return (TRUE);
  ELSE
  	RETURN(FALSE);
  	END IF;
end;

./fa/11684/0/

Thank You
  • Attachment: image2.png
    (Size: 12.55KB, Downloaded 107 times)
Re: ORA-00933 SQL Command not ended properly [message #607711 is a reply to message #607710] Tue, 11 February 2014 05:09 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
./fa/11685/0/

So if i pass:
p_dept: 10 or 20
lp_deptno: null

it works.

So if i pass:
p_dept: null
lp_deptno: 10 or 20

You get:
select * from emp==> 20 order by 8 asc.


  • Attachment: image1.png
    (Size: 13.01KB, Downloaded 117 times)

[Updated on: Tue, 11 February 2014 05:11]

Report message to a moderator

Re: ORA-00933 SQL Command not ended properly [message #607712 is a reply to message #607680] Tue, 11 February 2014 05:19 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
./fa/11686/0/
  • Attachment: image1.png
    (Size: 8.77KB, Downloaded 93 times)
Re: ORA-00933 SQL Command not ended properly [message #607714 is a reply to message #607680] Tue, 11 February 2014 05:30 Go to previous messageGo to next message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
I set LP_DEPTNO datatype as character(2000) and P_DEPT datatype as number, the datatype is not a problem right?
Re: ORA-00933 SQL Command not ended properly [message #607717 is a reply to message #607714] Tue, 11 February 2014 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Tue, 11 February 2014 11:09


So if i pass:
p_dept: 10 or 20
lp_deptno: null

it works.

So if i pass:
p_dept: null
lp_deptno: 10 or 20

You get:
select * from emp==> 20 order by 8 asc.



Ok. Stop. You need to switch your brain on, because you obviously haven't so far.


cookiemonster wrote on Tue, 11 February 2014 10:33
...... but I am sure you're ignoring your own code.
You have a BeforeReport trigger that sets lp_deptno based on p_dept. So supplying a value for lp_deptno in the parameter form is never going to work becuase you've specifically written code to overwrite it.
So stop trying to set that parameter in the parameter form and it should just work.


Littlefoot wrote on Tue, 11 February 2014 10:13
Quote:
I got error when i pass in the lp_deptno and left blank in the p_dept

Why do you do that? LP_DEPTNO is a lexical parameter, it is supposed to get its value through a trigger, based on what you enter into P_DEPT.


Littlefoot wrote on Tue, 11 February 2014 10:21
When entering values into parameters: forget that LP_DEPTNO even exists. Use P_DEPT only.



Read those quotes by me and LF and then ask yourself why you're specifying values for lp_deptno in the parameter form.
Re: ORA-00933 SQL Command not ended properly [message #607718 is a reply to message #607717] Tue, 11 February 2014 05:56 Go to previous message
mist598
Messages: 873
Registered: February 2013
Location: Hyderabad
Senior Member
IF :P_DEPT=10 THEN
    	 :lp_DEPTNO:='WHERE DEPTNO=10'; -->[b]This one[/b]
      else
         ...
         ....



Ok Got it .Thank You So Much Cookiemonster.. Smile

And Sorry for i am not recognized.

[Updated on: Tue, 11 February 2014 05:57]

Report message to a moderator

Previous Topic: Field Vertical alignment/ orientation
Next Topic: XML Publisher
Goto Forum:
  


Current Time: Thu Jul 31 14:05:16 CDT 2014

Total time taken to generate the page: 0.12408 seconds