Recently I decided to try out the new patching capabilities for the metadata in OBIEE 11g. The big reason for me was to avoid having to reset my connection pools. Each time I did a 3 way merge, the connection pools would get over-written with their development credentials. When I was doing the merge, I could never find a way to isolate (leave out) those objects, so it was off to try the patching.

What this patching does is creates an xml file of the differences between 2 RPDs. You can then edit that XML file if you so desire. Most of my duties over the last couple of years have centered around the RPD and front-end stuff, not nearly as much on the administrative side (i.e. migrations). So I need to catch up with the rest of the world.

Reading through the docs, I'm told that I need to use the "Compare repositories" dialog. OK, easy enough.



Where is my entry for it?

OK, let's try the Compare entry.



Since I'm using the prod_20120122 RPD, I select the dev_20120122 RPD.

I'm prompted for the Repository Password and then it spins for a few seconds, then gives me this



I select Yes.



OK...where's the Compare Repositories Dialog like the docs say?

I see the icons have changed signifying differences, but no dialog as mentioned in the docs.

What if I select No?



Ah, there it is.

Interestingly, if I maximize that Compare Repositories Dialog, the next time I run the Compare, I can see it plain as day.



Hopefully you'll find this next time you endeavor to learn how to patch your RPD and can't seem to find the Compare Repositories Dialog.

Five.

Six.

Another year, which always amazes us.

After suffering from analysis paralysis, we were put in touch with someone who helped get us unstuck. She told us how her school worked and the kind of treatment that Kate should be getting. When we told her Kate was getting an hour a month of speech, in a group, she almost cried. Her students got 25 hours a week, all year around, of ABA therapy. Kate was getting 1 or 2 a month. WTF?

With that, we moved her to a new school in October, Sydney's School, Specializing in Autism and Related Disabilities.

