If else possibility in universe designer

[former_member201536]

Hello

I am trying to implement If else logic in universe designer, but unfortunately I ended up getting error.

Below is my formula

If IsNull(@Select(abc\col1))

then 0

else @Select(abc\col1)

I am getting error From keyword not found where expected.

I am wondering whether if else logic possible in universe designer or it should only be implemeted in WEBI.

Thanks for your help.

Regards

Siva

[Former Member · Accepted solution]

Hi Siva  ,

We need to understand , universe supports database functions to which universe is pointing .

If you want to write logic like above ansi supported syntax will be

Original systax :-

If IsNull(@Select(abc\col1))

then 0

else @Select(abc\col1)

New syntax

case when (@Select(abc\col1)) is null then 0 else @Select(abc\col1) end

You can also handle the same using database specific finctions like NVL or coalesce() just go through available function list .

[Former Member]

Siva,

You have used report logic:

If IsNull(@Select(abc\col1))

then 0

else @Select(abc\col1)

If you are using SQL Server, then this can be written very simply as:

COALESCE(@Select(abc\col1),0)

Otherwise, use a case expression:

CASE WHEN @Select(abc\col1) is NULL THEN 0 ELSE @Select(abc\col1) END

Regards,

Mark

[former_member207052]

What is your database(MS SQL, my SQL, Oracle etc)?

Can you post the statement of your abc\col1 ?

Did you try referring to the column directly instead of referring the class object ?

Some thing like: if isnull(yourtable.yourcolumn) then 0 else yourtable.yourcolumn)

Message was edited by: Narashimman K S

[Former Member]

you can do the same using case statement.

If IsNull(@Select(abc\col1))

then 0

else @Select(abc\col1)

equal to

Case when IsNull(@Select(abc\col1)) = 1 then 0 else (@Select(abc\col1))

make sure that in result for any case you must have same data type.

[amitrathi239]

Hi,

Use NVL function for  replacing null values.

syntax like:

nvl(@Select(abc\col1),0)

Amit

[former_member201536]

Thanks Amit

Implemented your suggestion but got error Invalid Definition (UNV0023)

[amitrathi239]

What is the data type of col1?are you using UDT or IDT?Check if normal object is parsing or not?

[former_member201536]

Datatype is Number and I am using UDT...

Yes normal object without any condition is parsing..

[amitrathi239]

check in the universe->functions if you can see NVL function.

if not try with this.

IIf(isnull(@Select(abc\col1)),0,@Select(abc\col1))

Amit

[former_member201536]

Thanks Amit

Its giving me IIF is invalid identifier...any wayout..

Is if else condition possible in Universe

[amitrathi239]

check in the universe function.if you can see then you can use.

In IDT this is available not sure in UDT