IF in a CASE to assign a variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: IF in a CASE to assign a variable

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    use the nvl() function and or case, plus whats the error?

  3. #3
    Join Date
    Sep 2004
    Posts
    51
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    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;
    this space intentionally left blank

  5. #5
    Join Date
    Sep 2004
    Posts
    51
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    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;
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width