-1

I am trying to write a sql select like below

SELECT CASE WHEN table1.col1 IS NOT NULL 
AND  table2.col1='someValue' THEN 'YES'
WHEN main.col2 IS NOT NULL 
AND table2.col1 NOT LIKE '%Rejected%' THEN 'YES'
        ELSE NULL END AS eligible,table1.col13,table2.col14 
        FROM table1 
        INNER JOIN table2 ON table1.col5=table2.col6     
        WHERE eligible='YES'

but getting Invalid column name 'eligible', please advice

obeid salem
  • 129
  • 2
  • 13
Assessor1
  • 1
  • Welcome to the community. Do you have a column in either table1 or table2 named 'eligible'? If so, please post a minimally reproduceable sample of your data. – Edward Radcliffe Apr 08 '22 at 22:49
  • [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) – Ken White Apr 08 '22 at 23:24
  • kindly check [this](https://stackoverflow.com/a/18941677/11236563), seems to be your same issue – Omar Tammam Apr 08 '22 at 23:27

1 Answers1

0

What you probably looking for is not WHERE eligible. Instead it's HAVING eligible:

SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6     
HAVING eligible='YES';  /*here*/

Since the column eligible is actually an alias of your CASE expression and is not from any of your tables then you can't use it in WHERE.

If you're more familiar with using WHERE, here's a workaround by making the base query as subquery first:

SELECT * 
  FROM
( SELECT CASE WHEN table1.col1 IS NOT NULL AND  table2.col1='someValue'  
                THEN 'YES'
            WHEN main.col2 IS NOT NULL AND table2.col1 NOT LIKE '%Rejected%' 
                THEN 'YES'
           ELSE NULL END AS eligible,
      table1.col13,
      table2.col14 
FROM table1 
INNER JOIN table2 ON table1.col5=table2.col6 ) AS subquery
WHERE eligible='YES';
FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • I dont have right to upvote your answer but it answered my question suggesting two ways of having and where in right way. Thank you again – Assessor1 Apr 11 '22 at 16:19
  • If it solve your issue, you can mark as answer. That way others who might have same issue will know that this is a possible solution for them. Refer this link ["What should I do when someone answer my question?"](https://stackoverflow.com/help/someone-answers#:~:text=To%20mark%20an%20answer%20as,the%20answer%2C%20at%20any%20time.) – FanoFN Apr 12 '22 at 00:18