IF in a CASE to assign a variable

I'm trying to create name variables on the fly. I do this with firstname and lastname fine but they are required in one table or the other. With middlename, they're not required in either. I have a case statement for each of these. In the second part of the middlename case statement, I need something that says, if it's it's null then don't add a '.', otherwise add it. Everytime I add an IF statement, I get an error.
Also tried keeping the statement as is then doing another, right after that says case when a.a_mname is null then '' as middlename, but it also didn't work.

The current query is below and thanks for any assistance.

SELECT p.publication_name,p.publication_id,o.dept,o.link as author_link,
CASE WHEN o.lname is not null THEN o.lname ELSE a.a_lname END as lastname,
CASE WHEN o.mname is not null THEN o.mname || '.' ELSE a.a_mname || '.' END as middlename,
CASE WHEN o.fname is not null THEN o.fname ELSE a.a_fname END as firstname,
a.publicationa_id

FROM publications p INNER JOIN publications_authors a ON p.publication_id = a.publication_id
LEFT JOIN people o ON a.id = o.id
ORDER BY publicationa_id asc

use the nvl() function and or case, plus whats the error?

The error is "missing keyword" which tells me that my syntax is bum, which makes sense since I'm having trouble figuring how to format this statement.

I get that error when I do with IF:
CASE WHEN o.mname is not null THEN o.mname || '.' ELSE IF a.a_name is null THEN '' ELSE a.a_mname || '.' END as middlename,

It works fine without the IF in the middle. I've tried a CASE there too. I suspect either will work if I knew how to format it.

What I've read of nvl() is that it replaces a value when null is encountered. That's the opposite of what I need. If there's something there, I want to add a '.', but if it's null, i want it to stay null.

While you could use a searched CASE expression, NVL would be easier, as davey23uk already mentioned.

Code:

---

SELECT p.publication_name, p.publication_id,
      o.dept,o.link                  AS author_link,
      NVL(o.lname, a.a_lname)        AS lastname,
      NVL(o.mname, a.a_mname) || '.' AS middlename,
      NVL(o.fname, a.a_fname)        AS firstname,
      a.publicationa_id
  FROM publications p
  INNER JOIN publications_authors a
    ON p.publication_id = a.publication_id
  LEFT JOIN people o
    ON a.id            = o.id
  ORDER BY publicationa_id asc;

---

I have no problem using NVL or CASE - whichever is better is fine for me.

My issue is that I don't think it solves my problem. If something is in mname, then I want the "." when it assigns it to middlename, which this does fine - as did my version of the code. If something is NOT in mname, then I don't want the "." appended, but I still want to assign the blank to middlename.

The current version, either yours or mine, produces this:
mname with something in it (yay!): Tunes,L.B.
mname with nothing in it (boo!): Chen,L.. - too many dots at the end because it's a blank middlename

Or initialize middlename as blank would work but I've not had success initializing variables.

BTW, I really like your formatting. Very readable. I didn't figure out how to do that with this post.

When you post code look for the # sign which wraps your code with tags. If you are curious about how something is formated click on the quote button and look for the tags.

If NVL won't work the COALESCE is going to return the first non null value.

Code:

---

SELECT p.publication_name, p.publication_id,
      o.dept,o.link                  AS author_link,
      NVL(o.lname, a.a_lname)        AS lastname,
      COALESCE(o.mname || '.', a.a_mname || '.', ' ')
      AS middlename,
      NVL(o.fname, a.a_fname)        AS firstname,
      a.publicationa_id
  FROM publications p
  INNER JOIN publications_authors a
    ON p.publication_id = a.publication_id
  LEFT JOIN people o
    ON a.id            = o.id
  ORDER BY publicationa_id asc;

---