Comparing VarCHAR FIELD with NULL

Hi, I have the following query

SELECT *
FROM PABX
INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE)
AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3)
LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE)
WHERE
pabx.COD_cliente = 224 and
SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7')
AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2)
AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE
AND PABX.CLASSIFICA IS NULL
AND PABX.LOCALIDADE <> AUXILIAR.DESTINO
AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)

But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it

PS: Both auxiliar.destino and pabx.localidade is varchar(255)

Thanks




Answer this question

Comparing VarCHAR FIELD with NULL

  • ScottL

    what happens now

    SELECT *
    FROM PABX
    INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE)
    AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3)
    LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE)
    AND PABX.LOCALIDADE <> AUXILIAR.DESTINO
    WHERE
    pabx.COD_cliente = 224 and
    SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7')
    AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2)
    AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE
    AND PABX.CLASSIFICA IS NULL

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/



  • EEbigsky

    It works, thanks a LOT

    I lov u...



  • ChristoLee

    Wich one do you think it's better

    Thanks



  • jackie2526

    you can also change PABX.LOCALIDADE <> AUXILIAR.DESTINO to

    PABX.LOCALIDADE <> COALESCE(AUXILIAR.DESTINO,'')


  • Comparing VarCHAR FIELD with NULL