Oracle DECODE

  

Hi,

For a new record to be inserted on the database table, I need to concatenate First_Name, Middle_Name, First_Last _Name and Second_Last _Name to a FULL_NAME field.

For example: John A Ferguson Smith if the Middle_Name  has 'A', else
John Ferguson Smith if the Middle_Name is NULL.

Since the record is not yet on file, I have to bring those values thru parameters. I have this script but it give me error. "Database return the following error: Error in advanced query (Name of the Structure): Incorrect syntax near '|'


SELECT DECODE(@MDL_NAME,
                        NULL, @FIRST_NAME || ' ' || @FIRST_LAST_NAME || ' ' || @SECOND_LAST_NAME,
                           @FIRST_NAME
                        || ' '
                        || @MDL_NAME
                        || ' '
                        || @FIRST_LAST_NAME
                        || ' '
                        || @SECOND_LAST_NAME
                       )
from DUAL


Please let me know how to solve this.

Thank in Advance

Rooster(Edgar)

Ok, Manage to do it this way although if no Middle Name is populated it will expand one character on the FULL NAME field.

AgentForm.Record.AGNT_CONTACT_INFO.FIRST_NAME + " " + AgentForm.Record.AGNT_CONTACT_INFO.MDL_NAME + " " +AgentForm.Record.AGNT_CONTACT_INFO.FIRST_LAST_NAME + " " + AgentForm.Record.AGNT_CONTACT_INFO.SECOND_LAST_NAME