SQL query works in developer but not in JasperSoft Studio

Question

I've got a problem with the SQL query in my Jasperreport. The query is this:

select distinct 
  t.costcenter, 
  t.workplace, 
  t.sap_master_key, 
  t.product_family, 
  mb.max_module_name
from 
  timings t, 
  max_bbz_per_timing mb
where (
       ($P{Kostenstelle}  is not null and  
        $P{APS} is not null 
        and t.timing_id in (
          select 
            timing_id 
          from 
            timings 
          where costcenter = $P{Kostenstelle} 
          and workplace =  $P{APS} 
        )
       )
       or
        ($P{Kostenstelle} is not null 
         and $P{APS} is null 
         and t.timing_id in (
           select 
             timing_id 
           from 
             timings 
           where costcenter = $P{Kostenstelle}
         )
       )
       or
        ($P{Kostenstelle} is null 
         and $P{APS} is not null 
         and t.timing_id in (
           select 
             timing_id 
           from 
             timings 
           where workplace = $P{APS} 
         )
       )
    )
and mb.timing_id =t.timing_id
and mb.max_module_name is not null

$P{Kostenstelle} and $P{APS} are the parameters. They both can be not null or only one of them. when I try this SQL query in my development environment, it does what it should do, but in JasperSoft Studio it only executes when $P{Kostenstelle} is not null and $P{APS} is null, else it shows no results although it should.

I hope someone of you can help me here, I'm clueless.

Answer 1

This huge SQL script is equivalent to just that:

select distinct 
  t.costcenter, 
  t.workplace, 
  t.sap_master_key, 
  t.product_family, 
  mb.max_module_name
from timings t
inner join max_bbz_per_timing mb
  on mb.timing_id = t.timing_id and mb.max_module_name is not null
inner join timings t_t
  on t_t.timing_id = t.timing_id
  and ($P{Kostenstelle} is not null or $P{APS} is not null)
  and ($P{Kostenstelle} is null or t_t.costcenter = $P{Kostenstelle}) 
  and ($P{APS}          is null or t_t.workplace  = $P{APS})

If timing_id is unique the query can be even more straightforward:

select distinct 
  t.costcenter, 
  t.workplace, 
  t.sap_master_key, 
  t.product_family, 
  mb.max_module_name
from timings t
inner join max_bbz_per_timing mb
  on mb.timing_id = t.timing_id and mb.max_module_name is not null
where ($P{Kostenstelle} is not null or $P{APS} is not null)
  and ($P{Kostenstelle} is null or t.costcenter = $P{Kostenstelle}) 
  and ($P{APS}          is null or t.workplace  = $P{APS})