SQL case query with multiple statement

Question

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 ---

Tag your question with the database you are using. As a general rule in SQL, you cannot use a column alias defined in the select in expressions in the same select (or where or from for that matter). — Gordon Linoff
– Gordon Linoff, Commented Jul 29, 2020 at 15:15
Is the query exactly as shown? Because you have an extra comma and you're missing FROM — Andy
– Andy, Commented Jul 29, 2020 at 15:17

forpas · Accepted Answer · 2020-07-29 15:27:40Z

1

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.

edited Jul 29, 2020 at 15:27

answered Jul 29, 2020 at 15:22

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sujitmohanty30 · Accepted Answer · 2020-07-29 15:24:05Z

0

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 ---
)

answered Jul 29, 2020 at 15:24

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

1 Comment

Sujitmohanty30 Over a year ago

In case of both option I agree to the point of @forpas about removing the else part first case statement

Collectives™ on Stack Overflow

SQL case query with multiple statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related