Help me Please, My Query "HAVING" syntax in SQL SERVER error

Question

I Have Query :

SELECT
pks.id,
pks.nomor,
pks.nasabah_code,
nasabah.nama,
FORMAT(pks.pks_date_start, 'dd-MM-yyyy') AS tglMulai,
FORMAT(pks.pks_date_end, 'dd-MM-yyyy') AS tglAkhir,
pks.file_doc,
DATEDIFF(DAY, pks.pks_date_end, GETDATE()) as selExp,
CASE
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) < 0 THEN 'Akan Berakhir'
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) = 0 THEN 'Berakhir'
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) > 0 THEN 'Kadaluarsa'
END AS sts
FROM
vm.pks
INNER JOIN vm.nasabah ON nasabah.code = pks.nasabah_code AND nasabah.record_code = '1'
WHERE
pks.record_code = '1' AND
(pks.nomor LIKE '%CBG%' OR
pks.nasabah_code LIKE '%BSP%' OR
nasabah.nama LIKE '%Bina San Prima%')
HAVING sts = 'Kadaluarsa' AND selExp >= '0'
ORDER BY
selExp DESC

After execution get result code error :
Msg 207, Level 16, State 1, Line 23
Invalid column name 'sts'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'selExp'.

Answer 1

Hi @Rizal Valry ，

As Tom said, the order in which SQL Server processes statements is different from the order in which statements are written.

The aliases named in the CTE can be used at every stage of the code, please refer to:

;WITH cte as(SELECT  
pks.id,  
pks.nomor,  
pks.nasabah_code,  
nasabah.nama,  
FORMAT(pks.pks_date_start, 'dd-MM-yyyy') AS tglMulai,  
FORMAT(pks.pks_date_end, 'dd-MM-yyyy') AS tglAkhir,  
pks.file_doc,  
DATEDIFF(DAY, pks.pks_date_end, GETDATE()) as selExp,  
CASE  
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) < 0 THEN 'Akan Berakhir'  
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) = 0 THEN 'Berakhir'  
WHEN DATEDIFF(DAY, pks.pks_date_end, GETDATE()) > 0 THEN 'Kadaluarsa'  
END AS sts  
FROM  
vm.pks  
INNER JOIN vm.nasabah   
ON nasabah.code = pks.nasabah_code AND nasabah.record_code = '1'  
WHERE  
pks.record_code = '1' AND  
(pks.nomor LIKE '%CBG%' OR  
pks.nasabah_code LIKE '%BSP%' OR  
nasabah.nama LIKE '%Bina San Prima%'))  
  
SELECT * FROM cte  
WHERE sts = 'Kadaluarsa' AND selExp >= '0'  
ORDER BY selExp DESC  

In addition, the HAVING clause is generally used with aggregate functions or GROUP BY clauses. So your code is best to use WHERE clause instead of HAVING clause.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

SQL Select commands are not compiled from top to bottom. Although the first clause is the SELECT, that is one of the last clause's processed. The logical order a SELECT statement is scanned and made into an execution plan is

1) FROM including any JOINs and APPLYs, 2) WHERE, 3) GROUP BY, 4) HAVING, 5) SELECT, and 6) ORDER BY

So you are referencing sts and selExp in step 4 (HAVING), but they aren't defined until step 5 (SELECT), so you get an error.

Tom

Answer 3

Try replacing the HAVING line with AND:

 AND DATEDIFF(DAY, pks.pks_date_end, GETDATE()) > 0

Sometimes you can also compare the dates without DATEDIFF.

Answer 4

As @EchoLiu-MSFT noted, HAVING is used with aggregate functions (for example SUM or MAX) and (almost always) GROUP BY.

If you have a GROUP BY and a HAVING clause, the only items allowed in your SELECT clause are items in the GROUP BY, aggregate functions, and constants.

If you have a HAVING clause and don't have a GROUP BY, the only items allowed in your SELECT clause are aggregate functions and constants and the result of your query will have either zero rows or one row. It can't return multiple rows.

So for the query you have shown us, you definitely want to use WHERE and not HAVING.

Tom