TheOneThing TheOneThing - 5 months ago 22
SQL Question

SQL query works in developer but not in JasperSoft Studio

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

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})