Despite the finances of the place (which so far, haven't been as terrible as I predicted...fingers crossed), we knew it was good for her within a week.

You see, we have stairs. Kate can't do stairs. We've been working with her for years to walk up the stairs. She can crawl up them, no problem, but she can't get down. I did manage to teach her to slide on her butt a bit, but she just wouldn't do it. Then Kelly, her caretaker started to work with her constantly on going up and down the stairs, upright. She still wouldn't do it, unprompted.

So Kris goes to pick up Kate near the end of that first week and asks her teacher how she's handled the stairs. "Fine, no problems."

Kris: "What?"

Teacher: "She goes up and down just fine. She needs a little assistance, but she mostly does it on her own."

Kris (thinking): "That little shit."

The last 2 months at Sydney's School have been amazing. We're finally seeing real progress. Can't wait to see what a year of this kind of therapy will do for her.

That's definitely been the highlight of our year. Now she's on to the 7th year, something we never thought we'd see. Kate's tough as ever, smart as ever and still happy as ever.

I haven't seen too many (ok, none) of these this year, but one today hit home:

Predicting OBIEE 11.1.1.6 by Joe Leva.

I've been lucky enough to hang out (err, stalk, as he would say) with Joe in the recent past, he's a smart dude. Here's his prediction in short:


Now wouldn't that be cool, an OBIEE machine a la Exadata or Exalogic? I'll take 1, or 2.

Do you have any predictions for what will be announced this year? FMW will be big I'm sure. There's a big announcement on September 21st (Wednesday), maybe it's the Exadata Mini(-me)?

Let's hear some more fun predictions...

SQL> SELECT TO_DATE( 20111001, 'YYYYMMDD' ) - SYSDATE time_to_oow
  2  FROM dual;

TIME_TO_OOW
-----------
 15.8921065

Wow. Just a little over 2 weeks to go. Time certainly flies.

As I'm wont to do, I've put off just about everything. For the last 2 weeks I've been scrambling to get a room. Two days ago I looked at the prices in downtown San Francisco and I thought I might be living sleeping on someone's floor, or sleeping on the street (don't think I haven't done that). It was even more serious because I had promised a friend of mine that I would cover his accommodations; he had helped me out last year when Kate got sick by buying me a ticket to get home immediately (end of the month, I was out of funds).

Yesterday I finally scored a place through airbnb. Somewhere on Lombard street, Russian Hill? Whatever. It's 1.7 miles to the Moscone Center.



In San Francisco, 1.7 miles is nothing. Far too many great distractions (people watching) for it to feel like a long walk. Bonus points for staying (getting) in shape while there. Bonus points for helping the effects of the beer wear off before sleep. Negative bonus points if I get lost, which I will, especially if I am alone. No sherpa this year unfortunately.

As soon as I booked my room, I booked my flight. They're still cheap too.

I haven't even looked at the sessions yet. Shocking.

Lots of activities planned though:

- Sunday - Oracle ACE dinner thingy.
- Monday - Customer meeting and OTN party.
- Tuesday - Nothing yet.
- Wednesday - Blogger meet-up, Appreciation Event
- Thursday - Nothing
- Friday - OBIEE Meetup thingy with product development.

Somewhere in there I'm supposed to fight with Kellyn Pot'Vin. Additional fun will be a repeat of last year's game, The Piwowar challenge. Personally, I like Pot' Wen(ch), but that might get me punched again.

I've been throwing a little bit too. They have this Home Run Derby...area, where I can go and throw my arm out trying to see how hard I can throw after 10 years. I may be in a sling after the first day.

It's gonna be a hoot. So excited to hang out with a bunch of smart, passionate people and talk shop (or beer). I don't think I've left my house in weeks, I really need to get out.

For the past couple of years George Woods has been compiling a list of Twitterites who will be attending Oracle OpenWord. 2009 and 2010. I realized this year I hadn't seen it yet, so I emailed him. Unfortunately he will be unable to attend, he's working with that other database right now.

There is no planned event for us yet, but it can't hurt to have if something does come up.

If you plan on attending, please fill out this simple form; twitter handle is the only thing required.

Loading...

You can find the document here. If I can figure out how to embed the list here, I will do so.

OK, I just stole the iframe method from the above form. It's ugly, but it's better than nothing.

Loading...

If you know of a better (and easy) way, let me know.

I'm in the process of building out a tip-to-tail dev/test system that includes the components from OBIA.

The components include:
- EBS Vision database
- Informatica PowerCenter 9.0.1
- Data Warehouse Administration Console (DAC)
- OBIEE 11.1.1.5
- OBIA 7.9.6.3

I recently rebuilt my EBS instance. I put my last go at it on a removable disk and seem to have misplaced it.

This will be part of a series of posts describing the entire process. I don't do it often enough and so I have to "remember" what I did, now I'll have my own reference.

Environment

As usual, this is running in a VirtualBox virtual machine.
- Host: Ubuntu 11.04, Natty Narwhal
- VirtualBox: 4.1.2
- Guest: Oracle Enterprise Linux (64 bit)

Linux oracle-web-tier 2.6.32-200.13.1.el5uek #1 SMP
x86_64 x86_64 x86_64 GNU/Linux

- Installed software: 11gR2 Database, OBIEE 11.1.1.5, Oracle Web Tier

I grabbed the download from edelivery, file name is: V26109-01.zip.

Other references:

- OBIA 7.9.6.3 Documentation Library
- OBIA Installation Guide for Informatica PowerCenter Users Release 7.9.6.3: Specifically this section, Installing Informatica PowerCenter Services.

Step 1, create a database account.

CREATE USER infa IDENTIFIED BY testing
  DEFAULT TABLESPACE users;

GRANT DBA TO infa;

**Note I have given DBA privileges, this is not necessary.**

It is simply easier, for now, to do it this way.

Step 2, unzip the files on your system.

It is recommended that you create an OS user specific to this task. I am using my previously existing oracle (dba, oinstall) account

Step 3, run the install.sh file

[oracle@oracle-web-tier infa_zips]$ ./install.sh
OS detected is Linux
unjar task is in progress.............
unjar of ESD completed.....
Do you want to continue installation (y/n) ?
y
Starting installation ...

\***************************************************************************
\* Welcome to the Informatica 9.0.1 HotFix 2 Server Installer.  *
\***************************************************************************


To verify whether a machine meets the system requirements for an Informatica installation, 
run the Pre-Installation System Check Tool (i9Pi) before you start the installation process. 
You can find the i9Pi tool in the following directory: /i9Pi


Before you continue, read the 9.0.1 HotFix 2 Installation Guide and Release Notes.
You can find the 9.0.1 HotFix 2 documentation in the Documentation Center at 
http://my.informatica.com
Configure the LANG and LC_ALL variables to generate appropriate code pages and 
create and connect to repositories and Repository Services.
Do you want to continue? (Y/N)Y
Installer requires operating system Linux version 2.6 and later.
Current operating system Linux version 2.6.
Current operating system meets minimum requirements.

 Select a choice :  
1.   Install Informatica 9.0.1 with Hot Fix 2 or Upgrade from a previous version of Informatica 
to Informatica 9.0.1 with Hot Fix 2 
2.   Apply Informatica 9.0.1 Hot Fix 2 to existing 9.0.1 install.
Enter the choice(1 or 2):1

-----------------------------------------------------------
Checking for existing 9.0.1 HotFix 2 product installation.
Select (G)UI mode (needs X Window support) or (C)onsole mode (G/C):G
Launching installer in GUI mode ...
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

Preparing SILENT Mode Installation...

===============================================================================
Informatica 9.0.1 Services HotFix2               (created with InstallAnywhere)
-------------------------------------------------------------------------------

System Requirements:



License Key and installation directory:



Confirmation:



Running...



Configuring...



Create a domain and Enable HTTPS. Enable HTTPS is checked by default, I won't be using it.



Configure the database connection and JDBC URL



Testing the connection...



Informatica Domain. I'm going to accept the defaults:



Summary

===============================================================================
Configuring Installation...
---------------------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]

