To assign the value 0 to a query by returning NULL SQL

Asked

Viewed 1,541 times

0

Good afternoon, I have a question regarding the treatment of values in a query. The query below to the executed returns a null value NULL as I could assign the value 0 instead of NULL? 

    select 
    master.VALPROV13 VALPROV13ACUM 
    from PFHSTPROV master (NOLOCK),PFUNC a 
    where master.CHAPA=a.CHAPA

    and
    (  ((master.CODCOLIGADA=2) 
    and  (master.CHAPA=001024)))  
    and (  master.MES = 07
    and master.ANO = 2018  )
sql query null

  • Use the coalesce function, for your case: coalesce(field_que_pode_ser_nulo, 0).

1 answer

1


In SQL Server allows the use of the ISNULL function, which validates if the output is null and closes the default you set. Together I share your query for SQL Server, Mysql and Oracle.

SQL Server

 select 
    ISNULL(master.VALPROV13, 0) VALPROV13ACUM 
    from PFHSTPROV master (NOLOCK),PFUNC a 
    where master.CHAPA=a.CHAPA

    and
    (  ((master.CODCOLIGADA=2) 
    and  (master.CHAPA=001024)))  
    and (  master.MES = 07
    and master.ANO = 2018  )

Mysql

 select 
    IFNULL(master.VALPROV13, 0) VALPROV13ACUM 
    from PFHSTPROV master (NOLOCK),PFUNC a 
    where master.CHAPA=a.CHAPA

    and
    (  ((master.CODCOLIGADA=2) 
    and  (master.CHAPA=001024)))  
    and (  master.MES = 07
    and master.ANO = 2018  )  

Outra opção...

 select 
    COALESCE(master.VALPROV13, 0) VALPROV13ACUM 
    from PFHSTPROV master (NOLOCK),PFUNC a 
    where master.CHAPA=a.CHAPA

    and
    (  ((master.CODCOLIGADA=2) 
    and  (master.CHAPA=001024)))  
    and (  master.MES = 07
    and master.ANO = 2018  )

Oracle

 select 
    NVL(master.VALPROV13, 0) VALPROV13ACUM 
    from PFHSTPROV master (NOLOCK),PFUNC a 
    where master.CHAPA=a.CHAPA

    and
    (  ((master.CODCOLIGADA=2) 
    and  (master.CHAPA=001024)))  
    and (  master.MES = 07
    and master.ANO = 2018  )

Browser other questions tagged sql query null

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