Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Query syntax error

Sort:
You are not authorized to post a reply.
Author
Messages
DavidBrianCampbell
Basic Member
Posts: 13
Basic Member
9/14/2007 1:41 PM
    Thanks in advance... I have a manually written query I'm testing to join 3 name fields into one field. Here's what I have which needs to output in a format like "Doe, John M.":

    SELECT EMPLOYEE.EMPLOYEE, ([EMPLOYEE.LAST_NAME] & ", " & [EMPLOYEE.FIRST_NAME] & [EMPLOYEE.MIDDLE_INIT] &".") AS FULLNAME,
    FROM EMPLOYEE

    I get an Oracle 936 "missing operation" message. Any clues?
    Chris Martin
    Veteran Member
    Posts: 277
    Veteran Member
    9/14/2007 2:29 PM
      Sounds like you are looking for sql query against an Oracle database. If so, try this:

      select employee,
      trim(last_name) || ', ' || trim(first_name) || ' ' ||
      case when trim(middle_init) is null then trim(middle_init) else trim(middle_init) || '.' end
      from employee
      John Henley
      Senior Member
      Posts: 3348
      Senior Member
      9/14/2007 2:49 PM
        Remove the comma from the end of FULLNAME.
        Thanks for using the LawsonGuru.com forums!
        John
        John Henley
        Senior Member
        Posts: 3348
        Senior Member
        9/14/2007 2:52 PM
          If you are entering the SQL directly into Crystal via a command, it has to be in the syntax of the database you're querying...
          The syntax you're using will work against SQL server, but not sure about Oracle (I think Chris is correct in that you need the || characters....)
          In fact, since Chris has written the code for you, you owe him lunch!
          Thanks for using the LawsonGuru.com forums!
          John
          Chris Martin
          Veteran Member
          Posts: 277
          Veteran Member
          9/14/2007 3:13 PM
            The query I indicated should work, but I did leave off the renaming of the "full name" field. This one should be cleaner:

            select employee,
            trim(last_name) || ', ' || trim(first_name) || ' ' ||
            case when trim(middle_init) is null then trim(middle_init) else trim(middle_init) || '.' end as full_name
            from employee

            You are not authorized to post a reply.