question

RizalValry avatar image
0 Votes"
RizalValry asked EchoLiu-msft edited

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

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

131306-latest.png


sql-server-generalsql-server-transact-sql
latest.png (10.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @RizalValry

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I use this method to save query lines


even though your method is almost right,

I want to use the "HAVING" clause for some query conditions that are still in the select

is there any other way than the one you gave sir?

0 Votes 0 ·

In fact, the definition of HAVING in SQL SERVER is to filter the results after grouping. The role of WHERE is to filter the results returned by the FROM clause.

There is no explicit or implicit grouping in your code, so HAVING cannot be used.

Regards
Echo

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered RizalValry commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @Viorel-1

how with sts ? I need declaration from "sts = 'Kadaluarsa"

is there another way ?

0 Votes 0 ·
TomCooper-6989 avatar image
1 Vote"
TomCooper-6989 answered RizalValry commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, correct statement

131407-mysql-runwell.png

although that's it running well in mySQL Query


0 Votes 0 ·
mysql-runwell.png (35.8 KiB)
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered RizalValry commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I see

Thank you for the detailed explanation

0 Votes 0 ·