Home » SQL & PL/SQL » SQL & PL/SQL » error
error [message #577721] Tue, 19 February 2013 20:46 Go to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi experts

Below is the query for Oracle 11g

select	C1BCT as sortorder, trim(C1Y56CH01), trim(C1Y56CH02), trim(C1Y56CH03), trim(C1Y56CH04), trim(C1Y56CH08)
		, trim(C1Y56CS09), trim(C1Y56CS03), trim(C1Y56CS04), trim(C1Y56CS05), trim(C1Y56CS06)
		, trim(C1Y56CS07), trim(C1Y56CS08), trim(C1Y56CS10), trim(C1Y56CS11), trim(C1Y56CS12)
		, trim(C1Y56CS13), trim(C1Y56CS15), trim(C1Y56CD05), trim(C1Y56CD13), trim(C1Y56CD04)
		, trim(C1Y56CD14), trim(C1Y56CD06), trim(C1Y56CD11), trim(C1Y56CD07), trim(C1Y56CD10)
		, trim(C1Y56CD12), C1BCT 
from	CRPDTA.F56055C1 A 
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)

union

select	C1BCT+iteration.rn+1 as sortorder, trim(C1Y56CH01), trim(C1Y56CH02), trim(C1Y56CH03), trim(C1Y56CH04), trim(C1Y56CH08)
		, trim(C1Y56CS09), trim(C1Y56CS03), trim(C1Y56CS04), trim(C1Y56CS05), trim(C1Y56CS06)
		, trim(C1Y56CS07), trim(C1Y56CS08), trim(C1Y56CS10), trim(C1Y56CS11), trim(C1Y56CS12)
		, trim(C1Y56CS13), trim(C1Y56CS15), trim(C1Y56CD05), trim(C1Y56CD13), trim(C1Y56CD04)
		, trim(C1Y56CD14), trim(C1Y56CD06), trim(C1Y56CD11), trim(C1Y56CD07), trim(C1Y56CD10)
		, trim(C1Y56CD12), C1BCT 
from	CRPDTA.F56055C1 r_outer, (	select	rownum rn 
									from	dual connect by level < (	select	count(*) 
																		from	(	select	C1BCT, trim(C1Y56CH01)
																					from	CRPDTA.F56055C1 A 
																					where	C1Y55ELM01 = 'REP_ABSO' 
																					and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
																					and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
																				) t1)
																	) iteration
where	C1Y55ELM01 = 'REP_ABSO' 
and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)
and		trim(C1Y56CH01)='B'
and		( select	count(C1Y56CH01) 
			from	(	select	C1BCT, C1Y56CH01
						from	CRPDTA.F56055C1 A 
						where	C1Y55ELM01 = 'REP_ABSO' 
						and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
						and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_inner 
			where	trim(r_inner.C1Y56CH01)='D' 
			and		r_inner.C1BCT>r_outer.C1BCT 
			and		r_inner.C1BCT<(	select	nvl(MIN(C1BCT), 99999999) as mincolindex
									from	(	select	C1BCT, C1Y56CH01
												from	CRPDTA.F56055C1 A 
												where	C1Y55ELM01 = 'REP_ABSO' 
												and		(trim(C1Y55ELM03) is null or trim(C1Y55ELM03) in ('EDITED')) 
												and		C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) t1 r_min 
                                    where	trim(r_min.C1Y56CH01)='B' 
                                    and		r_min.C1BCT>r_outer.C1BCT))>iteration.rn
                                    
                                 
                                    
order
by  sortorder



while running the query I am getting
Error at Command Line:46 Column:70
Error report:
SQL Error: ORA-00936: missing expression

Please help
Re: error [message #577722 is a reply to message #577721] Tue, 19 February 2013 20:53 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>CRPDTA.F56055C1)) t1 r_min
error is above
Re: error [message #577723 is a reply to message #577722] Tue, 19 February 2013 20:59 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi Blackswan Tnaks for reply

I didnt get you . Is that is the error cause?

Yes my 46 line where it showing teh error is and C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) t1 r_min

What should I do it in here ? Please help
Re: error [message #577724 is a reply to message #577723] Tue, 19 February 2013 21:06 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Thanks got the error. Its worked
Re: error [message #577725 is a reply to message #577723] Tue, 19 February 2013 21:10 Go to previous message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
I guess that you did not actually compose/develop/construct the posted SQL.

>and C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) t1 r_min

what results if you change the line from as above to as below

and C1ICU = (select MAX(C1ICU) from CRPDTA.F56055C1)) r_min
Previous Topic: Bulk Collect & For all in PL/SQL
Next Topic: Decode logic not working
Goto Forum:
  


Current Time: Thu Dec 18 17:54:21 CST 2014

Total time taken to generate the page: 0.09261 seconds