|
hi to all
i have a table that product ID is key and clustered .
i create this view on my table if i not use of cluster index on view whats happen?
CREATE VIEW View3 WITH SCHEMABINDING AS
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)
note to this point that Product ID also Cluster index in base table
thanks for any help
|
|
|
|
|
|
|
Hi
Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
I only want rows where the sum of the item qty is Different in the two tables
The line:
WHERE tStk.[difference] <> 0 does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference]
I also tried
WHERE sum(tStk.Qty) <> tHST.Qty but aggregate functions are not allowed in a WHERE clause.
|
|
|
|
|
Try taking your existing query and make it a sub-query.
Some thing like:
Select * from ("put your query here") XX
where XX.tStk.Qty <> tHST.Qty
Remember to remove your "Where" clause from your inner query.
|
|
|
|
|
Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'
|
|
|
|
|
|
Awesome! Thanks Jörgen
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
HAVING sum(tStk.Qty) <>tHst.HstTot
I see that:
Aggregate functions can be used as expressions only in the following:
The select list of a SELECT statement (either a subquery or an outer query).
A COMPUTE or COMPUTE BY clause.
A HAVING clause.
|
|
|
|
|
whate is the different between two phase commit in SQL server and Oracl ???? :
modified 31-Mar-13 13:37pm.
|
|
|
|
|
|
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query.
Using MSSQL 2008R2.
Thanks
Eric
|
|
|
|
|
You may have misunderstodd, or he's wrong.
I've never heard of inserting into views, a view is essentially readonly, you have no idea how the data feeding it is structured -- one of the benefits of views. Then again databases have advanced quite a bit in the last twenty years and I may be behind the times. It may be that some versions of some databases support it in some cases, but I would never do it.
|
|
|
|
|
I had the same thoughts. You "can" do it in MS SQL 2008R2 but i never have... It seems to me to be a really bad practice as the view can (and does in my case) span multiple containers & servers.
Eric
|
|
|
|
|
Eric Whitmore wrote: a really bad practice
Yes, it is. It's got to be less efficient as the engine has to figure out how to do it and in a great many cases it simply can't. Plus it's not database agnostic, so why go to the trouble when inserting to tables works everywhere?
"Simplify. Simplify." -- Thoreau
|
|
|
|
|
I recently tested updates on a mock view, in SQL Server 2008, which contained a join between two tables.
I did this because where I work the majority of what our users see is through views and they occasionally need to update what they see.
I read up on it beforehand and all the documentation pointed to this being possible as long as it was unambiguous as to which row in which table was being updated.
The update did work - I then tried to break it and succeeded by using an aggregate function in the view so that there was ambiguity in what would be updated.
It can be useful to insert or update a view, since if one is passing a view into a control then it is easier to perform an update, on the source of that control, than to have to attach lots of meta data documenting which tables to update.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: passing a view into a control
But that's not a good idea. It doesn't lead to a properly layered application.
GuyThiebaut wrote: aggregate function
Or a UNION or a CASE , probably if there's CAST or CONVERT ... I suppose it requires that there be a primary key as well.
|
|
|
|
|
"But that's not a good idea. It doesn't lead to a properly layered application." - I don't understand why using a view in a control would do this, could you let me know why?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: I don't understand why using a view in a control would do this, could you let me know why?
Because if you use the view in the GUI then there is no database layer. Nor presumably a business layer for that matter.
|
|
|
|
|
There is a database layer - the control is populated via a query from this layer.
Any updates to this view are then passed through this layer via update commands.
So we can lock down particular columns and perform verification on the values before update so that we both have a business and database layer.
So I am still not getting what is wrong with using the results of a view in a GUI.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: Any updates to this view are then passed through this layer via update commands.
Either you are expressing it incorrectly or there is no layer.
You said you used the "view" in the "control".
If instead you are using a DTO in the "control" then that is not the same as what you said.
If however you are using a language specific layer to access the a view in the control then there is no database layer.
GuyThiebaut wrote: So I am still not getting what is wrong with using the results of a view in a GUI.
First, the question, per the OP, is why someone would insist that only views be used. It isn't whether views might or might not be used.
Second, as I already said your terminology/phrasing is not precise. If you have a database layer then you are not using the view in the GUI. What you are using is the results of the database layer or even business layer in the GUI. The fact that they originated from a view in another layer is irrelevant and expressing it that way should be avoided because data model entity might not originate in a one to one mapping with the database. And the user (GUI) should not concern itself with how it did originate from he database.
|
|
|
|
|
GuyThiebaut wrote: since if one is passing a view into a control then it is easier to perform an updat
Control? As in a GUI control?
For small systems with stand along apps that might be appropriate. For anything larger one should have a database layer.
And of course it won't work at all for a web app.
|
|
|
|
|
Since a view can become out of sync with the underlying table, it is not a good idea to insert through a view, [sarcasm]unless you are using MS Access[/sarcasm].
Another problem is, if there are multiple tables in the view things go wrong quickly.
Now if he would have said you should use a stored procedure to do the insert then I would say he has a point. However it is not wrong to insert directly into a table.
|
|
|
|
|
Eric Whitmore wrote: if I do "inserts" into "tables" I am doing it "wrong
You should ask why. There could be an architectural reason such as an intent to move to another schema or legacy reasons.
Could just be an odd way to provide a database layer though. For that though I use stored procs.
|
|
|
|
|
jschell wrote: I use stored procs
Right, I don't (willingly), but I know many do and, of the two techniques, procedures make more sense.
|
|
|
|
|
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement.
PieBald
You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
