Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers 3

up vote 6 down vote accepted

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

SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> / 

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

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.

share|improve this answer
    
Thanks for the answer. This works. I am sorry that my question was not clear. –  Pranav Shah Feb 11 '11 at 14:48
add comment

Try writing the where clause this way:

WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
   OR (T2.COMPARE_TYPE <> 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')
share|improve this answer
    
Thanks for the answer. This works too. I am sorry that my question was not clear. I am sorry, but I can only mark one answer as "Accepted Answer" –  Pranav Shah Feb 11 '11 at 14:49
    
+1 This is another of several correct interpretations. –  Leigh Riffel Feb 11 '11 at 16:08
add comment

Justin Cave and Eric Humphrey's queries both return different results. Here is a third equally valid answer that returns a third different set of results:

WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
OR T1.SOME_TYPE NOT LIKE 'NOTHING%' 

Only you will know which answer gives the results you are expecting, but they are all correct answers to the question as given.

Your question is better than many because it is self contained and includes the source data, but it will help if you include the output you are looking for as well. I recommend you add that to the question and make sure the accepted answer matches those results.

share|improve this answer
    
Thanks. I was more or less looking as to how I should do this. I din't care for the result. I was just looking for a set of code that would not give me an error. I will though take a second look and put an answer out. –  Pranav Shah Feb 11 '11 at 18:16
    
If the answer is anything that removes the error than the solution set is infinite and easiest solution is to remove the WHERE clause, which obviously wouldn't be helpful. So that this answer will be helpful in the future I suggest you change it to something like "How can CASE be referenced in a WHERE clause", which would fit with your accepted answer. –  Leigh Riffel Feb 11 '11 at 21:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.