[dba-SQLServer] SQL Views Code For IIF

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




More information about the dba-SQLServer mailing list