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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Replacements needed for SQL Query

Author  Topic 

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 01:04:01
I am posting my query, can anyone suggest what should I replace here to get it working in SQL. Present query is in MSAccess :
SELECT People.People, People.Name,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/8/2004#,1,0))) AS Created,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/8/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))) AS Complete,
People.email FROM (((Register INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status)
LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project)
INNER JOIN StakeHolders ON Register.Register = StakeHolders.Register)
INNER JOIN People ON StakeHolders.People = People.People WHERE (((SR_Status.Status)='Active')
AND ((StakeHolders.Status)=Yes)) GROUP BY People.People, People.Name, People.email
HAVING (((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/8/2004#,1,0))))>0)
AND ((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/8/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))))>0)) ORDER BY People.People

Its a big query but you need to check where I got the errors :
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '!'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Register'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'SR_Status'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Sum'.

! should be replaced by ., similarly, I need to know for IIF, IsNull, Sum......

Regards


Cheers

ditch
Master Smack Fu Yak Hacker

1466 Posts
Posted - 2004-10-14 : 01:11:51
For one date literals should be between '' <- Single Quotes and should not have the '#'.
The sum and other aggregates look ok.
I think the IIF's need to be replaced by a Case statement - Look up Case in Books Online.


Duane.
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 01:28:50
thanks duane, let me give it a try, I somehow tried so many ways, but everytime I come across some backlog, will get back if issues are open :)

Cheers
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 01:51:23
hi, I have this problem now, can anyone have a look,

SELECT People.People, People.Name,
Sum(Case(IsNull(ProjectStatusReport.Week),0,Case(ProjectStatusReport.Week='10/8/2004',1,0))) AS Created,
Sum(Case(IsNull(ProjectStatusReport.Week),0,Case(ProjectStatusReport.Week='10/8/2004',Case(IsNull(ProjectStatusReport.Accomplishments)
Or IsNull(ProjectStatusReport.Plans),0,1),0))) AS Complete, People.email FROM (((Register INNER JOIN SR_Status
ON Register.SR_Status = SR_Status.SR_Status) LEFT JOIN ProjectStatusReport
ON Register.Register = ProjectStatusReport.Project) INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register) INNER JOIN People
ON StakeHolders.People = People.People WHERE (((SR_Status.Status)='Active') AND ((StakeHolders.Status)=Yes))
GROUP BY People.People, People.Name, People.email HAVING
(((Sum(Case(IsNull(ProjectStatusReport.Week),0,Case(ProjectStatusReport.Week='10/8/2004',1,0))))>0)
AND ((Sum(Case(IsNull(ProjectStatusReport.Week),0,Case(ProjectStatusReport.Week='10/8/2004',Case(IsNull(ProjectStatusReport.Accomplishments)
Or IsNull(ProjectStatusReport.Plans),0,1),0))))>0)) ORDER BY People.People

The error is :
Server: Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 arguments.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'Register'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'SR_Status'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Sum'.

regards

Cheers
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts
Posted - 2004-10-14 : 02:02:01
isnull is used like this:

isnull(Col1, 0) <-- That treats nulls as 0

case is used like this

select case when branchcode < 100 then 'Yes' else 'No' end as OldBranch

Hopefully this helps more


Duane.
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 02:33:29
hmm, Duane, I think I am loosing, I am not finding any solution, tried by removing case and putting braces correctly for case and now the query looks like this, let me know if I need to do any modifications :
SELECT People.People, People.Name,
Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',1,0)) AS Created,
Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',IsNull(ProjectStatusReport.Accomplishments)
Or IsNull((ProjectStatusReport.Plans),0),1,0)) AS Complete, People.email FROM (((Register INNER JOIN SR_Status
ON Register.SR_Status = SR_Status.SR_Status) LEFT JOIN ProjectStatusReport
ON Register.Register = ProjectStatusReport.Project) INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register) INNER JOIN People
ON StakeHolders.People = People.People WHERE (((SR_Status.Status)='Active') AND ((StakeHolders.Status)=Yes))
GROUP BY People.People, People.Name, People.email HAVING
(((Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',1,0)))>0)
AND ((Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',IsNull(ProjectStatusReport.Accomplishments)
Or IsNull((ProjectStatusReport.Plans),0),1,0)))>0)) ORDER BY People.People

