Query syntax error - LawsonGuru.com - LawsonGuru.com Forums -

Name	Points
Greg Moeller	4184
David Williams	3349
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244
Chris Martin	825

DavidBrianCampbell

New Member

Posts: 0

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

New Poster

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

Posts: 3366

New Poster

Engaged Reader

Avid Reader

9/14/2007 2:49 PM

Remove the comma from the end of FULLNAME.

John Henley

Posts: 3366

New Poster

Engaged Reader

Avid Reader

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!

Chris Martin

Veteran Member

Posts: 277

New Poster

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