SQL function - Returning value for number of records in table

Question

I am putting a bit of SQL into an Oracle script, if I run the Vanilla SQL i get the correct output of one single returned value/record. However in my custom function I get the value I am looking for returned as many times as there are records. Here is an example of what I have.

create function EXAMPLE_FUNCTION (passedID in NUMBER)
return NUMBER
IS 
returnValue NUMBER;
BEGIN
SELECT "TABLE1"."ID" INTO returnValue
FROM "TABLE1" WHERE "TABLE1"."ID" = passedID;
RETURN  returnValue;
END;

So if TABLE1 has 20 records I will get the record with ID 1 returned 20 times, I am not sure where its going wrong, but I'm sure its simple!

Can you further clarify what you are wanting to do? What result are you expecting? How are you calling this function? — Craig
– Craig, Commented Apr 26, 2011 at 17:23
If you're getting 20 return values, you must be calling the function 20 times. Show us how you are calling it. — Dave Costa
– Dave Costa, Commented Apr 26, 2011 at 17:49

Clodoaldo Neto · Accepted Answer · 2011-04-26 18:37:30Z

3

You are probably calling the function like this:

select EXAMPLE_FUNCTION (1)
from my_table

Call it without the from:

select EXAMPLE_FUNCTION (1)

EDIT:

As pointed by Shannon Oracle requires the from clause so I searched and found examples using the dual table:

select EXAMPLE_FUNCTION (1)
from dual

edited Apr 26, 2011 at 18:37

answered Apr 26, 2011 at 18:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

henry.oswald Over a year ago

This makes lots of sense, however without the from I get "ORA-00923: FROM keyword not found where expected"

Shannon Severance Over a year ago

-1 Oracle always requires a from clause for a select statement.

Clodoaldo Neto Over a year ago

Edited with a hint from searches

Shannon Severance Over a year ago

Per edit, removed down vote. @Beck: About the dual table see download.oracle.com/docs/cd/E11882_01/server.112/e17118/…

Snipe656 · Accepted Answer · 2011-04-26 18:35:05Z

2

Just do something like: SELECT EXAMPLE_FUNCTION(1) FROM dual;

answered Apr 26, 2011 at 18:35

Snipe656

8457 silver badges15 bronze badges

Comments

thursdaysgeek · Accepted Answer · 2011-04-26 17:30:45Z

1

Would something like the below work in your function?

Select COUNT(*) into returnValue 
from TABLE1 
where Table1.ID = passedID;

answered Apr 26, 2011 at 17:30

thursdaysgeek

7,99621 gold badges82 silver badges119 bronze badges

2 Comments

henry.oswald Over a year ago

It runs but I get the same 'problem', I am fairly certain Kakao is right.

thursdaysgeek Over a year ago

Oh, the way you call it you got 20 entries, not that the inside of the function has 20 return values. I didn't understand the question. This code would make the function easier to understand, and Kakao's way of calling it will give you the answer once.

APC · Accepted Answer · 2011-04-26 17:50:00Z

What value are you using for passedID? If you want to get soemthing different out for each row you need to pass something different in.

Compare these two function calls. First a fixed value:

SQL> select example_function(1) from table1
  2  /

EXAMPLE_FUNCTION(1)
-------------------
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1
                  1

20 rows selected.

SQL>

Then using the table column to supply the parameter:

SQL> select example_function(id) from table1
  2  /

EXAMPLE_FUNCTION(ID)
--------------------
                   1
                   2
                   3
                   4
                   5
                   6
                   7
                   8
                   9
                  10
                  11
                  12
                  13
                  14
                  15
                  16
                  17
                  18
                  19
                  20

20 rows selected.

SQL>

Collectives™ on Stack Overflow

SQL function - Returning value for number of records in table

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related