Multiple conditions on same column

building semantic layer
1

Hi,

I am developing a universe and I am new to BO. I have three objects which get the data from same column but with different where clauses.
But If I select two objects at a time then I am getting porblem as Bo generating the where clause with and statement.

My statements looks like this

select * from table where
column1=‘123’ and column1=‘234’

How can I change that and to or.

Thanks in advance.

AK

Ak (BOB member since 2003-10-09)

2

AK,

As a best practice avoid placing conditions in the Objects Where clause in the Universe since it might contradict with some other objects Where clause, which you are facing now. So the preferred way is to use CASE WHEN END statement to navigate different conditions.

Sri

Sridharan :india: (BOB member since 2002-11-08)

3

Hi Sri,

Thanks for your suggestion. But my requirement is like this.
These are three different objects

  1. User wants to see score_value where score_code =‘123’
  2. User wants to see score_value where score_code =‘312’
  3. User wants to see score_value where score_code =‘132’
    If user select more that one object for one report then I am running in to problem.
    The report requirement may be like this
  4. count of customers for 1 object thats ok
    but he wants to see count of customers for 1 and 2 objects.

I hope I made my self clear.

Thanks once again

AK

Ak (BOB member since 2003-10-09)

4

AK,

  1. Something like this should help you create the three objects.

Obj1

CASE WHEN score_code ='123' THEN score_value END

Similarly for other objects.

Now what do you mean by count. Count of Obj1 or something else. Can you put it in other words.

Sri

Sridharan :india: (BOB member since 2002-11-08)

5

AK,

Can you not build a user response into the measure?

e.g.


fact.date >= @Prompt('1. Start Date','D',,mono,free)

in the where statement would ask the user to enter a single start date to return all transactions greater than or equal to that start date.
Have a look in the help (F1) at the syntax behind the @prompt clause.
If you just want to limit the use to select one (or more) of the values that you have asked for, then I would guess that your where clause would look something like:


table.score_code IN @Prompt('Please choose score code(s)','N',{123,231,312},multi,CONSTRAINED)

This would limit the user to choosing codes 123,231 or 312 in the query.

You could then return score_code and score_value in the query and section/filter the report by score_code as required.

Regards,
Mark

6

I think having three different pre-defined conditions, rather than three objects, would suit you better. A pre-defined condition is essentially a “where clause only” object. The user selects only one score_value object (with no where clause) and then can choose one, two, or all three pre-defined conditions with the appropriate AND / OR / parentheses as needed.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

7

Ak, what database are you using and please post the full SQL of a query?

Stating the database is helpful because Oracle has a DECODE statement, which may be applicable in this case, and other databases do not. The full SQL is applicable because the right answer depends on.

Let’s say that you are doing something with invoice lines where there is a LINE_TYPE column with the values of SALE, TAX, FREIGHT. If you want a Sales Amount object, Tax object and Freight object and you are working in Oracle, then a sum of a DECODE on the LINE_TYPE may do the trick. However, if you are doing something in the query to get the total number of invoice lines of that line type, that a sum of a DECODE will overcount your results (unless you create DECODE objects for line counts for each of the line types). So, knowing the full query is helpful in deciding the best solution.

Dennis W. Disney :us: (BOB member since 2003-09-17)