I think the answer to this is "No", but I wanted to check anyway.
Is there any way a Case Statement can be used to flag the same data item twice?
For example, this is some dummy data:
WITH table_data AS
(SELECT 'Payables' jnl_src , 400900 doc_number FROM DUAL UNION ALL
SELECT 'Receivables' jnl_src, 400901 doc_number FROM DUAL UNION ALL
SELECT 'Projects' jnl_src , 400902 doc_number FROM DUAL UNION ALL
SELECT 'Payables' jnl_src , 400900 doc_number FROM DUAL UNION ALL
SELECT 'Manual' jnl_src , 400976 doc_number FROM DUAL)
SELECT CASE WHEN jnl_src IN ('Payables') THEN 'AP'
WHEN jnl_src IN ('Receivables') THEN 'AR'
WHEN jnl_src IN ('Projects') THEN 'Projects'
WHEN doc_number IN ('400976') THEN 'Donations Correction'
WHEN doc_number IN ('400976') THEN 'OTHER'
ELSE 'GL'
END case_check
FROM table_data;
I don't know if it is possible, but in this example, we'd like to be able to label doc_number twice, once as "Donations Correction" and once as "OTHER".
I realise it's probably not possible since the CASE statement finds a match, and then stops looking at any of the other WHEN sections once a match is found.
It's just that we have a Calculation on a Discoverer Report which is made up of a CASE statement, and we ideally need to be able to categorise the same Document Number with 2 different labels in the same CASE statement.
Thanks