Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jennypretty
Yak Posting Veteran
96 Posts |
 Posted - 2004-10-04 : 15:15:00
|
| Hello friends, Jenny got great help from this site. Jenny has a new question:This is an Access query:SELECT empid,1* sum(Iff(nz(Emp.emp_hours),IIf(nz(Sal_Vw.em_hours_total),IIf(nz(Sal_Emp_Vw.emp_hours),0,Sal_Emp_Vw.emp_hours, Sal_Vw.emp_hours_total, Emp.emp_hours AS emp_sal_hoursFROM blah blah...GROUP BY bla blah blah....This is a SQL view:CREATE VIEW Emp_View ASSELECT empid,1* sum(Iff(ISNULL(Emp.emp_hours),0,Emp.emp_hours),ISNULL(Sal_Vw.em_hours_total),0,Sal_Vw.emp_hours_total),IIf(ISNULL(Sal_Emp_Vw.emp_hours),0,Sal_Emp_Vw.emp_hours)) AS emp_sal_hoursFROM blah blah...GROUP BY bla blah blah....However, it shows error at line 3 "The Sum function requires 1 arguments."PLEASE HELP.THANKS.The stupid question is the question you don't ask.www.single123.com |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-04 : 15:35:37
|
| [code]Access SQL------- ------Isnull(a) a is NullIIF(a,b,c) CASE WHEN a THEN b ELSE c ENDNZ(a,b) ISNULL(a,b)IIF(a is null,0,a) ISNULL(a,0)[/code]- Jeff |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-04 : 15:35:48
|
1. sql server doesn't support IIF use case when2. please doon't address yourself in 3rd person.... it reads creepy...  sum takes only one argument. so if i understand correctly, you want to sum 3 columns together.will this help?CREATE VIEW Emp_View ASSELECT empid, sum(isnull(Emp.emp_hours, 0) + ISNULL(Sal_Vw.em_hours_total, 0) + ISNULL(Sal_Emp_Vw.emp_hours,0)) as emp_sal_hoursFROM blah blah...GROUP BY bla blah blah.... Go with the flow & have fun! Else fight the flow  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-04 : 15:37:45
|
| Jeff hopes he has helped- Jeff |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-04 : 15:45:30
|
LOL!!  Spirit1 too...Go with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
|
|
create SQL VIEW from ACCESS query, SUM function???