Query Problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query Problem

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-18 : 17:56:42

Hi all,

Here is the query that I have

SELECT     TOP (100) PERCENT dbo.[vIssueType&Time].IssueTypeID, dbo.[vIssueType&Time].IssTypDescription, SUM(dbo.[vIssueType&Time].Tim) 
                      AS ServiceTime
FROM         dbo.[vIssueType&Time] RIGHT OUTER JOIN
                      dbo.[vIssueType&Time] AS [vIssueType&Time_1] ON dbo.[vIssueType&Time].ServiceDate = [vIssueType&Time_1].ServiceDate
WHERE     ([vIssueType&Time_1].ServiceDate BETWEEN '9/1/2008' AND '9/18/2008')
GROUP BY dbo.[vIssueType&Time].IssueTypeID, dbo.[vIssueType&Time].IssTypDescription
HAVING      (SUM(dbo.[vIssueType&Time].Tim) IS NOT NULL)
ORDER BY dbo.[vIssueType&Time].IssTypDescription, ServiceTime

I get this error when I run it:
Error converting data type nvarchar to numeric!!!
What is wrong here? When I remove this line everything works well!

WHERE     ([vIssueType&Time_1].ServiceDate BETWEEN '9/1/2008' AND '9/18/2008')

In vIssueType&Time_1 view ServiceDate has a datetime data type.

Sep

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 18:11:14

May be your dateformate wrong.
try this: SET DATEFORMAT mdy
what is the datatype of ([vIssueType&Time_1].ServiceDate ?

hey

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-18 : 18:14:58

Thanks for reply.
the datatype of ([vIssueType&Time_1].ServiceDate is Datetime.

Sep

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 18:20:19

is working now?

hey

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-18 : 18:22:33

no.I checked that.It is not working yet.

Sep

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 18:29:03

Try putting the dates in in a format that's not ambiguous

BETWEEN '2008/09/01' AND '2008/09/18'

--
Gail Shaw
SQL Server MVP

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-18 : 18:32:01

The problem is it works sometimes and I can't find the problem.

Sep

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 18:32:21

Wait a sec... nvarchar and numeric? I don't see any of either in that query
Are those tables or are they views?

Couple other comments:
The TOP is unnecessary and SUM(<expression>) will never be null unless there are no records in the rowset been summed, so the having clause will not eliminate any rows.

--
Gail Shaw
SQL Server MVP

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 18:32:48

Any other field type got nvarchar? try to run the sql just for 1 day. Some of your records return numeric value instead nvarchar.

hey

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-18 : 18:49:36

I tried different period of time like 1/1/2000 to 09/18/2008 and it works well but when I tried 9/1/2008 to 09/18/2008 I got the error again.

I am really confused by the result .9/1/2008 to 09/18/2008 is in the like 1/1/2000 to 09/18/2008.

Sep

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 19:07:27

some where your data got wrong. find out the date and fix the data.

hey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 00:22:43

whats the datatype of Tim column?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-19 : 04:12:56

Are those views or tables?
If views, can you post the query that defines the view?
If tables, can you post the create table statements?

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-19 : 04:43:22

Change the WHERE to AND because you have an OUTER JOIN.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 06:00:39

quote:
Originally posted by Peso

Change the WHERE to AND because you have an OUTER JOIN.

E 12°55'05.63"
N 56°04'39.26"

or add

.. OR [vIssueType&Time_1].ServiceDate IS NULL

to current where condition

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-19 : 16:44:15

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

Change the WHERE to AND because you have an OUTER JOIN.

E 12°55'05.63"
N 56°04'39.26"

or add
.. OR [vIssueType&Time_1].ServiceDate IS NULL

to current where condition

No!

A very common mistake, but you shouldn't do that -- it is not the same as putting the criteria in the JOIN. See: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx for details on this.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-19 : 16:45:18

Also -- use LEFT OUTER JOINS, not RIGHT OUTER JOINS, it makes your code much clearer and easier to read and work with.

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-20 : 01:45:03

quote:
Originally posted by jsmith8858

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

Change the WHERE to AND because you have an OUTER JOIN.

E 12°55'05.63"
N 56°04'39.26"

or add
.. OR [vIssueType&Time_1].ServiceDate IS NULL

to current where condition

No!

A very common mistake, but you shouldn't do that -- it is not the same as putting the criteria in the JOIN. See: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx for details on this.

- Jeff
http://weblogs.sqlteam.com/JeffS

yup i understand they are different

thanks for pointing out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources