You have to use select into statement (or) use select statement as a cursor definition other wise you can not use select statements directly in forms as mentioned in your example.
Hi, Thanx to reply, Actually I have tried the query in both(using into and using cursor) but in both time there is an error Error 103 at line12,column 70 Encountered the symbol"[" when expecting from. the full querry I am using is :
select REGISTRATIONID into REG from (select registrationid ,dense_rank() over(order by REGISTRATIONID asc) ranking from TBL_REGISTRATION where ADMITTED=0)where ranking=1;
I think dense_rank analytical function is not supported in oracle forms 11g.,
If So please suggest me an alternative
Yes you are correct Aaditya dense_rank not supported by forms.
create view tbl_reg_vu as
select registrationid ,dense_rank() over(order by REGISTRATIONID asc) ranking
from TBL_REGISTRATION where ADMITTED=0
SQL> select * from tbl_reg;
6 rows selected.
In your forms change the statement as follows,
select REGISTRATIONID into REG from tbl_reg_vu where ranking=1;
I hope this will help you.
You are right I have tried it with view n it works.I just wanted to know that dense_rank (or its alternative) function is applicable in forms or not. thank you
Analytic functions (like dense_rank) are not supported in forms. The only way would be to use a view as already suggested or use dynamic SQL.
The view is the way better aproach, and basing your example there is no reason why you should use dynamic SQL instead.