Mackin, Christopher
CMackin at quiznos.com
Thu Jan 13 09:43:08 CST 2005
What is the exact error message? When it says it's not supported it may be saying that it cna't be viewed graphically in Enterprise Manager. Are you writing this in an .adp, Enterpriose Manager, Query Analyzer or other?
-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 8:36 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: RE: RE: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com