help need on syntex of condition using Information Designer Tool 4.0

[Former Member]

I am new to BO. Here is an illustration of the needs:

1. I have a table mytab (col1, col2, col3).

2. add a custom dimension, called "mydim"

3. click on SQL assistant to bring up SQL expression editor on "mydim".

4. Here what I need to do to define the outcome of mydim:

*if value of col1 starts with 'A' then mydim=col1*

*else if col2 starts with 'B' then mydim = col2 and col1 concadnated else col3. .*

I would appreciate help on the exact syntex to achive this. Thx

[Former Member]

case when col1 like 'A%' then col1 when col2 like 'B%' then col1+col2 else col3 end

try out this option if your database in sql server. If your backend database is oracle then use || instead of +symbol in the above syntax.

one more this you need to consider is the priority of the conditions in the case statement. for example is there any possibility of getting rows with col1 values starting with A and also col2 values starting with B , in that case which one u need to display