View with errors still saved, and still works

Question

View with errors still saved, and still works

pdanes

SSCrazy Eights

Points: 8502
More actions
February 25, 2014 at 1:04 am

#282793

I have the following saved as a view:
SELECT
STUFF((
SELECT ',' + TC.BPAuthorAbbreviation
FROM VazbyLidiTaxon VLTI
inner join TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.TypVazby = 'TA' and VLTI.TaxaAutoID = VLTO.TaxaAutoID
FOR XML PATH('')),1,1,'') AuthorString
FROM VazbyLidiTaxon VLTO
group by VLTO.TaxaAutoID
order by AuthorString
Whenever I save it, or even open it in design view, I get the following errors:
Error in WHERE clause near '('.
Error in WHERE clause near 'AuthorString'.
Error in WHERE clause near 'GROUP'.
Unable to parse query text.
However, the query IS saved, and DOES return correct results when executed. What gives?

Viewing 7 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

The design view is a piece of crap that barely works and only supports a subset of the T-SQL language.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pdanes SSCrazy Eights Points: 8502 More actions · Answer 2

So if the query gives me what I need, I should ignore whatever the design view says? I'm kind of leery about ignoring warning messages - I always wonder if it will bite me somewhere later, especially in something like this, where I patched the query together from bits and pieces, by trial and error, and don't completely understand how it works.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

pdanes (2/25/2014)
So if the query gives me what I need, I should ignore whatever the design view says?

Better yet, avoid design view entirely.

I'm kind of leery about ignoring warning messages - I always wonder if it will bite me somewhere later, especially in something like this, where I patched the query together from bits and pieces, by trial and error, and don't completely understand how it works.

Well the implementing something you don't understand is a problem, how do you fix it if it breaks, but the warnings/errors here are because design view is a half written piece of junk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pdanes SSCrazy Eights Points: 8502 More actions · Answer 4

So far, it's only in my development system, and I'm trying to learn every aspect of how it works, before I send it out into the real world to fend for itself.

I built the query by hand, and dropped it into design view in order to save it as a view, instead of typing the 'Create View...' business. I'm a horrible typist, and partial to graphic methods in general, so right-clicking New View and pasting in the SELECT statement is easier for me.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Right-click on the view and select 'Script view as...', see if it's what you are execting.

Why would you want to convert this into a view?

Why does it have an ORDER BY? (funny this doesn't come up as one of the errors)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

pdanes (2/25/2014)
So if the query gives me what I need, I should ignore whatever the design view says? I'm kind of leery about ignoring warning messages - I always wonder if it will bite me somewhere later, especially in something like this, where I patched the query together from bits and pieces, by trial and error, and don't completely understand how it works.

It might be a little easier to understand with some of the superfluous noise removed:

SELECT

VLTO.TaxaAutoID,

AuthorString = STUFF(

(SELECT ',' + TC.BPAuthorAbbreviation

FROM TableOfCloveks TC

WHERE TC.ClovekAutoID = VLTO.ClovekAutoID

FOR XML PATH(''))

,1,1,'')

FROM VazbyLidiTaxon VLTO

WHERE VLTO.TypVazby = 'TA'

GROUP BY VLTO.TaxaAutoID

ORDER BY AuthorString

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden