Home » Developer & Programmer » Forms » Error in Case statement (Oracle 10G)
Error in Case statement [message #445137] Fri, 26 February 2010 03:00 Go to next message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
Hi, i'm using following query into oracle form v.10
if i execute statement into sqlplus i'm getting proper output,but same query is not compiling into forms getting error

Error:103 at line 138,column 18
Encountered the symbol "select" when expecting on of the following {-+ case mod new not null others.....)
Error:103 at line 140 column 45
Encountered the symbol ")" when expecting on of the following
(*@%.....)

Please advice reg same

Thank you
SELECT parameter1 emp_code, agentname (parameter1) emp_name,
       DECODE (emp_status,
               'A', 'active',
               'R', 'resigned',
               'Active'
              ) emp_status,
       create_datetime,
       (CASE
           WHEN TRUNC (create_datetime) < '01-Apr-2009'
              THEN 'Emp was coded before 01-Apr-2009'
        END
       ) code_remarks,
       (CASE
           WHEN (SELECT emp_flag
                   FROM emp_to_ter
                  WHERE emp_code = parameter1) = 'Y'
              THEN 'emp is converted to manager'
        END
       ) conversion_remarks,
       (CASE
           WHEN SUBSTR (parameter1, 1, 3) <> '20P'
              --AND agent_status = 'T'
        THEN parameter1 || ' is not Emp'
        END
       ) lg_remarks
  FROM t_upload a, emp_db b
 WHERE a.parameter1 = b.emp_code AND process = 'BLKRI';


[EDITED by LF: applied [code] tags]

[Updated on: Fri, 26 February 2010 04:05] by Moderator

Report message to a moderator

Re: Error in Case statement [message #445149 is a reply to message #445137] Fri, 26 February 2010 04:06 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
If sqlplus likes the query and forms doesn't then the simplest solution is to create a view with your query and then query the view from the form.
icon14.gif  Re: Error in Case statement [message #445164 is a reply to message #445149] Fri, 26 February 2010 05:00 Go to previous messageGo to next message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
Dear Sir,

I've created view, now its working.


Thank You very much.
Re: Error in Case statement [message #445165 is a reply to message #445137] Fri, 26 February 2010 05:09 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
One other thing, this:
(CASE
           WHEN TRUNC (create_datetime) < '01-Apr-2009'
              THEN 'Emp was coded before 01-Apr-2009'
        END
       ) code_remarks,
should be:
(CASE
           WHEN TRUNC (create_datetime) < to_date('01-Apr-2009', 'DD-Mon-YYYY')
              THEN 'Emp was coded before 01-Apr-2009'
        END
       ) code_remarks,

Otherwise you're relying on implicit conversion and the nls_date_format not changing.
Previous Topic: How to get current cursor postion in oracle 10g form
Next Topic: I have a question
Goto Forum:
  


Current Time: Wed Dec 07 03:20:30 CST 2016

Total time taken to generate the page: 0.06922 seconds