January 11, 2013 at 2:14 am
Hi All
I moved a simple database from SQL Server 2000 to SQL server 2008. It has a couple of Tables and a couple of views.
I have a view using the following statement.
SELECT DISTINCT
TOP (100) PERCENT PERSONID AS EmpID, CONVERT(numeric, PERSONNUM) AS ClockNo, FIRSTNM + ' ' + LASTNM AS EmpName, EMPLOYMENTSTATUS,
HOMELABORLEVELNAME3
FROM KronosWFC.dbo.VP_EMPLOYEE
WHERE (EMPLOYMENTSTATUS = 'Active')
ORDER BY ClockNo
The above works perfectly, however if I modify it slightly like below.
SELECT DISTINCT
TOP (100) PERCENT PERSONID AS EmpID, CONVERT(numeric, PERSONNUM) AS ClockNo, FIRSTNM + ' ' + LASTNM AS EmpName, EMPLOYMENTSTATUS,
HOMELABORLEVELNAME3
FROM KronosWFC.dbo.VP_EMPLOYEE
WHERE (EMPLOYMENTSTATUS = 'Active') AND (CONVERT(numeric, PERSONNUM) = 722)
ORDER BY ClockNo
I get an SQL error "Error converting data type nvarchar to numeric"
Now the above view worked perfectly in SQL 2000 but not in SQL 2008, I just cant figure out what it is. At first I thought maybe it was because one of the rows could not be converted to a numer but I would have assumed the first view wouldnt work either. Also I have checked all the rows and they are all numbers.
Any help would be appreciated.
Regards
Douglas Bell
January 11, 2013 at 2:37 am
That is a funny one, as the convert is the same in the select and the where, so both should fail.
Would it be possible to post the DDL (CREATE TABLE script) form VP_EMPLOYEE and also some dummy data, to see if I can repro the error.
January 11, 2013 at 5:26 am
It's probably because the SELECT criteria only converts the information returned, but the WHERE clause, since it's a function on a column which means a complete scan of the table (and a very bad idea), it must resolve the values. So you may not see the error from the SELECT, but you're guaranteed to always see it in the WHERE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 5:29 am
Sorry to possibly add to your troubles but you may have more than one problem with your View.
The "Select Top 100 Percent" method was commonly used in previous versions of sql server to retrieve ordered
results from Views. I don't believe this works in newer versions of Sql Server as the optimizer seems to just ignore the request to Sort.
You can easily test it by viewing the plan of a call to your View.
If the order of rows is important, you may need to order the rows in an outer query as you retrieve them from the View.
January 11, 2013 at 6:15 am
What is the datatype of PERSONNUM?
You can change this in the WHERE clause:
WHERE (EMPLOYMENTSTATUS = 'Active') AND (CONVERT(numeric, PERSONNUM) = 722)
to this, where DATATYPE is the datatype of PERSONNUM, e.g. VARCHAR(20) or NVARCHAR(50), which will make that part a SARG:
WHERE (EMPLOYMENTSTATUS = 'Active') AND (PERSONNUM = CAST('722' AS DATATYPE))
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2013 at 12:02 pm
Grant is right - you need to check your data types.
Also, you cannot have an ORDER BY clause in a view. SQL 2000 tolerated it but the later versions do not.
If you need one, you have to include the TOP n clause in the SELECT statement.
January 14, 2013 at 12:18 pm
Ed Wagner (1/14/2013)
If you need one, you have to include the TOP n clause in the SELECT statement.
...or TOP (n) PERCENT
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2013 at 12:24 pm
Ed Wagner (1/14/2013)
Grant is right - you need to check your data types.Also, you cannot have an ORDER BY clause in a view. SQL 2000 tolerated it but the later versions do not.
If you need one, you have to include the TOP n clause in the SELECT statement.
It will only guarantee which rows and NOT guarantee the output order. If you want an order to the result the ONLY way to ensure that is to have an order by on the query, not the view.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2013 at 3:21 am
Hi All
Sorry for the late reply, first of me getting a chance to look at this particular database.
PERSONNUM is NVARCHAR(15)
I have taken away the order by clause as to be honest its not really needed as the application front end does the sorting anyway.
I have tried multiple things converting both ClockNo and 722 to numeric, converting the 722 to NVARCHAR(15) I still get the error that it cannot convert.
Regards
Douglas Bell
January 15, 2013 at 6:43 am
What does this query yield?
SELECT DISTINCT
PERSONID AS EmpID,
CONVERT(NUMERIC, 722) AS ClockNo, -- we know the ClockNo is 722 per the WHERE-clause
FIRSTNM + ' ' + LASTNM AS EmpName,
EMPLOYMENTSTATUS,
HOMELABORLEVELNAME3
FROM KronosWFC.dbo.VP_EMPLOYEE
WHERE EMPLOYMENTSTATUS = 'Active'
AND PERSONNUM = CAST('722' AS NVARCHAR(15));
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 15, 2013 at 7:30 am
Hi opc.three
Perfect that worked, I just had to modify it slightly to change the 722 to @SearchNo which was a parameter.
Cheers
Douglas Bell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply