Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL BLOCK: Subquery In Select Clause Returns ERR
PLSQL BLOCK: Subquery In Select Clause Returns ERR [message #208466] Mon, 11 December 2006 00:20 Go to next message
manith
Messages: 3
Registered: December 2006
Junior Member
Hi all,

i have written a subquery in select clause and returns me all the required values exactly..

eg:
select col1, (select count(col2) from table2 where
table2.col1 = table1.col1)
from table1;

If i am going to use the same query inside a plsql block it returns me a error..whts the reason..

eg:
declare
var number;
var1 number;
begin
select col1, (select count(col2) from table2 where
table2.col1 = table1.col1) into var,var1
from table1;
end;
Re: PLSQL BLOCK: Subquery In Select Clause Returns ERR [message #208473 is a reply to message #208466] Mon, 11 December 2006 00:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What error do you get?
What version of the database do you use?

Select INTO in a pl/sql block!

[Updated on: Mon, 11 December 2006 00:32]

Report message to a moderator

Re: PLSQL BLOCK: Subquery In Select Clause Returns ERR [message #208477 is a reply to message #208473] Mon, 11 December 2006 00:39 Go to previous messageGo to next message
manith
Messages: 3
Registered: December 2006
Junior Member
i use oracle 8.

ERR: unexpected 'select' statement when expecting count, min, max ...

Re: PLSQL BLOCK: Subquery In Select Clause Returns ERR [message #208497 is a reply to message #208477] Mon, 11 December 2006 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle 8's PL/SQL doesn't support this structure.

Would something like this, instead, do any good?
SELECT t1.col1, COUNT(t2.col2)
FROM table1 t1, table2 t2
WHERE t1.col1 = t2.col1
GROUP BY t1.col1;
Re: PLSQL BLOCK: Subquery In Select Clause Returns ERR [message #208511 is a reply to message #208497] Mon, 11 December 2006 02:45 Go to previous message
manith
Messages: 3
Registered: December 2006
Junior Member
thanks.. so ccording to your it can be done in oracle 9i.. rite..
Previous Topic: create dynamic tables
Next Topic: whats wrong in the below code.
Goto Forum:
  


Current Time: Sat Dec 10 09:20:33 CST 2016

Total time taken to generate the page: 0.23231 seconds