Errors :
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'Register'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'SR_Status'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Sum'.

Regards

Cheers
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-14 : 02:36:28
follow the syntax errors and trace them by line...

just a hint on debugging...

--------------------
keeping it simple...
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 02:38:25
i am on that........ :)

Cheers
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts
Posted - 2004-10-14 : 02:51:37
quote:
Originally posted by sinapra

Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',1,0)) AS Created,
Sum(IsNull((ProjectStatusReport.Week),0),(ProjectStatusReport.Week='10/8/2004',IsNull(ProjectStatusReport.Accomplishments)
Or IsNull((ProjectStatusReport.Plans),0),1,0)) AS Complete,


What are you trying to do in these functions? and why do you use so many paranthesis "()" ? This only makes the code look complex and difficult to debug.

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 03:15:29
see, I will repeat my question, the whole application was built using Access DB, but some of them havenot been changed. I am trying to change these queries to SQL compatible mode.
let me know if you have any idea on the replacements!!!

Cheers
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-14 : 05:10:43
something like this?


SELECT 	People.People, People.Name, 
	Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created, 
	Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)	+
	sum(isnull(ProjectStatusReport.Accomplishments, 0)) + 
	sum(isnull(ProjectStatusReport.Plans, 0)) AS Complete, 
	People.email 
FROM 	Register 
	INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status 
	LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project 
	INNER JOIN StakeHolders ON Register.Register = StakeHolders.Register
	INNER JOIN People ON StakeHolders.People = People.People 
WHERE 	(SR_Status.Status = 'Active') AND 
	(StakeHolders.Status = 'Yes')  -- if this is a bit field it should be (StakeHolders.Status = 1)
GROUP BY People.People, People.Name, People.email 
HAVING 	(Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) > 0) AND 
	(Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)	+
	sum(isnull(ProjectStatusReport.Accomplishments, 0)) + 
	sum(isnull(ProjectStatusReport.Plans, 0)) > 0)
ORDER BY People.People


Go with the flow & have fun! Else fight the flow
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 06:26:08
hi spirit, well said and thanks for taking out time and write down this for me, I am coughing now and still trying, it somehow shows this now :

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type as an argument.

any hint.........

Cheers
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-14 : 06:32:19
convert the fields in red to numeric type...

quote:
Originally posted by spirit1

something like this?


SELECT 	People.People, People.Name, 
	Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created, 
	Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)	+
	sum(isnull(ProjectStatusReport.Accomplishments, 0)) + 
	sum(isnull(ProjectStatusReport.Plans, 0)) AS Complete, 
	People.email 
FROM 	Register 
	INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status 
	LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project 
	INNER JOIN StakeHolders ON Register.Register = StakeHolders.Register
	INNER JOIN People ON StakeHolders.People = People.People 
WHERE 	(SR_Status.Status = 'Active') AND 
	(StakeHolders.Status = 'Yes')  -- if this is a bit field it should be (StakeHolders.Status = 1)
GROUP BY People.People, People.Name, People.email 
HAVING 	(Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) > 0) AND 
	(Sum(IsNull(ProjectStatusReport.Week, 0)) + 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)	+
	sum(isnull(ProjectStatusReport.Accomplishments, 0)) + 
	sum(isnull(ProjectStatusReport.Plans, 0)) > 0)
ORDER BY People.People


Go with the flow & have fun! Else fight the flow


--------------------
keeping it simple...
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts
Posted - 2004-10-14 : 06:35:17
It is because the first (underlined) argument returns a date if it is not null (obviously, ProjectStatusReport.Week is a datetime datatype) and the second (bold) argument is an integer.

