Error in Case statement [message #445137] |
Fri, 26 February 2010 03:00  |
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 #445165 is a reply to message #445137] |
Fri, 26 February 2010 05:09  |
cookiemonster
Messages: 13963 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.
|
|
|