Error using HAVING in query with date / time

Navigation
1

I have the following query: 

 SELECT 
    ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
    V.CHAPA AS CHAPA,
    F.NOME  AS NOME,
    V.DATA  AS DATA,
    CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END AS IS_NULL
        FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
      WHERE
          V.CHAPA = 2311 
            GROUP BY V.CHAPA,V.DATA,F.NOME,V.BATIDA
                HAVING IS_NULL = '1900-01-01 00:00:00.000'
              ORDER BY DATA ASC

You are returning the following message:

  

Message 207, Level 16, State 1, Line 42 Invalid column name   'IS_NULL'.

    
asked by anonymous 20.10.2016 / 20:44

3 answers

2

Depending on the database you are using, you can not use a column being HAVING in SELECT

An alternative would be: 

SELECT 
    ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
    V.CHAPA AS CHAPA,
    F.NOME  AS NOME,
    V.DATA  AS DATA,
    CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END AS IS_NULL
FROM
    ARELBATIDATRANSITOVIEW AS V
LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
WHERE
    V.CHAPA = 2311 
GROUP BY V.CHAPA,V.DATA,F.NOME,V.BATIDA
HAVING 
    CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END = '1900-01-01 00:00:00.000'
ORDER BY DATA ASC
    
20.10.2016 / 21:33
0

I think you're missing the nickname in IS_NULL . For example: 

a.IS_NULL
    
20.10.2016 / 21:08
0

Final result: 

 SELECT 
    ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
    V.CHAPA AS CHAPA,
    F.NOME  AS NOME,
    V.DATA  AS DATA,
    isnull(V.BATIDA, 0 ) as TBATIDA
FROM
 ARELBATIDATRANSITOVIEW AS V
 LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
WHERE V.CHAPA = 2311  
AND isnull(V.BATIDA, 0 )='1900-01-01 00:00:00.000'
  ORDER BY DATA ASC
    
20.10.2016 / 22:39
How to prevent loading a Javascript library more than once Is it possible to access multiple databases in the same Laravel 5 project?