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
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.
The function can only return one value, if you need the values from two columns you need to concatenate them or return a table
