SQL - IIF clause outside of select statement?

Question

I'm working in MS Access 2010 doing a series of UNIONs inside of a SELECT statement. After all of these UNIONs are complete, I need to add a couple of new columns using IIF statements (i.e., based on the value of a ch column). However, I can't seem to find out how or where to properly squeeze in this syntax so that the IIFs check against an entire column in the final 'unioned' table. Simply put, after the below syntax (which is trimmed and simplified for this question) runs, I want to then append a new variable column (e.g., 'asterisk') in this final table based on whether an existing column cell (e.g., 'grades') contains an asterisk. Help and patience is greatly appreciated -- I am just becoming familiar with the syntax principles of SQL.

SELECT * FROM(
      SELECT class, teacher, student
      grades2010 AS grades
      WHERE NOT(grades2010 IS NULL OR grades2010="")
      UNION
      SELECT class, teacher, student
      grades2011 AS grades
      WHERE NOT(grades2011 IS NULL OR grades2011="")
      UNION
      SELECT class, teacher, student
          grades2012 AS grades
          WHERE NOT(grades2012 IS NULL OR grades2010="")
)

Here's an example of the type of IIF I'd want to run -- see if 'grades' contains an asterisk and create an indicator:

IIF(InStr([grades],"*")>0,"YES","NO") AS asterisk

JNevill JNevill · Accepted Answer · 2016-02-23T21:11:47

So... You are on the right track. It would look something like:

SELECT 
     IIF(InStr([grades],"*")>0,"YES","NO") AS asterisk,
     class,
     teacher,
     student
FROM(
      SELECT class, teacher, student
      grades2010 AS grades
      WHERE NOT(grades2010 IS NULL OR grades2010="")
      UNION
      SELECT class, teacher, student
      grades2011 AS grades
      WHERE NOT(grades2011 IS NULL OR grades2011="")
      UNION
      SELECT class, teacher, student
          grades2012 AS grades
          WHERE NOT(grades2012 IS NULL OR grades2010="")
) as mysubquery

But... grades is not a column in your UNION subquery, so this won't work. You'll need to make sure that grade column is added to each SELECT statement in your subquery to do anything with it in your main SELECT.

Also worth mentioning is that your schema isn't the best. Instead of having a table for each year, you should really just have a single table with year as a column. This will get you out of having to do these nasty, and often times slow Union queries. You could fix that up pretty quick by making a table and using that UNION query there to do an INSERT from each of your year based tables.

SQL - IIF clause outside of select statement?

1 Answers