Oracle – Replacing Row Results in Oracle 11g R2

oracleoracle-11g-r2ssrsvpd

I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential student, the database needs to essentially return nothing. Currently, we do something along the lines of:

select
  case 
    when CONFIDENTIALITY_IND = 'Y' or :EmployeeClass = 'XA'
      then 'CONFIDENTIAL'
    else db.table.name
  end as name
from
  db.student

This isn't a big deal for such a small query, but most of them return many fields (often 20+) and I'm not a fan of so many case statements for obvious reasons. Because of how Reporting Services work, I can't do a general replacement either, it must be done on each and every field.

Is there a more efficient method where I could replace every value in the row with 'Confidential' in a single case statement, or at least something more elegant?

Edit: To clarify, that there isn't just this one field in the select. I only wrote one for the example, but in production, some reports are looking at displaying a huge amount of columns. I'm trying to avoid making two comparisons on every column for reports that could return rather large sets of data.

Best Answer

what you can do, is to write two stored procedures with the following logic:

  1. Build a stored procedure which does the case thing. This may be optional but the resulting sql would look better and easier to debug/develop.
  2. Define a stored procedure which has as input the tablename
  3. Read out the columns of the table - maybe here can be done some grouping/distinct which columns fall into the case CONFIDENTIALITY_IND
  4. Build up the sql statement dynamically by iterating over the result of 3. When the column is your special case column - apply the stored procedure of 1. Otherwise select it. How to build up dynamic sql look here: http://www.dba-oracle.com/t_oracle_execute_immediate.htm - execute immediate is your friend here.

Hope I understood your problem correct. This would be my way

Related Question