Column formatting in a CASE statement

I have a daily RMAN report that I send to myself and I wanted to color code the duration of the backup… for instance, backups taking less than 100 minutes were green, between 100 and 300 minutes, orange, over that red.

Currently, I’m using the following to format the column in the HTML output:

column “B/Up Duration in mins” ON HEADING ‘B/Up Duration!in mins’ format 999.99

Well, that works fine until I put that column into a CASE statement.  It still splits the heading on two lines but no longer gives me the number format I wanted.  Also, I needed to alias the column so the column formatting would take affect… to get around the two problems, here’s what I did.  First, I format the column within the CASE statement and then put the alias at the end of the CASE statement as seen below:


    WHEN elapsed_seconds/60 < 100 then

      ‘<span class=”threshold-ok”>’||rpad(trunc(elapsed_seconds/60,2),4,0)||'</span>’

   WHEN elapsed_seconds/60 between 100 and 300 then

       ‘<span class=”threshold-warning”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’


        ‘<span class=”threshold-critical”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’

   END as “B/Up Duration in mins”



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s