Home » SQL & PL/SQL » SQL & PL/SQL » SELECT statement in DECODE
SELECT statement in DECODE [message #36921] Thu, 03 January 2002 23:29 Go to next message
wangxq
Messages: 1
Registered: January 2002
Junior Member
the following statement execute successfully in SQLPLUS:
insert into new_ipflow
(year,month,day,ip_address,others_in)
select a.year,a.month,a.day,a.standard_ip,
decode(a.status,20,(select flow_in_sum from ipflow_mview
where year=a.year and month=a.month and day=a.day and standard_ip=a.standard_ip and status=20))
from ipflow_mview a;
but when i write the same statement in procedure.
it always compile error as below:
24/21 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

25/95 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
; return returning and or

----------------------------------------------------------------------
Re: SELECT statement in DECODE [message #36925 is a reply to message #36921] Fri, 04 January 2002 04:13 Go to previous messageGo to next message
RYAN
Messages: 22
Registered: December 2000
Junior Member
why not do the "select flow_in_sum from ipflow_mview" into a var then use it in the insert? it'll make the code easier to decipher too...

about the original question, this doesnt seem right to me, and i swear i've done this before...? go figure.

r.

----------------------------------------------------------------------
Re: SELECT statement in DECODE [message #36928 is a reply to message #36921] Fri, 04 January 2002 04:27 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
sql and pl/sql execution engines are different in oracle 8i and earlier versions. thats reason some of queries work in SQL and not in pl/sql. In oracle 9i, oracle made both engines similar.

----------------------------------------------------------------------
Re: SELECT statement in DECODE [message #36934 is a reply to message #36921] Fri, 04 January 2002 17:14 Go to previous messageGo to next message
wachaspati
Messages: 1
Registered: January 2002
Junior Member
Did you try using a function instead of that SQL in the decode ? The function will take the parameters as the columns used in sql. I am not sure if that will work in Pl/SQL..butI have used functions in sqls and they work in Pl/SQL also..The only thing is whether that works in Decode..Try :)

----------------------------------------------------------------------
Re: SELECT statement in DECODE [message #39196 is a reply to message #36921] Tue, 25 June 2002 10:42 Go to previous message
wachaspati Pandey
Messages: 1
Registered: June 2002
Junior Member
CREATE A FUNCTION
----------------

APPS@p2pdb1d:P2PDB40> ed
Wrote file afiedt.buf

1 create or replace function ret return number as
2 begin
3 return 5;
4* end;
APPS@p2pdb1d:P2PDB40> /

Function created.

USE IT IN DECODE
-----------------

APPS@p2pdb1d:P2PDB40> select decode(&x,2,ret,3) from dual;
Enter value for x: 2
old 1: select decode(&x,2,ret,3) from dual
new 1: select decode(2,2,ret,3) from dual

DECODE(2,2,RET,3)
-----------------
5

APPS@p2pdb1d:P2PDB40> select decode(&x,2,ret,3) from dual;
Enter value for x: 8
old 1: select decode(&x,2,ret,3) from dual
new 1: select decode(8,2,ret,3) from dual

DECODE(8,2,RET,3)
-----------------
3

Wachaspati Pandey
Visit me @http://www.angelfire.com/indie/wachaspati/
Previous Topic: Re: Reading next line of text file using UTL_FILE
Next Topic: Re: ORU-10028: line length overflow, limit of 255 bytes per line
Goto Forum:
  


Current Time: Wed Oct 23 05:00:24 CDT 2019