J. Doe J. Doe - 6 months ago 8
SQL Question

My query returns multiple rows

I am working on this function is pl/sql where I want to return one number, the amount of expressions.

This is my function:

CREATE OR REPLACE FUNCTION media_f(
p_media in varchar2,
p_date in TIMESTAMP,
p_sentiment in varchar2)

RETURN NUMBER
IS

l_amount NUMBER;
CURSOR c_media IS
SELECT COUNT()
FROM MEDIA
WHERE MEDIA.DATES = p_date
AND MEDIA.KIND = p_media
AND MEDIA.POSITIVE_NEGATIVE = p_sentiment;


BEGIN
open c_media;
fetch c_media into l_amount;
close c_media;

RETURN l_amount;
END;


When I try to use it like this:

select Project.MEDIA_F ('tv', '06-05-13', 'n')
from MEDIA;


It return all the records from the table with either everywhere a 1 or a 0.

Answer

You are not using any columns from table in the from clause. However, the SQL engine will still return one row for each row "generated" by the from clause.

Assuming you are using Oracle, just use dual instead of a table name:

select Project.MEDIA_F('tv', '06-05-13', 'n') 
from dual;

Most other databases don't require the from clause.