locked
HELP WITH SQL STATEMENT RRS feed

  • Question

  • Morning,

    I have a field as part of my SQL statement which I want to be able to say if the value is > 0 then have make the value Y, If the value is 0 then make the value N.

    Im thinking this is IF, ELSE Maybe? Im just not sure how id go about changing my syntax to give me what I want.

    My SQL Statement is:

    SELECT DISTINCT 
               Emp.Employee_Number, Emp.Surname, Emp.Preferred_Name, Emp.EMail, ISNULL(dr.DirectReports, 0) AS Direct_Reports, 
               reportsTo.Person_Number AS Manager_Aurion_Number, Emp.Division, Emp.Org_Unit, Emp.Reporting_Unit, Emp.Position_number, Emp.Job_Title, 
               Emp.Business_Unit
    FROM     dbo.vwEmployeesListing AS Emp LEFT OUTER JOIN
               dbo.vwEmployeesListing AS reportsTo ON Emp.Reports_To_Position = reportsTo.Position_number LEFT OUTER JOIN
                 (SELECT   Reports_To_Position, COUNT(*) AS DirectReports
                  FROM     dbo.vwEmployeesListing AS dr1
                  GROUP BY Reports_To_Position) AS dr ON Emp.Position_number = dr.Reports_To_Position
    WHERE   (Emp.Status = 'ACTIVE')

    and the field I am referring to is ISNULL(dr.DirectReports, 0) AS Direct_Reports

    Can anybody Help?

    Thanks

    Friday, August 12, 2011 1:13 AM

Answers

  • Try using CASE statement:

     

    SELECT DISTINCT 
      Emp.Employee_Number, Emp.Surname, Emp.Preferred_Name, Emp.EMail, ISNULL(dr.DirectReports, 0) AS Direct_Reports, 
    
    CASE WHEN ISNULL(dr.DirectReports, 0) > 0 then 'Y' else 'N' END as [Manager],
    
      reportsTo.Person_Number AS Manager_Aurion_Number, Emp.Division, Emp.Org_Unit, Emp.Reporting_Unit, Emp.Position_number, Emp.Job_Title, 
      Emp.Business_Unit
    FROM dbo.vwEmployeesListing AS Emp LEFT OUTER JOIN
      dbo.vwEmployeesListing AS reportsTo ON Emp.Reports_To_Position = reportsTo.Position_number LEFT OUTER JOIN
      (SELECT Reports_To_Position, COUNT(*) AS DirectReports
      FROM dbo.vwEmployeesListing AS dr1
      GROUP BY Reports_To_Position) AS dr ON Emp.Position_number = dr.Reports_To_Position
    WHERE (Emp.Status = 'ACTIVE')
    
    

    In SQL Server Denali you will be able to also use IIF function.

    BTW, it's not morning everywhere in the word. Say, here it's 8:25 PM.


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


    My blog


    • Marked as answer by lrj1985 Friday, August 12, 2011 1:41 AM
    Friday, August 12, 2011 1:20 AM

All replies

  • Try using CASE statement:

     

    SELECT DISTINCT 
      Emp.Employee_Number, Emp.Surname, Emp.Preferred_Name, Emp.EMail, ISNULL(dr.DirectReports, 0) AS Direct_Reports, 
    
    CASE WHEN ISNULL(dr.DirectReports, 0) > 0 then 'Y' else 'N' END as [Manager],
    
      reportsTo.Person_Number AS Manager_Aurion_Number, Emp.Division, Emp.Org_Unit, Emp.Reporting_Unit, Emp.Position_number, Emp.Job_Title, 
      Emp.Business_Unit
    FROM dbo.vwEmployeesListing AS Emp LEFT OUTER JOIN
      dbo.vwEmployeesListing AS reportsTo ON Emp.Reports_To_Position = reportsTo.Position_number LEFT OUTER JOIN
      (SELECT Reports_To_Position, COUNT(*) AS DirectReports
      FROM dbo.vwEmployeesListing AS dr1
      GROUP BY Reports_To_Position) AS dr ON Emp.Position_number = dr.Reports_To_Position
    WHERE (Emp.Status = 'ACTIVE')
    
    

    In SQL Server Denali you will be able to also use IIF function.

    BTW, it's not morning everywhere in the word. Say, here it's 8:25 PM.


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


    My blog


    • Marked as answer by lrj1985 Friday, August 12, 2011 1:41 AM
    Friday, August 12, 2011 1:20 AM
  • You need to use a CASE statement.

    Something like:

    CASE WHEN <ColumnName> > 0 THEN <Y> ELSE <N> END as <ColumnAlias>

     

    HTH


    Craig Bryden - Please mark correct answers
    Friday, August 12, 2011 1:22 AM
  • Many Thanks. Works perfectly.
    Friday, August 12, 2011 1:42 AM