CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
iAteABug_And_iLiked_it
iAteABug_And_iLiked_it

Reputation: 3795

PL/SQL select returns more than one line

I'm embarrassed to admit this is a totally noob question - but I take shelter in the fact that I come from a T-SQL world and this is a totally new territory for me

This is a simple table I have with 4 records only

ContractorID ProjectID Cost

 1              100        1000
 2              100        800
 3              200        1005
 4              300        2000

This is my PL SQL function which should take a contractor and a project id and return number of hours ( 10 in this case )

create or replace FUNCTION GetCost(contractor_ID IN NUMBER, 
                                          project_ID in  NUMBER)
    RETURN NUMBER
IS
    ContractorCost  NUMBER;    

BEGIN
    Select Cost INTO  ContractorCost
    from Contractor_Project_Table
    where ContractorID= contractor_ID and ProjectID =project_ID ;
    return ContractorCost;
END;

But then using 

select GetCost(1,100) from Contractor_Project_Table;

This returns same row 4 times

1000
1000
1000
1000

What is wrong here? WHy is this returning 4 rows instead of 1

Thank you for

Upvotes: 0

Views: 212

Answers (2)

hkutluay
hkutluay

Reputation: 6944

Because you have 4 rows in Contractor_Project_Table table. Use this query to get one record.

select GetCost(1,100) from dual;

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231781

As @a_horse_with_no_name points out, the problem is that Contractor_Project_Table has (presumably) 4 rows so any SELECT against Contractor_Project_Table with no WHERE clause will always return 4 rows. Your function is getting called 4 times, one for each row in the table.

If you want to call the function with a single set of parameters and return a single row of data, you probably want to select from the dual table

SELECT GetCost( 1, 100 )
  FROM dual

Upvotes: 3

Related Questions