Case statement in webi report throwing error

[BIKAS]

Hello Frnz,

SAP BusinessObjects - Web Intelligence (WebI) 

I am trying to modify a webi report and adding a @prompt condition to it, but its throwing invalid relational operator error. Any suggestions please. Adding the query below i am using. Report is a custom SQL report.

case when 'ALL' in @prompt('BT:','A',,Multi,Free,Not_Persistent,{'ALL','B','D'},User:0) then 1
when 'B' in @prompt('BT:','A',,Multi,Free,Not_Persistent,{'ALL','B','D'},User:0) then
Case when trim(table.col1)) not in 'U','V','W','X','Y','Z' then 1
else 0
end

when 'D' in @prompt('BT:','A',,Multi,Free,Not_Persistent,{'ALL','B','D'},User:0) then
Case when trim(table.col1)) in 'U','V','W','X','Y','Z' then 1
else 0
end
else 0
end = 1

Error Message : 

The following database error occurred: ORA-00920: invalid relational operator
. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)

TIA

[BIKAS]

@ajmaradiaga 

@N1kh1l 
@Palak 
@DataTherapist 

Can someone help please

[nachtaktiv]

- simplify your multiple-cases-and-values-statement to check if the syntax of a single part is ok and working !
- what is that supposed to do  "end = 1"  ?

[Riddhiman]

Generally the case syntax is on the variable, not sure if this kind of syntax is allowed. It seems to me that it would be like

case @prompt('BT:','A',,Multi,Free,Not_Persistent,{'ALL','B','D'},User:0) 

when 'ALL' then 1

when 'B' then

case when trim(table.col1) not in ('U','V','W','X','Y','Z') then 1

else 0

end

when 'D' then

case when trim(table.col1) in ('U','V','W','X','Y','Z') then 1

else 0

end

else 0

end

There seemed to be some missing/extra brackets as well.

[BIKAS]

Thank you for replying.

[JimSpath]

As a public service reminder (or maybe news to some), you can add and edit code in a readable format using this "Add/Edit Code Sample" control here.

case 
  when 'ALL' in @prompt('BT:','A',,Multi,Free,Not_Persistent, {'ALL','B','D'},User:0) 
    then 1
  when 'B' in @prompt('BT:','A',,Multi,Free,Not_Persistent,{'ALL','B','D'},User:0) 
    then
      case when trim(table.col1)) not in 'U','V','W','X','Y','Z' 
      then 1
  else 0
end

 I probably did not capture the intended logic flow, so you can help the community by editing any post with code to be more understandable.