| SELECT statement in DECODE [message #36921] |
Thu, 03 January 2002 23:29  |
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  |
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 |
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 |
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 |
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/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