Installation Complete.

To verify your installation, open up http://localhost:6007/administrator/, username is administrator and the password is the one you supplied during installation.



You'll be redirected to this page.



Next steps will be to cover the Information Repository Service and Informatica Integration Service. That of couse, is later.

Previously I wrote about how to start and stop individual components via Enterprise Manager.

This time, I'm going to run through the manual steps to do the same, start and stop individual components using the Oracle Process Manager and Notification Server (OPMN) Tool.

First, navigate to the ORACLE_INSTANCE/bin directory. For me on Linux, that is /obiee/Middleware/instances/instance. List out the directory contents and you should see the opmnctl

[oracle@oracle-web-tier bin]$ ls -lah
total 56K
drwx------  3 oracle dba 4.0K Aug 16 00:53 .
drwx------ 14 oracle dba 4.0K Sep  8 17:09 ..
drwxr-x---  2 oracle dba 4.0K Aug 16 00:53 essbase_ha
-rwx------  1 oracle dba  44K Aug 16 00:53 opmnctl

Let's see what is running:

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    1525 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    1443 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    1487 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    1469 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |   30698 | Alive

All of the components are running. Good. Let's shut down everything.

[oracle@oracle-web-tier bin]$ ./opmnctl shutdown
[oracle@oracle-web-tier bin]$ ./opmnctl status
opmnctl status: opmn is not running.

