• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Query syntax error

 4 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
DavidBrianCampbell
New Member Send Private Message
Posts: 0
New 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 Send Private Message
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
Send Private Message
Posts: 3351
9/14/2007 2:49 PM
Remove the comma from the end of FULLNAME.
Thanks for using the LawsonGuru.com forums!
John
John Henley
Send Private Message
Posts: 3351
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 Send Private Message
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