I have a report whose query goes something like this:
SELECT (outer select)
FROM
(Inner Query
) "T0"
WHERE
CASE
WHEN ("T0"."C10">"T0"."C11")
THEN 'A'
ELSE 'B'
END ='B' <-- Detail filter 1
AND "T0"."C12" =1 <-- Detail filter 2
AND "T0"."C13"<=6 <-- Detail filter 3
ORDER BY "Field Names"
There are 3 detail filters in the report, and that's what you see in the where clause of the query above. I want the filters to function in such a way that the Detail filter 1 and 2 get applied to the report together, whereas the Detail filter 3 only gets applied to the result of Detail filter 1 and 2 and not along with them. So the query should look something like this:
SELECT
FROM (
(SELECT (outer select)
FROM
(Inner Query
) "T0"
WHERE
CASE
WHEN ("T0"."C10">"T0"."C11")
THEN 'A'
ELSE 'B'
END ='B' <-- Detail filter 1
AND "T0"."C12" =1 <-- Detail filter 2
ORDER BY "Field Names"
) T0 WHERE "T0"."C13"<=6 <-- Detail filter 3
How do I make this happen? I have tried "After auto aggregation" property but that gives unexpected results. Maybe I need to create another query and somehow link that to the main query ?
The result sets of the two queries are different and the second one gives the right results.
Thanks for your help.