Having Invalid column name when the name exist SQL Server

Question

I am having an error on the column "PREMIUM" when the premium exist on the select statement. I am not sure what I am doing wrong.

SELECT QUIKPLAN.FORMS, COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ((QUIKPLAN.FORMS)='L-4054' and ((PREMIUM)>1))

This code works in Snowflake, but it is not working in SQL Server. Thanks for your help.

You can't use calculated columns in the having clause in SQL Server, you need to include that in a sub-query of some sort. — Dale K, Nov 15 '22 at 20:47
@Desmond yes we know but that belongs always in the question — nbk, Nov 15 '22 at 20:56

score 0 · Accepted Answer · answered Nov 15 '22 at 20:49

As mentioned in the comment above, you cannot access a calculated field within the same query scope. However, you can use the same calculation twice with no penalty.

SELECT 
    QUIKPLAN.FORMS, 
    COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, 
    ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM 
    QUIKPLAN
    LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ((QUIKPLAN.FORMS)='L-4054' and ((ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0))>1))

eshirvana · Answer 2 · 2022-11-15T20:53:45.667

0

you need to do this :

SELECT 
    QUIKPLAN.FORMS
    , COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate
    , ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 
   ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
WHERE (QUIKPLAN.FORMS)='L-4054'
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) >1

edited Nov 15 '22 at 20:53

answered Nov 15 '22 at 20:49

eshirvana

23,227
3
22
38

score 0 · Answer 3 · answered Nov 15 '22 at 20:53

You can use a CTE for that

WITH CTE AS (SELECT QUIKPLAN.FORMS, COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING QUIKPLAN.FORMS ='L-4054' )

SELECT FORMS, ReportDate,PREMIUM
FROM CTE
WHERE PREMIUM>1

Having Invalid column name when the name exist SQL Server

3 Answers3