CODEWITHSUNDEEP
oracle-databaseoracle12c
Abderrahim
Abderrahim

Reputation: 663

Error in my Subquery Factoring (WITH) Clause

I have an error when I run this script :

WITH q AS (
  SELECT COUNT(id)
  FROM   test
  )
SELECT 1, 
  CASE 
    WHEN q=0 then 'toto'
    ELSE 'titi'
  END

FROM   dual;

I must to use a Subquery Factoring (WITH) clause.

The idea is to display 'toto' when the count equal 0 else I must display 'titi'.

Upvotes: 1

Views: 226

Answers (2)

MT0
MT0

Reputation: 168361

You can simplify it to:

SELECT 1, 
       CASE COUNT(*) WHEN 0 then 'toto' ELSE 'titi' END
FROM   test;

The error in your query is that you have created a named subquery q but then you are selecting the column q from the DUAL table (which does not have a column q).

You can fix your query like this:

WITH q AS (
  SELECT COUNT(id) AS cnt -- name this column
  FROM   test
)
SELECT 1, 
       CASE WHEN cnt = 0 -- use the column you named previously
            THEN 'toto'
            ELSE 'titi'
            END
FROM   q; -- use the named subquery q not the dual table

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use an alias for the result, and FROM q

WITH q AS (
  SELECT COUNT(id) as total
  FROM   test
  )
SELECT 1, 
  CASE 
    WHEN q.total = 0 THEN 'toto'
    ELSE 'titi'
  END

FROM   q;

Upvotes: 2

Related Questions