Substitute AND with OR when using multiple measure condition objects

[Maiden]

Hello
I have a column in my table containing a code. Depending on this code I know witch value in the corresponding row of the corresponding column must be selected.

I have defined 2 variations on this @Select condition object.

The problem arises when I want to execute a query to select both measures because the WHERE clause will contain an AND operator, resulting in 'No data to fetch' as the @Select object cannot have both conditions apply.

If I manually change the SQL code as follows:

WHERE
( ( BLA.MIST01.MISOBJ ) = '01' )
AND ( ( BLA.MIST01.MISOBJ ) = '02' )

And select “Do not generate SQL before running” it works perfect.

The problem is that I need a lot of these measures in my query and I wonder if there is some way to make Business Objects aware it should use an OR instead of an AND in this situation.

Any ideas?
Maiden

 

[PAULSGS]

Have you thought about creating an Alias of your table?
This way you could have your two variations of an object looking at one version of the same table each. This should resolve the "ANDing" problem.

 

[Maiden]

Hello Paul

Thanks for your suggestion. It was the second voice mentioning Alias tables, so I'll try it out.

We will also resort to creating local conditions in the report itself, instead.

Rgds
M

 

[skrandel]

I'll say it again. You can't use the where statement in an object very often. They simply make it way to easy to build mutually exclusive statements.

If this were my system, I wouldn't use Aliases. What you really need is a CASE statment

You should have 2 objects

OBJ1
CASE BLA.MIST01.MISOBJ
When '01' then Column1_value
else 0

OBJ2
CASE BLA.MIST01.MISOBJ
When '02' then Column2_value
else 0

My syntax is not correct, but it should give you the idea. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com