Is it possible to somehow do this?
WITH T1 AS
(
SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
select 6 as seq, 'SOMETHING 4' AS SOME_TYPE from dual
)
, T2 AS
(
SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
SELECT 'B' AS COMPARE_TYPE FROM DUAL
)
SELECT T2.*, T1.*
FROM T1, T2
WHERE CASE T2.COMPARE_TYPE
WHEN 'A'
THEN T1.SOME_TYPE LIKE 'NOTHING%'
ELSE T1.SOME_TYPE NOT LIKE 'NOTHING%'
END
I know that my WHERE is clause is not correct.
Any help would be great in knowing if this type of statement is possible.
I don't want to write a Dynamic SQL. If I have to I will write 2 different SQL statements.
Thanks