Sum(IsNull(ProjectStatusReport.Week, 0)) +
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts
Posted - 2004-10-14 : 06:48:21
Sum(IIf(IsNull([ProjectStatusReport]![Week]), 0, IIf([ProjectStatusReport]![Week]=#10/8/2004#,1,0))) AS Created,
in Access equals to
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created
in SQL

So,
I'm guessing... this is probably all you need...

SELECT 	People.People, People.Name, 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created, 
	sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)
	 + sum(isnull(ProjectStatusReport.Accomplishments, 0)) 
	 + sum(isnull(ProjectStatusReport.Plans, 0)) AS Complete, 
	People.email 
FROM 	Register 
	INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status 
	LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project 
	INNER JOIN StakeHolders ON Register.Register = StakeHolders.Register
	INNER JOIN People ON StakeHolders.People = People.People 
WHERE 	(SR_Status.Status = 'Active') AND 
	(StakeHolders.Status = 'Yes')  
GROUP BY People.People, People.Name, People.email 
HAVING 	(sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) > 0) AND 
	(sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)	
	  + sum(isnull(ProjectStatusReport.Accomplishments, 0))
	  + sum(isnull(ProjectStatusReport.Plans, 0)) > 0
        )
ORDER BY People.People


Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 07:10:17
Hemanth, Its incomplete for my requirements, since I need the sum of :

SELECT People.People, People.Name,
Sum(IsNull(ProjectStatusReport.Week, 0)) +
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created,.......

Let me know how to convert the first input to int since the output is not matching to the return value of second value.

Cheers
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts
Posted - 2004-10-14 : 07:23:32
Sinapra, Sum(IsNull(ProjectStatusReport.Week, 0)) is incorrect. Lets analyse this....

ISNULL() does not work the same way as in Aceess. In SQL when you use ISNULL(arg1,arg2) what it means is; if arg1 is null, then return arg2, else return arg1.
Ideally, both arg1 and arg2 should be of the same datatype, but somehow SQL server does not show it as an error. Instead SQL considers 0 in your case as '1900-01-01'.

Like I said, the SQL Server equivalent of Sum(IIf(IsNull([ProjectStatusReport]![Week]), 0, IIf([ProjectStatusReport]![Week]=#10/8/2004#,1,0))) is sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)

The query I've given you is after analysing you original Access query. Have you compared the results in Access and SQL?


Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-14 : 07:25:51
what i don't get is why do you want to sum a date column -> ProjectStatusReport.Week.
i mean what type is it?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts
Posted - 2004-10-14 : 07:29:24
Hi Spirit,
I don't think he does, atleast, looking at his original Access query.

But I'm guessing he got that idea from your post

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-14 : 07:33:22
yeah... didn't see
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)
in your solution...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

sinapra
Starting Member

24 Posts
Posted - 2004-10-14 : 07:44:15
in access the result is perfect, I got your point for sure, but the problem is I never worked in MSAccess, The other point is, I have to modify the already built query and the scripts which again is a tough job, I have done most of it, but few with complex queries are returning errors. I will try to analyze on what you said, I will post few more similar queries where conversions are required. If I get some solution here, I think I will win the match!!!

Still the modified query given by you returned few errors :
SELECT People.People, People.Name,
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end) AS Created,
sum(case when ProjectStatusReport.Week = '10/8/2004' then 1 else 0 end)
+ sum(isnull(ProjectStatusReport.Accomplishments, 0))
+ sum(isnull(ProjectStatusReport.Plans, 0)) AS Complete,
People.email ...........

error is :
Server: Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with ntext
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: ntext is incompatible with int
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: int is incompatible with ntext
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: ntext is incompatible with int
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: int is incompatible with ntext
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: ntext is incompatible with int
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: int is incompatible with ntext
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: ntext is incompatible with int
Server: Msg 279, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Server: Msg 279, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Server: Msg 279, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Server: Msg 279, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

hmmm


Cheers
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site