i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(
SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as select T1.Matcle as CorpID, T2.Nmpres as Name, T4.DATDEB as LeaveFrom, T4.TEMDEB as PM, T4.DATFIN as LeaveTo, T4.TEMFIN as AM, T10.LIBLON as LeaveType, T8.LIBLON as Location, T12.LIBLON as ParentOrg
from HRINU.zy00 T1, HRINU.zy3y T2, HRINU.zy39 T3, HRINU.zyag T4, HRINU.zy38 T5, HRINU.zy1s T6, HRINU.zd00 T7, HRINU.zd01 T8, HRINU.zd00 T9, HRINU.zd01 T10, HRINU.zd00 T11, HRINU.zd01 T12 where T4.Nudoss = T3.nudoss and T4.Nudoss = T1.Nudoss and T1.Nudoss = T2.nudoss and T3.nudoss = T5.nudoss and T6.nudoss = T1.nudoss AND T7.NUDOSS = T8.NUDOSS AND T9.NUDOSS = T10.NUDOSS AND T11.NUDOSS = T12.NUDOSS AND T3.IDWKLO = T7.CDCODE AND T4.MOTIFA = T9.CDCODE AND T5.IDESTA = T11.CDCODE and T6.stempl = 'A' and t7.cdstco = 'z04' AND T8.CDLANG = 'U' and t9.cdstco = 'DSJ' AND T10.CDLANG= 'U' and t11.cdstco= 'DRE' AND T12.CDLANG= 'U' and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00 and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00 and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables. Also throw some light on how to analyse the execution plan for further enhancements.
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?
We are trying to create a view that references lengths in both metres and feet.
What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.
We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.
I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".
I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".
Table: workorder has columns: woid, startdate (plus many others) Table: wo_comment has columns: woid, comment, sequence
Desired View: view_workorder has columns: woid, startdate, allcomments
I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?
create view [workorder_view] as ( Select w.woid, w.startdate, cast( DECLARE @all_comments nvarchar(4000), @curr_comment nvarchar(256) DECLARE wo_comment_cur CURSOR FOR SELECT woc.Comments FROM WO_Comment as woc WHERE woc.woid = w.woid OPEN wo_comment_cur FETCH NEXT FROM wo_comment_cur INTO @curr_comment WHILE (@@FETCH_STATUS = 0) BEGIN Set @all_comments = @all_comments + @curr_comment FETCH NEXT FROM wo_comment_cur INTO @curr_comment END CLOSE wo_comment_cur; DEALLOCATE wo_comment_cur; as nvarchar(80000)) as COMMENTS
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only, 2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login. 3.If we Deny the View definition option he can't able to see the Objects which are there under the DB. 4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2 Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
SELECT [DEFINITION] FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)
I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.
Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN GO use tempdb GO CREATE TABLE [dbo].[ItemProperties]( [itmID] [int] NOT NULL, [cpID] [smallint] NOT NULL, [ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id      stats_name                                     stats_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id      index_name                                     index_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS ( SELECT s.object_id ,s.name as stats_name ,( SELECT c.name + ', ' as [data()] FROM sys.stats_columns as sc
Write 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
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
We need two reports one is QTD and another one YTD..FOR QTD report is working fine.We are doing summing of YTD report as Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as APPROVEd We are going to select the YTD Q1 reports we need the output as 10 Summing of Q1 We are going to select the YTD Q2 reports we need the output as 10 Summing of Q1+Q2 We are going to select the YTD Q3 reports we need the output as 30 Summing OF Q1+Q2+Q3 We are going to select the YTD Q4 reports we need the output as 40 Summing of Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as DRAFT We are going to select the YTD Q1 reports we need the output as 0 Summing of Q1 We are going to select the YTD Q2 reports we need the output as 15 Summing of Q1+Q2 We are going to select the YTD Q3 reports we need the output as 15 Summing OF Q1+Q2+Q3 We are going to select the YTD Q4 reports we need the output as 15 Summing of Q1+Q2+Q3+Q4
We need a output view level.We are not using procedure in the report.
In SSMS, when editing a step in a SQL Agent job, on the Advanced screen for a step there is a button called View by the Output File. How do you get that to be enabled? It's always disabled for me and I wish I could view the file right from the job. This is something I miss from 2000. However, the button is there so I would think that I am just doing something wrong.
My company has upgraded some of the database systems to SQL 2005 while other have a fresh install.
The problem we're seeing is that the view button located in the Advanced section of the steps in the Job properties windows is greyed out. The path is valid as we can copy the path and paste it at the run command and view the log.
Does anyone know how this problem can be resolved? If we select to log the results to a table; we have access to the corresponding view button but not the one for the file system logging.
We rely heavily on this feature since we used it often under SQL 2K. Can anyone provide any helpful details to resolve this or has anyone seen this problem before???
I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)
Client Number SSN Client Name Old SD New SD ------------- ----------- ---------------------------------------- -------- -------- 800901 899-34-3482 John Smith 04/20/20 05/01/20 400909 144-23-0029 John Smith 04/09/20 04/11/20 447788 445-89-9967 kjl;j;j 04/05/20 04/12/20 300099 234-90-7815 John Johnson 04/08/20 04/15/20
What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents, (SELECT i.Surname FROM individuals i, families f where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID)) as "father" FROM individuals i where i.Parents>0 order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Hi Everyone, I am trying to do a query where I need to use as little C# as possible to build my gridview. Basically I have a column called statusID. There are about 15 options for this column but I only want to count certain ones. I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed". I also want it to count based on the month. To accomplish this I have used CASE and GROUP BY. This has worked to some extent. Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January. Unfortunately the query returns 2 records for the month. The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value. I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data. My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL. The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month. Below is my query, any suggestions about how to approach this will be greatly appreciated. If any clarification is needed please let me know. Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable. SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID WHEN 3 THEN COUNT(Attend.statusID) END AS fullUnitsUsed,CASE Attend.statusID WHEN 4 THEN COUNT(Attend.statusID) WHEN 14 THEN COUNT(Attend.statusID) WHEN 15 THEN COUNT(Attend.statusID) WHEN 16 THEN COUNT(Attend.statusID) WHEN 17 THEN COUNT(Attend.statusID) WHEN 18 THEN COUNT(Attend.statusID) WHEN 19 THEN COUNT(Attend.statusID) WHEN 20 THEN COUNT(Attend.statusID) END AS halfUnitsUsed FROM Attend INNER JOIN People ON Attend.personID = People.personID INNER JOIN Property ON Attend.propertyID = Property.propertyID INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID CROSS JOIN OpenDays WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007') GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID ORDER BY Property.Name, fullName, NYSDDSORegion.Description
There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).
A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.
For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:
I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns