Mackin, Christopher
CMackin at quiznos.com
Thu Jan 13 09:51:21 CST 2005
EM will always say that it's not supported, but that just means you can't view it graphically, drop the same code into Query Analyzer and run it with CREATE VIEW dbo.MyViewName AS on top of the current SQL and you should be fine. Clicking OK in EM should also make the table disappear graphically, but should still work fine. -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:48 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: RE: RE: RE: [dba-SQLServer] SQL Views Code For IIF Using Enterprise Manager - Create New View: The following errors were encountered while parsing the contents of the SQL pane CASE is not supported and just have an OK and HELP button Message date : Jan 13 2005, 03:43 PM >From : "Mackin, Christopher" To : dba-sqlserver at databaseadvisors.com Copy to : Subject : RE: RE: RE: [dba-SQLServer] SQL Views Code For IIF 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 _______________________________________________ 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