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
Best Answer
Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.
I'm guessing that you want something like
But I'm making a lot of guesses about what your code is supposed to mean.
That this appears to be identical to a question someone asked in the OTN forums. My answer is the same in both places.