locked
Force Query To Return 0 not null RRS feed

  • Question

  • This is my syntax and it does not return any results, which is accurate, but how can I set it to return a '0' instead of a blank result set?  I REALLY need this to take place within a query and not a stored procedure, if at all possible...

    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone

    Wednesday, January 15, 2014 6:24 PM

Answers

  • Yes, there is another way to write the query above using UNION ALL, e.g.

    ;with CTE AS (Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone)
    
    
    SELECT * FROM CTE
    
    UNION ALL
    
    SELECT 0 as EmployeeID, '' as EmployeePhone, 'Employee Info' as Data
    WHERE NOT EXISTS (SELECT 1 FROM CTE); -- in case there are no rows in the select
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 15, 2014 7:29 PM

All replies

  • if for example colA of tbl_name is returning NULL and you want it to return 0 in place of NULL then:

    select ISNULL(colA,0) from tbl_name


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, January 15, 2014 6:26 PM
  • if for example colA of tbl_name is returning NULL and you want it to return 0 in place of NULL then:

    select ISNULL(colA,0) from tbl_name


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Tried the ISNULL() approach, not working is returning a blank query result set :(
    Wednesday, January 15, 2014 6:29 PM
  • Try like this

    select isnull(id,0)
    from
    (
    select COUNT(*) id from msdb.dbo.sysjobsteps where command ='avc'
    )T

    -Prashanth

    Wednesday, January 15, 2014 6:35 PM
  • Joe,

    depends where you use.. Can yu tell us for which column of the above query you want it to be implemented?

    select 
      isnull(employeeID,0) as employeeID,
      isnull(employeephone,0) as employeePhone,
      data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt



    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    • Edited by Jayakumaur (JK) Wednesday, January 15, 2014 6:47 PM added code..
    Wednesday, January 15, 2014 6:45 PM
  • Try like this

    select isnull(id,0)
    from
    (
    select COUNT(*) id from msdb.dbo.sysjobsteps where command ='avc'
    )T

    -Prashanth

    Using your example returned -- (No Column Name) and a 0

    Now how would I format that to fit my scenario/

    • Edited by Joe Marx's Wednesday, January 15, 2014 6:50 PM
    Wednesday, January 15, 2014 6:45 PM
  • Joe,

    depends where you use.. Can yu tell us for which column of the above query you want it to be implemented?

    select 
      isnull(employeeID,0) as employeeID,
      isnull(employeephone,0) as employeePhone,
      data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt



    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Only the employeeID field.

    Msg 4104 Level 16 State 1 Line 2

    And attempting this query I get an error of the multi-part identifier employeeID could not be found

    the multi-part identifier employeephone could not be found

    • Edited by Joe Marx's Wednesday, January 15, 2014 6:54 PM
    Wednesday, January 15, 2014 6:51 PM
  • select 
      isnull(employeeID,0) as employeeID,
      employeePhone,
      data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt
    This shud help..

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, January 15, 2014 6:54 PM
  • Try:

    IF OBJECT_ID('tempDB..#Results', N'U') IS NOT NULL DROP TABLE #Results;

    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data

    INTO #Results

    FROM tableMaster b Inner Join tableBadSetup a ON a.empID = b.empID Where b.empStatus = 'Leave' Group By b.employeePhone;

    IF @@RECCOUNT = 0

    SELECT 0 as EmployeeID, -- BTW, strange column's name for a count

    '' as EmployeePhone, 'Employee Info' AS DATA;

    ELSE

    SELECT * FROM #Results;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 15, 2014 6:55 PM
  • select 
      isnull(employeeID,0) as employeeID,
      employeePhone,
      data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt
    This shud help..

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Same error:

    the multi-part identifier employeephone could not be found

    Wednesday, January 15, 2014 6:58 PM
  • Try:

    IF OBJECT_ID('tempDB..#Results', N'U') IS NOT NULL DROP TABLE #Results;

    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data

    INTO #Results

    FROM tableMaster b Inner Join tableBadSetup a ON a.empID = b.empID Where b.empStatus = 'Leave' Group By b.employeePhone;

    IF @@RECCOUNT = 0

    SELECT 0 as EmployeeID, -- BTW, strange column's name for a count

    '' as EmployeePhone, 'Employee Info' AS DATA;

    ELSE

    SELECT * FROM #Results;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Is there a way to do this syntactically through a straight query and avoid using a temp table or a stored proc?
    Wednesday, January 15, 2014 6:59 PM
  • Joe,

    Can you pls check if tableMaster has the column employeePhone and if the below query given by you is running without any error?

    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone

    If the above runs without any error, then the below should as well .. 

    select 
      isnull(tt.employeeID,0) as employeeID,
      tt.employeePhone,
      tt.data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, January 15, 2014 7:14 PM
  • Joe,

    Can you pls check if tableMaster has the column employeePhone and if the below query given by you is running without any error?

    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone

    If the above runs without any error, then the below should as well .. 

    select 
      isnull(tt.employeeID,0) as employeeID,
      tt.employeePhone,
      tt.data
    from
    (
    Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone
    ) tt


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    table master does contain employeePhone and yes the query I provided is runnign w/o error.  The query you postd above, prefacing each field before the FROM statement now has this query running succesful, however it is still returning a blank dataset and not any data.
    Wednesday, January 15, 2014 7:22 PM
  • Yes, there is another way to write the query above using UNION ALL, e.g.

    ;with CTE AS (Select count(a.empID) As employeeID, b.employeePhone, 'Employee Info' As data
    
    FROM tableMaster b Inner Join tableBadSetup a
    ON a.empID = b.empID
    Where b.empStatus = 'Leave'
    Group By b.employeePhone)
    
    
    SELECT * FROM CTE
    
    UNION ALL
    
    SELECT 0 as EmployeeID, '' as EmployeePhone, 'Employee Info' as Data
    WHERE NOT EXISTS (SELECT 1 FROM CTE); -- in case there are no rows in the select
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 15, 2014 7:29 PM
  • If the inner query you provided returns rows when executed seperately the whole query will also return results..

    If otherwise, can yu post sample output of your inner query ?


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, January 15, 2014 7:30 PM