Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Creating a funtion to change name format.

    Click image for larger version. 

Name:	ERD.png 
Views:	23 
Size:	84.6 KB 
ID:	11743

    I have to write a function utilizing emp_fname, emp_initial, and emp_lname from the employee table of diagram. The object of the function is to change the format of the names as such; if they have a middle initial John P. Smith if they don't have a middle initial John Smith.

    I'm pretty new to SQL so I was just looking for a little insight on the best way to go about this. This is what I have so far, been kind of working from the bottom up.

    create or replace function Format_Emp_Name(
    p_fname hartmar.employee.emp_fname%TYPE
    p_initial hartmar.employee.initial%TYPE,
    p_lname hartmar.employee.lname%TYPE)
    return varchar
    v_emp_name char;
    BEGIN
    SELECT
    employee.emp_fname,
    employee.initial,
    employee.emp_lname
    INTO v_emp_name
    FROM hartmar.employee
    RETURN v_emp_name
    EXCEPTION
    when no_data_found then
    dbms_output.put_line('Invalid name');
    when others then
    dbms_output.put_line(SQLERROR);
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have to write a function utilizing emp_fname, emp_initial, and emp_lname from the employee table of diagram.
    I, for one, find it easier to DDL (CREATE TABLE) statements; than shrunk & fuzzy image files.
    >The object of the function is to change the format of the names as such; if they have a middle initial John P. Smith if they don't have a middle initial John Smith.
    Change from what to what?
    Please provide specific examples.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2011
    Posts
    4
    This function will format the employee's name in the following format:
    if the employee has a middle initial: John P. Smith
    If the employee does not have a middle initial: John Smith

    Those were my instructions.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    concatenate the columns together
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    4
    Do you just add that into the select statement within the function?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    the SELECT statement has no WHERE clause so will error out when more than 1 row exists in table

    what exactly is being passed into this function?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2011
    Posts
    4
    CREATE OR REPLACE function Format_Emp_Name
    (p_fname hartmar.employee.emp_fname%TYPE
    p_initial hartmar.employee.initial%TYPE
    p_lname hartmar.employee.emp_lname%TYPE)
    --
    RETURN varchar2;
    --
    IS
    --
    v_emp_name varchar2;
    --
    BEGIN
    --
    COLUMN emp_fname -
    heading'Employee Name'
    SELECT
    (employee.emp_fname || ' ' || employee.initial || ' ' ||employee.emp_lname)Empl\
    oyee Name
    --
    INTO v_emp_name
    --
    FROM hartmar.employee;
    --
    RETURN v_emp_name;
    --
    EXCEPTION
    when no_data_found then
    dbms_output.put_line('Invalid name');
    when others then
    dbms_output.put_line('Invalid name');
    when others then
    dbms_output.put_line(SQLERROR);
    --
    END;
    /




    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/1 PLS-00103: Encountered the symbol "P_INITIAL" when expecting one
    of the following:
    := ) , default character
    The symbol "," was substituted for "P_INITIAL" to continue.

    4/1 PLS-00103: Encountered the symbol "P_LNAME" when expecting one of
    the following:
    := ) , default character
    The symbol "," was substituted for "P_LNAME" to continue.

    8/1 PLS-00103: Encountered the symbol "IS"

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/8 PLS-00103: Encountered the symbol "EMP_FNAME" when expecting one
    of the following:
    := . ( @ % ;


    That's the current syntax and errors I'm receiving.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is PL/SQL syntax you are unfamiliar with. There are just too many errors in such a short function; it is useless to write programs using your technique, so - perhaps you should first read some documentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •