CODEWITHSUNDEEP
sql
Prashanth Benny
Prashanth Benny

Reputation: 1609

WHERE on a SELECT sub-query

Here is a table

--------------------
|   Id    |   Val  |
|---------|--------|
|   1     |   4    |
|   2     |   3    |
|   3     |   5    |
|   4     |   1    |
--------------------

I need a query like

SELECT * , 
CASE 
 WHEN val > 3 THEN 'PASS' 
 ELSE 'FAIL' 
END as Status
FROM StatusTable
WHERE Status = 'PASS'

But, it doesn't seem to work that way. here is the error:

Uncaught Error: near "when": syntax error

Please help me with a workaround.

Upvotes: 0

Views: 88

Answers (3)

DonKnacki
DonKnacki

Reputation: 427

I am not answering directly your question but seems you want all line where val > 3
Why do you need to use 'Fail' and 'Pass' ?

SELECT Id, Val
FROM StatusTable
WHERE Val > 3

should give you the same result as with other suggested query

If you need a column with 'Pass', you can add it manually:

SELECT Id, Val, 'Pass'
FROM StatusTable
WHERE Val > 3

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18780

Assuming this is oracle: There are 2 things that break this query.

  • It's possible to do SELECT * to get all columns, but not to do SELECT *, <some other column>. That will raise an ORA-00923: FROM keyword not found where expected. The workaround is to alias the table and the "*" like this SELECT t.*, t.<some other column>
  • In the WHERE clause table columns or functions can be referenced, not aliases of columns that are in the SELECTclause. The WHERE Status = 'PASS' fails because "Status" is an alias, not a column.

Taking both those into account, the query becomes:

WITH StatusTable(id, val) AS
(
SELECT 1,4 FROM DUAL UNION ALL
SELECT 2,3 FROM DUAL UNION ALL
SELECT 3,5 FROM DUAL UNION ALL
SELECT 4,1 FROM DUAL 
) 
SELECT 
  t.* , 
  CASE 
   WHEN t.val > 3 THEN 'PASS' 
   ELSE 'FAIL' 
  END as Status
FROM StatusTable t
WHERE
  CASE 
   WHEN t.val > 3 THEN 'PASS' 
   ELSE 'FAIL' 
  END = 'PASS';

Upvotes: 2

Kamran Ali Raza Sandrana
Kamran Ali Raza Sandrana

Reputation: 13

You are missing from clause in your query

SELECT * , 
CASE 
 WHEN val > 3 THEN 'PASS' 
 ELSE 'FAIL' 
END as Status
FROM Table_Name
WHERE Status = 'PASS'

Upvotes: -1

Related Questions