Complex SQL not working in query editor

[rathi]

Hi,

I have a query as

Select

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

attribute6,

attribute7,

attribute8,

attribute9,

 attribute10

From farmer df

where df.CompanyId =$P{company_id}

and 1= 

(case 

when $P{current_history} = 'Active' and df.partitionflag = 1 then 1

when $P{current_history} = 'Inactive' and df.partitionflag = 0 then 1

else 0

end)

 

The case part in the 'where' clause isn't working.

 

Any help on understanding how to solve this would be greatly appreciated

 

Thanks

Rathi

[dimiturtabakov]

"and 1=
(case
when $P{current_history} = 'Active' and df.partitionflag = 1 then 1
when $P{current_history} = 'Inactive' and df.partitionflag = 0 then 1
else 0
end)"
 I'm not sure that's the way to do what you're trying to. Try this instead:
"...
and case
        when $P{current_history} = 'Active' and df.partitionflag = 1 then 1=1
        when $P{current_history} = 'Inactive' and df.partitionflag = 0 then 1=1
        else 1=0
    end
    "

 

[rathi]

Thank you will try this :)

[rathi]

Hi , 

The suggested case expression is not working.

Does jasper not support case?

Thanks

Rathi

[rathi]

Hi,

Its working now. Its just that the case statement must not be surrounded by brackets

.... and 1= (case when .... then ... end) does not work

but ... and 1 = case when.., then ... end works

Thanks

Rathi