When I ran this SQL Query, I got an unexpected result.
//Sql Query
Code:
SELECT distinct Year FROM vw_EMD_Options_Pricing WHERE (Year >= '2003') AND (Year <= '2008')
The view is the "vw_EMD_Options_Pricing". The request I got is the year that go from 1981 to 2008. This is for MS-SQL 2000. Why does the SQL Query does that?
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies View RelatedA colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie
I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year.
tblPayments (ClientID, PmtID, PmtDate, PmtAmt)
How would I set the criteria for this?
How do I set up a sql statement to use as a view?
I want to use two tables but they are both from different databases.
From the Offices database I want to use a table called Office Code and only the Name column
The other database is called Library and I want to use a table called Requestors and join it to others.
I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database.
Create view GetDataview
select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name], Titles.title as Title from requestors
join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column?
I hope this makes sense
how can i make a view in my sqlexpress 2005 database that get data from oracle database or from RDB (old oracle database)
View 6 Replies View RelatedI am trying to write a view to retrieve data from
two different servers..
create view as
select c1 from ser1.database1.dbo.table1
union
select 1 from ser2.database2.dbo.table1
First it is not accepting to write it from EM, hence I wrote it from QA,
the problem when I am trying to execute it is giving following error
"Could not find server 'hercules' in sysservers"
Help me out what needs to be done?
i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.
ur help is appriciated.
I have a view which I am supposed to run manually first thing tomorrow morning but I will not be
in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.
How many maximum number of tables I can used to create the view in SQL 6.5..?
View 1 Replies View RelatedI have a database with a view to another table in another database which I created a form out of. I was wondering when if this rule applies
"Data Entry" property of the form is set to True. The default value for this property is "False" which means that Access opens the form and shows the existing records. However, if set to True, the Data Entry property of the form specifies that the form will only show a blank record.
The reason I am asking is because you cant see the records after you close the form and reopen it again.
Hi,
I have 2 databases, db1, db2 in the SQL Server. In db1, I created the view to link one table from db2. When I run it, it is just read-only, I want to edit/insert data in the view. Can we edit/insert the data in the view from db1(which linked table from db2)?
Hello Everyone,
When I write a sql joining more than views how will be executed?.
Does SQL Server execute each view and join the result set or interpret into one final sql and execute?
Thanks
Masanam
Hello All,
My sample data looks like this in SQL Server 2000
SubGr,Trno,Glcode,Amount,DrCr
----------------------------
Bank,1000,100001,1000.00,D
Other,1000,100012,1000.00,C
Other,1001,100010,1500.00,D
Other,1001,100010,1500.00,D
Bank,1001,100002,3000.00,C
Bank,1002,100001,2000.00,D
Bank,1002,100003,2000.00,C
Other,1003,100051,5000.00,D
Other,1003,100051,5000.00,C
I want a view which gives a output like this for those SubGr with 'Bank'.
Output
SubGr,Trno,Glcode,Amount,DrCr,Bank
----------------------------------
Other,1000,100012,1000.00,C,100001
Other,1001,100010,1500.00,D,100002
Other,1001,100010,1500.00,D,100002
Bank,1002,100001,2000.00,D,100003
Bank,1002,100003,2000.00,C,100001
Thanks in anticipation
Nirene
I want to create a view that has an if then statement within it, how is this done?
View 5 Replies View RelatedHi,
I have a view in my database which returns:
ID Date Area
1010712008F
1020712008F
1030712008F
I should change this view’s output in such a way that for each Id I should have 3 record:
IDDateAreaType
1010712008FLO
1010712008FPO
1010712008FSO
1020712008FLO
1020712008FPO
1020712008FSO
1030712008FLO
1030712008FPO
1030712008FSO
LO ,PO and SO are constant.
How should I do that?
Thanks.
i have a view that calls 4-5 functions and many joins,
the time to display data is around 10-11 mins,
how can i improve performance?
I have a small doubt.
Can we update a table through view.
Is there any limitation to upadate
Hi
How can i save a query as a view if query contains the case statement
asm
I've never worked with views before though I have a vaugue idea what they're for :)
I have a table that is quite large and growing fast and my primary use for the data is to run lots of aggregate functions on it.
See my previous post for an example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63748
Queries like that are taking 10 seconds to run.
So what I'm looking for is a way to store the results of a query like that, run the actual query maybe once an hour, and any time a user needs that data they get the results of the last run.
My first thought was to just have "summary" tables that get updated by a Stored Procudure that runs on a schedule under "Jobs". Is there a better way?
Thanks!
Edit: Some of the "summary" data would be based on date, so the summarized data would only need to be processed once. Don't know if that is something to consider.
Shahbaz writes "Hi
1.Can we delete the record using view.?
2.Can we insert and update the record through view?
Thanks and Regards,
Shahbaz"
Hey Guys,
I am trying to get 5 days back date and in my table the following 3 records are there but my query is not pulling. Could you please tell me what is wrong with my query? Thanks
select * from
dbo.tblTransaction
WHERE Performance_dm >= dateadd(day, datediff(day, 0, getdate()), - 5) AND Performance_dm < dateadd(day, datediff(day, 0, getdate()), -4)
order by Performance_dm
These are the records in the table but it is not pulling. How should i pull these records please?
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
2007-01-28 00:01:00.000
I have tried Books online and have queried this forum for assistance in creating a SQL view and have spent much time trying to solve on my own.
I am working with only one table:
Sales: SOP10200
SOPTYPE ITEMNMBR ITEMDESC QUANTITY
3 Widget special 4
3 Widget special 7
2 Gadget special 1
2 Widget special 12
3 Gadget special 5
I want to create a view that summarizes the data based upon SOPTYPE and ITEMNMBR as follows:
ITEM_NUMBER ITEM_DESC QTY_INVOICED QTY_ORDERED
Widget special 11 12
Gadget special 5 1
I am struggling whether to use a select statement or a CASE statement.
I am able to summarize one SOPTYPE as follows:
CREATE VIEW [dbo].[Tec_SOP10200_INVOICE_Qty]
AS
SELECT ITEMNMBR as ITEM_NUMBER, ITEMDESC as ITEM_DESC, SUM(QUANTITY) AS QTY_INVOICED
FROM SOP10200
WHERE SOPTYPE = 3
GROUP BY ITEMNMBR, ITEMDESC
But I need more than one soptype in same view
I tried this CASE statement -
Create tec_sales_view
as
SELECT SOP10200.ITEMNMBR as ITEM_NUMBER
, SOP10200.ITEMDESC as ITEM_DESC
, SOP10200.QTY1 'QTY_INVOICED'
, SOP10200.QTY2 'QTY_ORDERED'
, (CASE
WHEN SOP10200.SOPTYPE = 3 THEN SUM(QUANTITY) QTY1 ELSE 0)
(CASE
WHEN SOP10200.SOPTYPE = 2 THEN SUM(QUANTITY) QTY2 ELSE 0)
END as QTY_Type
FROM SOP10200
GROUP BYITEMNMBR, ITEMDESC, QTY1, QTY2
But I get error message doesn't recognize QTY1 (invalid column name)
Thank you in advance for any assistance.......
I am doing a monthly report to export to an excel Doc from SQL 2000using views.With my design it works great until it is the first of the month. As thereport alwayschecks from yesterday to the first of the current month. One view is below.The date statement checks between the first of month and current date -1Can anyone help with with this?MikeCREATE VIEW dbo.GUTURASSELECT COUNT(*) AS GUTURFROM dbo.ChevWHERE ([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)AND GETDATE() - 1) AND ([N/U] = 'U') AND (([Sales Type] = 'R') or ([SalesType] = '3'))
View 1 Replies View RelatedHi,How can I make a view that gives it's data from az XML file(it hasURL)?Thanks alot
View 1 Replies View RelatedHi friend ! i want to ask you about view. will the view data beupdated automatically when we insert or update the physical table orit is updated when we execute the view ?
View 3 Replies View RelatedI have a table tblElectronic that recieves data electronically. Itcontains fields: filenumber, status, date, qualifier and comments. Thequalifier field is either 100 or 101, meaning the file is confirmed orestimated, respectively. I then join tblElectronic to another table(tblFile) in a view. I am having trouble building the view. The fieldQualifier in tblElectronic can have the value 100 or 101.filenumber statusDate Qualifier Comments1111xxxx01/01/01 100Comments1111xxxx01/01/01 101Comments1112xxxy01/01/01 101Comments1113xyxy01/01/01 100 CommentsI want to use the record where qualifier = 100 in my view, except in thecase where 101 is the only qualifier that exists, meaning it has notbeen confirmed yet.So my view should pull the rows:filenumberstatusdate qualifiercomments1111xxxx01/01/01 101Comments1112xxxy01/01/01 101Comments1113xyxy01/01/01 100 CommentsI have tried case statements but to no avail. Here is my view:SELECT tblFile.Filenumber, tblFile.DataofFile, tblElectronic.status,tblElectronic.date, tblElectronic.commentsFROM tblFile inner jointblElectronic on tblFile.filenumber = tblElectronic.filenumberwhere tblElectronic.qualifier = ??????Not sure what to put here, since I want it to be where qualifier = 100unless that doesn't exist, in which case I want it to be where qualifier= 101.Thanks for the help.Rubia*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related