Error using HAVING in date/time query

Asked

Viewed 76 times

1

I have the following appointment:

 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

Is returning the following message:

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

sql query

  • that’s correct IS_NULL ? would not be is null ??

  • is_null was just the name I gave to the column, plus any name I put from the bug

3 answers

2


Depending on the database you are using, you cannot use HAVING a column being created in the 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

0

I believe the nickname is missing from IS_NULL. For example:

a.IS_NULL

  • I didn’t, thank you

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

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.