CODEWITHSUNDEEP
sqloracle-database
KSp
KSp

Reputation: 1239

SQL case query with multiple statement

I am trying to have a new column using case query but i also wanted to use it in my other case query as shown below, i am unable to execute the query below . Please give your opinion. Thanks

    select

    case  when ccis.id  is not null then 'High'
    else null 
    end as category,
    
    case  
    when category is not NULL then True
    else False 
    end as flag



    FROM "view1" as ccis left outer join "view2" as cctm 
    on ccis.study_name = cctm.study_name 


    from goes here ---
    Join  statement goes here ---

Upvotes: 1

Views: 79

Answers (2)

forpas
forpas

Reputation: 164089

You can't use category in the next CASE expression.
But you can apply the same logic with the previous CASE:

select
  case when id  is not null then 'High' end as category,
  case when id  is not null then 'True' else 'False' end as flag
  ..........................

because category is not null when id is not null.
I removed else null from the 1st CASE expression because it is not needed since it is the default functionality to return null when none of the conditions in the when branches is satisfied.

Upvotes: 1

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

how about , ( you can simply use the id again to determine flag or if you say you cannot, just add a outer clause and use category there )

SELECT
 CASE
    WHEN id IS NOT NULL THEN
     'High'
    ELSE
     NULL
 END AS category,
 CASE
    WHEN id IS NOT NULL THEN
     TRUE
    ELSE
     FALSE
 END AS flag
FROM   goes here ---
JOIN   STATEMENT goes here ---

OR
    
SELECT
CASE
    WHEN category IS NOT NULL THEN
     TRUE
    ELSE
     FALSE
 END AS flag
FROM
( 
  SELECT
   CASE
      WHEN id IS NOT NULL THEN
       'High'
      ELSE
       NULL
   END AS category,
  FROM   goes here ---
  JOIN   STATEMENT goes here ---
)

Upvotes: 0

Related Questions