Oracle Forms - Error 103, Encountered the symbol “ORDER”

1
votes

I am using below query in my forms but it throws this error:

Oracle Forms - Error 103, Encountered the symbol “END”.

Same query its working on toad, there is no issue.

Is there any limitation to use ORDER by in forms or in sub query or as select table ?

Forms 6i oracle 10g

SELECT apr_rate 
INTO lv_apr_rate      
FROM 
(SELECT apr_rate                                                                                                  
     FROM cm_contract_extension
    WHERE country_code = '044' 
      AND company_code = '0441' 
      AND contract_number = '0000002140426510'
      AND supply_sequence_number = 1  
      ORDER BY version_number DESC)
        WHERE ROWNUM < 2 ;

BR, Shadab Hussain

2
sqloracleforms
If you are using at the block level, there is a block level property to specify which column the block should ORDER BY rather than specifying ORDER BY explicitly in SQL. - Jacob
No, I am not using at the block level. - Shadab Hussain

2 Answers

0
votes

Forms doesn't let you use ORDER BY, if you want that you'd need to write it as a database procedure.

0
votes

In Oracle, you can write this using KEEP:

SELECT MAX(apr_rate) KEEP (DENSE_RANK FIRST ORDER BY version_number DESC) as apr_rate                                                                                                  
FROM cm_contract_extension
WHERE country_code = '044' AND
      company_code = '0441' AND
      contract_number = '0000002140426510' AND
      supply_sequence_number = 1 ;