And bring everything back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startall
opmnctl startall: starting opmn and all managed processes...
[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |    3122 | Alive

Now, let's stop the BI Server, coreapplication_obis1 or OrcleBIServerCom~. There are 2 ways to bring this down. Well, one command, stopproc, but 2 different ways. Notice the column headers up above, you have ias-component and process-type. Using ias-component:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc ias-component=coreapplication_obis1
opmnctl stopproc: stopping opmn managed processes...
[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |     N/A | Down

Start it back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startproc ias-component=coreapplication_obis1
opmnctl startproc: starting opmn managed processes...
[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |    3525 | Alive 

process-type shutdown:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc process-type=OracleBIServerComponent
opmnctl stopproc: stopping opmn managed processes...
[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |     N/A | Down

Note that I didn't use OracleBIServerCom~. It expects the full name of the component, in this case, OracleBIServerComponent. If you use the shortened name, you'll get this:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc process-type=OracleBIServerCom~
opmnctl stopproc: stopping opmn managed processes...
================================================================================
opmn id=oracle-web-tier:9501
    No processes or applications match the specified configuration.

Finally, bring the BI Server back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startproc process-type=OracleBIServerComponent
opmnctl startproc: starting opmn managed processes...
[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status  
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive   
coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive   
coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive   
coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive   
coreapplication_obis1            | OracleBIServerCom~ |    3803 | Alive

Fairly simple.

For a full list of opmnctl commands, simply run ./opmnctl help and you'll get the following output:

[oracle@oracle-web-tier bin]$ ./opmnctl help

usage: opmnctl [verbose] [<scope>] <command> [<options>]

verbose: print detailed execution message if available

Permitted <scope>/<command>/<options> combinations are:

 scope    command     options
-------  ---------   ---------
          start                         - Start opmn
          startall                      - Start opmn & all managed processes
          stopall                       - Stop opmn & all managed processes
          shutdown                      - Shutdown opmn & all managed processes
[<scope>] startproc   [<attr>=<val> ..] - Start opmn managed processes
[<scope>] restartproc [<attr>=<val> ..] - Restart opmn managed processes
[<scope>] stopproc    [<attr>=<val> ..] - Stop opmn managed processes
[<scope>] reload                        - Trigger opmn to reread opmn.xml
[<scope>] status      [<options>]       - Get managed process status
[<scope>] metric      [<attr>=<val> ..] - Get DMS metrics for managed processes
[<scope>] dmsdump     [<dmsargs>]       - Get DMS metrics for opmn
[<scope>] debug       [<attr>=<val> ..] - Display opmn server debug information
[<scope>] set         [<attr>=<val> ..] - Set opmn log parameters
[<scope>] query       [<attr>=<val>]    - Query opmn log parameters
          launch      [<attr>=<val> ..] - Launch a configured target process
          phantom     [<attr>=<val> ..] - Register phantom processes
          ping        [<max-retry>]     - Ping local opmn
          validate    [<filename>]      - Validate the given opmn xml file
          help                          - Print brief usage description
          usage       [<command>]       - Print detailed usage description
          createinstance                - Create an Oracle Instance
          createcomponent               - Create a specified component
          deleteinstance                - Delete an instance and components
          deletecomponent               - Delete a specified component
          registerinstance              - Register with admin server
          redeploy                      - Redeploy the admin server application
          unregisterinstance            - Unregister with admin server
          updateinstanceregistration    - Update instance registration
          updatecomponentregistration   - Update component registration

The very first thing I thought, after firing up my first OBIEE 11g instance, was how freaking intimidating it was.

You have Enterprise Manager, the WLS Console and regular old /analytics. Nah...not too much.

Whatever.

Of course I'm still learning it daily, but most of the basics I have down now.

A basic concept which was super-easy in 10g, like stopping and starting individual services like the BI Server and Presentation Server, didn't seem so easy any more.

Now this isn't rocket science, but I'm sure it will help someone new (and intimidated!) by OBIEE 11g.

First, using Enterprise Manager.

After logging in, you'll see the Farm_bifoundation_domain (essentially your home) page:



From there, navigate to the Business Intelligence folder and click on coreapplication:



This will take you to your coreapplication (BI) page:



From there, you can stop all components of the BI Server; BI Server, Presentation, Java Host, etc.



Easy.

Sometimes though, you just need to restart one of the services, usually the BI Server or the Presentation Server.

Easy enough, see that tab Capacity Management, click on it:



That'll take you here, where you can start, stop and restart individual services:



Simply select the service you want to stop, start or restart like so:



Then select Stop Selected and you'll be prompted to confirm your selection:



Now you've stopped the BI Server service.



You can confirm this by looking at the Overview page as well:



There is also a manual way of doing this. Since it's Friday evening, I will wait until later to write that one up. Must go watch Megamind or some other fun kids movie with one of the monsters.

My ode to Joel Kallman.

Last week I wrote up an issue with Session variables, you can read it here.

That was part of my project to use an Oracle pipelined function as a table source.

Initially, I was only accepting 4 parameters, but I had never gotten it to work perfectly.

Now I have 14 to work with and a deadline fast approaching.

Here's the function call:

foo

  ( p_01 IN VARCHAR2,

    p_02 IN VARCHAR2 DEFAULT NULL,

    p_03 IN VARCHAR2 DEFAULT NULL,

    p_04 IN VARCHAR2 DEFAULT NULL,

    p_05 IN VARCHAR2 DEFAULT NULL,

    p_06 IN VARCHAR2 DEFAULT NULL,

    p_07 IN VARCHAR2 DEFAULT NULL,

    p_08 IN VARCHAR2 DEFAULT NULL,

    p_09 IN VARCHAR2 DEFAULT NULL,

    p_10 IN VARCHAR2 DEFAULT NULL,

    p_11 IN VARCHAR2 DEFAULT NULL,

    p_12 IN VARCHAR2 DEFAULT NULL,

    p_13 IN DATE DEFAULT NULL,

    p_14 IN NUMBER DEFAULT NULL ) 

  RETURN my_object_table PIPELINED;

Here's the table source:

SELECT *

FROM TABLE( FOO( 'VALUEOF(NQ_SESSION.S_P_01)', 'VALUEOF(NQ_SESSION.S_P_02)',

'VALUEOF(NQ_SESSION.S_P_03)', 'VALUEOF(NQ_SESSION.S_P_04)',

'VALUEOF(NQ_SESSION.S_P_05)', 'VALUEOF(NQ_SESSION.S_P_06)',

'VALUEOF(NQ_SESSION.S_P_07)', 'VALUEOF(NQ_SESSION.S_P_08)',

'VALUEOF(NQ_SESSION.S_P_09)', 'VALUEOF(NQ_SESSION.S_P_10)', 

'VALUEOF(NQ_SESSION.S_P_11)', 'VALUEOF(NQ_SESSION.S_P_12)', 

'VALUEOF(NQ_SESSION.S_P_13)', 'VALUEOF(NQ_SESSION.S_P_14)' )

Note that everything is enclosed in single quotes.

On the report side, I navigate to the Advanced tab



In the Prefix box



I add the following (you'll have to assume that I have created my prompts and presentation variables already, I'm too lazy to go in and create them again).

SET VARIABLE S_P_01='@{P_S_P_01}',S_P_02='@{P_S_P_02}',S_P_03='@{P_S_P_03}',

S_P_04='@{P_S_P_04}',S_P_05='@{P_S_P_05}',S_P_06='@{P_S_P_06}',S_P_07='@{P_S_P_07}',

S_P_08='@{P_S_P_08}',S_P_09='@{P_S_P_09}',S_P_10='@{P_S_P_10}',S_P_11='@{P_S_P_11}',

S_P_12='@{P_S_P_12}',S_P_13='@{P_S_P_13}',S_P_14='@{P_S_P_14}';

(I put hard returns in there for display purposes, remove those).

All is well.

Or not.

I enter in a value in Prompt 1, P_S_P_01, and nothing for the rest. Take a look at the physical SQL:

SELECT *

FROM TABLE( FOO( 'some value', '@{P_S_P_02}',

'@{P_S_P_03}', '@{P_S_P_04}',

'@{P_S_P_05}', '@{P_S_P_06}',

'@{P_S_P_07}', '@{P_S_P_08}',

'@{P_S_P_09}', '@{P_S_P_10}', 

'@{P_S_P_11}', '@{P_S_P_12}', 

'@{P_S_P_13}', '@{P_S_P_14}' )

Yeah, that's not going to work. Off to Gerard Nico's excellent wiki. There I see you can have a default value for the presentation variable.

@{variables.<variableName>}{<default>}[format]

Excellent. At the end of each session variable assignment, I tack on {NULL}. That didn't work either. That just put NULL in single quotes, like this: 'NULL'. Barnacles.

I then asked for help. Frank suggested wrapping each call to the session variable in the SQL statement in a CASE statement (I swear I had tried that...), so I did...and it worked. Here's my final SQL (Table Source):

SELECT *

FROM TABLE( FOO( 'VALUEOF(NQ_SESSION.S_P_01)',

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_01)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_01)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_02)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_02)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_03)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_03)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_04)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_04)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_05)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_05)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_06)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_06)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_07)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_07)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_08)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_08)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_09)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_09)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_10)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_10)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_11)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_11)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_12)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_12)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_13)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_13)' END ),

( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_14)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_14)' END ) ) )

Absolutely hideous! And annoying. But it works.

Hopefully someone (hint hint) will chime in with a better method.