[dba-SQLServer] SQL Views Code For IIF

Just tried the following code and got an error CASE is not supported any idea's why ?
SELECT tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode, 
     CASE lblStaffingCode 
  WHEN 'Manager' THEN COUNT([PayrollNo])
       WHEN 'Supervisor' THEN COUNT([PayrollNo]) 
      WHEN 'Pharm Super' THEN COUNT([PayrollNo]) 
      WHEN 'Water Super' THEN COUNT([PayrollNo]) 
      WHEN 'Wilk Manager' THEN COUNT([PayrollNo]) 
     END AS StaffMan 
 CASE lblStaffingCode 
  WHEN 'Counter' THEN COUNT([PayrollNo])
  WHEN 'Checker' THEN COUNT([PayrollNo]) 
      WHEN 'Control' THEN COUNT([PayrollNo]) 
      WHEN 'Planner' THEN COUNT([PayrollNo]) 
      WHEN 'Dispensary' THEN COUNT([PayrollNo]) 
      WHEN 'Pharmacy' THEN COUNT([PayrollNo]) 
      WHEN 'Planner/Counter' THEN COUNT([PayrollNo]) 
      WHEN 'MB Driver Only' THEN COUNT([PayrollNo]) 
      WHEN 'Traveller' THEN COUNT([PayrollNo]) 
      WHEN 'Trainer' THEN COUNT([PayrollNo]) 
      WHEN 'Planner/Checker' THEN COUNT([PayrollNo]) 
      WHEN 'Site Visitor' THEN COUNT([PayrollNo]) 
      WHEN 'Pharm Counter' THEN COUNT([PayrollNo]) 
      WHEN 'Pharm Traveller' THEN COUNT([PayrollNo]) 
      WHEN 'Wilk Estimator' THEN COUNT([PayrollNo]) 
      WHEN 'Wilk Planner' THEN COUNT([PayrollNo]) 
      WHEN 'Wilk Checker' THEN COUNT([PayrollNo]) 
      WHEN 'Wool Core Team' THEN COUNT([PayrollNo]) 
      WHEN 'Water NSA' THEN COUNT([PayrollNo]) 
      WHEN 'Pharm Counter' THEN COUNT([PayrollNo]) 
     END AS StaffOther
WHERE (((tblAvailabilityAll.JobDate) >= GETDATE() - 30))
GROUP BY tblAvailabilityAll.JobNo, 
    tblAvailabilityAll.lblStaffingCode
HAVING (((tblAvailabilityAll.JobNo) IS NOT NULL))





Message date : Jan 13 2005, 02:44 PM
>From : "Mackin, Christopher" 
To : dba-sqlserver at databaseadvisors.com
Copy to : 
Subject : RE: RE: [dba-SQLServer] SQL Views Code For IIF
You can, but you just can't see them graphically once you do it, but they're perfectly fine in Views.

-Chris Mackin

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, January 13, 2005 7:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: RE: [dba-SQLServer] SQL Views Code For IIF


Didn't think you could use the case statement in Views ?





Message date : Jan 13 2005, 02:40 PM
>From : "Mackin, Christopher" 
To : dba-sqlserver at databaseadvisors.com
Copy to : 
Subject : RE: [dba-SQLServer] SQL Views Code For IIF
Lok up the CASE statement for T-SQL, it provides the IIF functionality.

-Chris Mackin

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, January 13, 2005 2:45 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Views Code For IIF


To all,
I have the following code in an Access database:
SELECT tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode, IIf([lblStaffingCode]="Manager" Or [lblStaffingCode]="Supervisor",Count([PayrollNo]),0) AS StaffMan, IIf([lblStaffingCode]<>"Manager" And [lblStaffingCode]<>"Supervisor",Count([PayrollNo]),0) AS StaffOther
FROM tblAvailabilityAll
WHERE (((tblAvailabilityAll.JobDate)>=Date()-30))
GROUP BY tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode
HAVING (((tblAvailabilityAll.JobNo) Is Not Null))

How can I put this into a view in SQL Server 7.0, The only thing I'm sure of is changing the Date()-30 to GETDATE()-30 and changing the " (double quotes) to ' (single quotes)

Thanks for any help in advance

Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm