Syntax for using the if else condition inside custom query in sap bo webi

[former_member188628]

Hi all

      I have to use a custom sql in one of the report using the if else condition in where clause.Usage of else is throwing the error and if i use the ternary operator syntax(using semicolons) ,its throwing the incorrect syntax error.Please help me resolve this

[former_member199945]

Hi,

Instead of writing custom script you can create variable for the same at report level .

Thanks

[former_member188628]

Hi Seshu

But I have to use custom sql only

[former_member199945]

Have you tried by replacing len value of custodain  '3' as 3 ? 7 also is there any specific reason using custom sql . why because it is not recommended always .

Take the cln length object in query filter and aplly  filter as >3  now see sql code how it was  generated for this
and try to define same

If it is ameasure /string first make sure if it is measure then try as above.

Thanks

[Former Member]

Hi,

Your SQL looks a bit strange.

You have used ";" in the IF statement and I don't know any database supporting the ";" as a separator in a IF statement.

I assume that you are using SQL Server.

Try to use IIF but don't use IF.

Which where clause do you want to create?

Didier

[Former Member]

That's what I suggested in my earlier post get rid of the single quotes. I am not sure if this has been tried yet

[former_member188628]

Hi

I have removed the quotes and tried but still the same error

[former_member188628]

Hi Didier

       I am using the Case stmt inside the where  clause now.

Moumita

[Former Member]

Moumita,

If you copy and paste the SQL in SQL Analyzer and execute, do you get any error message?

If yes, you can check with backend resources and fix it.

Regards,

Yuvraj

[Former Member]

Hi,

You cannot put condition as the output of then in a case stament.

If your requirement is to filter name fields whose value is null or whose length is either less than 3 or greater 35

then try below

length(name) ❤️ or length(name)>35 or name is null

You cannot compare null with equal to operator this will not give null instead gives the data containing string as null.

there is no need to keep the case statement to check for nulls i.e name<>'' then only get the length of the name field and compare it with some numbers.

because length('') gives null and it will fail for both ❤️ and >35 condition

Hope i have not confused you

[Former Member]

Hi Moumita, Did you try whatever i suggested?

[former_member188628]

Thanks Divya, yes that problem was sorted, sorry for my late response:)

[Former Member]

Hi Moumita,

As far as my understanding goes, IF THEN ELSE statement will not work in simple SQL.

You will have to use CASE WHEN ELSE as a substitute. Please try and check.

Regards,

Yuvraj

[former_member188628]

Hi Yuvraj

      The in immediate pic above i have used case only in custom sql ,but its throwing error , and I do have one more doubt : actually there is length() function present not len(),but when i used length() it throws error that internally its not recognized funtion, however the len() function is accepted without errors.Wondering how

[Former Member]

Hi Moumita,

Len() function will return a numeric value; however, you are comparing it with values in single codes i.e. string.

Provide the values without single qoutes and check.

Regards,

Yuvraj

[Former Member]

Hello Moumita,

What is your requirement exactly may be we can tweak the logic in another way to get this done?

Regards

Niraj

[former_member188628]

Hi Niraj

       The condition is simple if the name1 is null then check for the rule that it should be ❤️ and >35

else display null values i tried with case and isnull but getting error

[Former Member]

Hello Moumita,

I see in the screen shot that you have compared the numbers within quotes '3' and '35'. Could you please remove that and use it like 3 and 35 and give it a shot?

Regards

Niraj

[Former Member]

Hi Moumita,

If I understood correctly, you want to substitute name1 as null for the following situations:

1. when name1 is blank

2. when name1 is less than 3 characters

3. when name1 is greater than 35 characters

then the following syntax should be part of select in your SQL:

case

when len(dbo.stg_cmdb_clnsd.ln1_name) < 3

then 'NULL'

--then NULL

when len(dbo.stg_cmdb_clnsd.ln1_name) > 35

then 'NULL'

--then NULL

else

dbo.stg_cmdb_clnsd.ln1_name

end

Also, please note, that 'NULL' will actually show the word "NULL" in the output, to get a blank use NULL (without quotes).

Just for your info, the correct syntax of case in where clause is as follows:

where <table>.<column> =

case

     when <condition1>

          then <calculated output 2>

     when <condition2>

          then <calculated output 2>

     else  <calculated output 3>

end

Regards,

Goutam

[former_member188628]

Thanks a lot Gotham , that problem is sorted:)

[Former Member]

Hi,

try using length instead of len,

Regards,

Rogerio

[former_member188628]

Hi Rogerio

Thanks for the reply ,but the problem is the same: