|
|
|
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 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 1179 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 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 #607698 is a reply to message #607697] |
Tue, 11 February 2014 03:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 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 #607700 is a reply to message #607699] |
Tue, 11 February 2014 03:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Tue, 11 February 2014 09:43Quote:
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 #607705 is a reply to message #607704] |
Tue, 11 February 2014 04:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 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 #607709 is a reply to message #607708] |
Tue, 11 February 2014 04:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 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 #607717 is a reply to message #607714] |
Tue, 11 February 2014 05:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 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:13Quote: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:21When 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.
|
|
|
|