Different result in Query vs. View

Question

Different result in Query vs. View

tnpich

Ten Centuries

Points: 1223
More actions
January 16, 2012 at 10:17 am

#251409

Somewhat of a basic question I think but...
If I run this in a query window, I get the results I expect:
SELECT a.RC_ID,
a.SM_USPName AS ImportProcedure,
CONVERT(date, a.RC_RunStartDate) AS ImportDate,
COUNT(*) AS ExceptionCount
FROM dbo.tC_USPRunCtl AS a
INNER JOIN dbo.tE_ImportExceptions AS b ON
a.SM_USPName = b.tE_ImportProc AND
CONVERT(date, a.RC_RunStartDate) <= b.tE_ImportDt AND
CONVERT(date, a.RC_RunEndDate) >= b.tE_ImportDt
GROUP BY a.RC_ID, a.SM_USPName, a.RC_RunStartDate
ORDER BY ImportProcedure
If I try to set this up as a view for others to run as required, I get an error message saying Cannot call methods on date.
In the Executed SQL statement it reads
SELECT TOP (100) PERCENT a.RC_ID, a.SM_USPName AS ImportProcedure, CONVERT(date, a.RC_RunStartDate).ToString() AS ImportDate, COUNT(*) AS ExceptionCount...
Why does the query add the .ToString and not allow the query to run?
TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

At a guess, I would say you are using the graphical designer to create a view in SQL Server Management Studio. It's a guess, because I've never really used it. Try running something like this is a query window instead:

GO

CREATE VIEW dbo.MyViewName

AS

SELECT

a.RC_ID,

a.SM_USPName AS ImportProcedure,

CONVERT(date, a.RC_RunStartDate) AS ImportDate,

COUNT(*) AS ExceptionCount

FROM dbo.tC_USPRunCtl AS a

INNER JOIN dbo.tE_ImportExceptions AS b ON

a.SM_USPName = b.tE_ImportProc

AND CONVERT(date, a.RC_RunStartDate) <= b.tE_ImportDt

AND CONVERT(date, a.RC_RunEndDate) >= b.tE_ImportDt

GROUP BY

a.RC_ID, a.SM_USPName, a.RC_RunStartDate;

GO

Then:

SELECT

*

FROM dbo.MyViewName

ORDER BY

ImportProcedure;

See CREATE VIEW

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

tnpich Ten Centuries Points: 1223 More actions · Answer 2

Yes, I was using the graphical designer. I guess I shouldn't depend on it.

It did work creating the view that way.

Thanks

Paul White SSC Guru Points: 150468 More actions · Answer 3

tnpich (1/16/2012)
Yes, I was using the graphical designer. I guess I shouldn't depend on it.
It did work creating the view that way.

The view designer doesn't get much love from Microsoft...this is just a selection of old (and very old) bugs that have not been fixed yet. The first three are most relevant to you. If you feel strongly that the (very silly) toString thing should be fixed, this is where to vote.

http://connect.microsoft.com/SQLServer/feedback/details/693013/ssms-cannot-cast-convert-to-bigint-in-view-designer

http://connect.microsoft.com/SQLServer/feedback/details/249248/management-studio-generates-invalid-top-100-percent-order-by-in-views

http://connect.microsoft.com/SQLServer/feedback/details/126056/simple-and-valid-query-using-integer-fails-in-view-designer-same-query-with-int-succeeds

http://connect.microsoft.com/SQLServer/feedback/details/676137/sql-server-2008-management-studio-view-designer-reshuffle-conditions

http://connect.microsoft.com/SQLServer/feedback/details/317249/sql2008-ctp5-view-designer-allows-specification-of-sort-order

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

My 2 cents on query designers. I'm not a huge fan of them for SQL Server. They are about the only place I've ever seen a RIGHT JOIN created. In 12 years of writing queries I've never actually written a query with a RIGHT JOIN. My mind doesn't work that way. I've only ever seen another programmer write a RIGHT JOIN twice in that time.

I tried the View designer all of once. I found that the time spent messing with things to get good clear SQL code generated wasn't worth the effort.

Todd Fifield