question

sqlnewb avatar image
sqlnewb asked

Create Function to return name

I am trying to create a function that given the customer_id returns the name of the sales person that works with that customer. I am getting the error not enough values on line 7. Have I put this function together correctly? CREATE or replace FUNCTION CustomerID (p_cust_id customers.customer_id%type) RETURN Varchar2 IS v_firstname employees.first_name%type; v_lastname employees.last_name%type; BEGIN SELECT e.first_name, e.last_name INTO v_firstname, v_lastname from customers c JOIN employees e ON e.employee_id = c.salesperson_id where c.customer_id = p_cust_id; RETURN(v_firstname, v_lastname); END; / show errors
oraclefunctionsoracle-sql-developer
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
I am no oracle developer, but to me it looks like you are trying to insert multiple columns into v_name The syntax for SQL server is slightly different. To assign a value into a variable you use select @v_name=e.lastname from customers.... In SQL server you use the select xx into from .. syntax to insert the result set into another table and I guess the syntax is similar in oracle. Ty to change the return type and variable into a table if you need all columns.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
I updated the code changing a few things around but still not working.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
The function can only return one value, if you need the values from two columns you need to concatenate them or return a table
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.