Execute SQL queries within views conditionally

[Theju112]

We had a couple of huge SQL statements (executed within an embedded sqlrpgle program) for which we created separate views - one for each SQL.

The SQL statements were being executed within the programs conditionally. That is, the program had some conditional logic in it (written in RPG) which would determine whether we would execute one query or the other.

Now what we are trying to do is to create a single view which would be an equivalent of the RPG program.

That is somehow include the conditional logic in the view code.

Below is the simplest pseudocode of the view that I could imagine of what I am trying to achieve

Code: Select all

if (condition 1 is true)
   Select * from table1;
else
   Select * from table2;

The if condition logic needs to be part of the view that I am trying to create. Is something like this possible in DB2?

Please note that the condition 1 used above is a very high level representation. The logic is actually comprised of several program calls etc.

Any advise would be appreciated.

[francesco.toppan]

Hi

first thing that comes in my mind is to develop a UDF returning a table, as long as the UDF is able to check or retrieve your condition.

if your pseudocode is

Code: Select all

if (condition 1 is true)
   Select * from View1;
else
   Select * from View2;

I am assuming that the format returned by the two views is identical, hence a UDF could work.

Once you've define a working UDF called MyFunctionT, you code would be something similar to this:

Code: Select all

select * from table (MyFunctionT(Condition1)) MyView

and depending by the condition passed, the return will be from View1 or View2