I have about 14 tables that I am trying to tie together in a view to
see a user's status in our company.
Joe User:
Email - Active
ADP - Active
Portal - Inactive
etc.
We need to track application access across the company, so we have
tables for each application that contain user info(username,
password(encrypted), start date, end date, changed date) so that we
can track who has what, and when they were given access as well as
when it was taken away.
Every application table has a userID attached to it.
What I would like to do is to take the userID field and look for their
application access across the company. To do this, i'll have to look
for the max value in each table because someone could be given access,
have it taken away, and be given it again. People move all over the
place here, so we have to be able to track who has what, when, and at
what building.
I started out with trying to left outer join the tables together, but
it didn't work. I tried doing something along the lines of:
select
esarfAppEmail.emailID,
esarfAppEmail.esarfUserID,
CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'
end as EmailStatus--,
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?
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
Hello, I have 2 tables, first is the mineral values, 2nd is ore composition... Minerals table:
Mineral Value
Mineral1 10
Mineral2 20Ore composition table:
Ore Mineral1 Mineral2
Ore1 100 0
Ore2 0 200
Ore3 10 10Now what I need to do is to have a view (or something else that gerenates a table) that will give me the value of each ore. So for example ore 3 would be 10 X mineral1 value plus 10 X mineral2 value. So the result should be:
Ore Value
Ore1 1000
Ore2 4000
Ore3 300Can anyone point me to some tutorial or example of a similar calculation with sql views?
I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:
My code is:
GO IF OBJECT_ID ('Salaries') IS NOT NULL DROP VIEW Salaries ; GO CREATE VIEW Salaries AS select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt' Group By Salary, ExemptStatus; GO SELECT * FROM [Salaries]
I'm receiving the following Msg 4511, Level 16, State 1, Procedure Salaries, Line 3 Create View or Function failed because no column name was specified for column 1. Msg 208, Level 16, State 1, Line 1 Invalid object name 'Salaries'.
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
I am creating a view and want to select records where the value of a Customer field (Klant Test Plan) is NULL or has exact the same value as for example customer field 2 (Klant Schedule).
I have already the code below:
SELECT DISTINCT Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â TOP (100) PERCENT dbo.Product.ERPKey, dbo.TestPlan.CoA, dbo.TestMethod.WorkInstruction, dbo.Customer.Name AS [Klant Testplan], Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Customer_1.Name AS [Klant Schedule], LEFT(dbo.ShopFloor.ShopFloorNumber, 7) AS Schedule, Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END AS Customer FROMÂ Â Â Â Â Â Â Â dbo.Customer AS Customer_1 INNER JOIN
I am creating a website. i have completed the GUI. i have labels on the left side of the form and a text box for each of those labels on the right side of the form. i have the two dropdown boxes that you have helped me create and bind. now im stuck on how to start the code to get this all working. i know what i need it to do (pseudocode) but dont know how to write the code for it. these books and online sources, really dont say where to put things, they just give examples, not stating where to place it. I am using a SQL database called Manufacturers i would like to insert or update, but not delete records wtih this tool pixelsyndicate mentioned using the GUI to do the code for me, would love to know how to do this. any help would be awesome, or any good beginner resources would help too
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
I have suddenly lost the ability to view the tables for one of my database via Enterprise Manager. It just sits at an hourglass and never returns! Alos, if I attempt to link to the tables via MSAccess's ODBC interface, I get a timeout.
I am able to view the table list for other user databases, as well as the system databases.
I am able to view other object list (like SPs) for the database in question, via Enterprise Manager.
I am able to perform a select query against the sysobjects table for the database in question.
Any ideas on why Enterprise Manager, and ODBC links, are hanging while trying to access the tables for one particular user database?
Any help is appreciated.
All other functions appear normal. If I can't figure anything out, or no one has any other ideas, I will try rebooting the server after the users are gone for the day.
Hi, I€™m new to sql ex 2005 and have a couple of questions I can€™t find the answers to. Q.1 I am working with VS web dev ex 2005 and I€™m trying to backup my db, is it as simple as copying the .mdf file? Or should I create a text file, or something else? Q.2 How do I view the content of the db in my web programs in the sql server 2005 ex? I can€™t figure out what to do, to view the content. I have tried to find tutorials on ex 05 but can€™t find answers to my questions. Help would be appreciated. Thank you.
I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views. Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view? I have used the following SQL, but unfortunately it gives too many results: SELECT VIEWS.name AS VIEW_NAME, TABLES.name AS TABLE_NAME, VIEW_SQL.text FROM sysobjects VIEWS INNER JOIN syscomments VIEW_SQL ON VIEWS.id = VIEW_SQL.id INNER JOIN sysobjects TABLES ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%' WHERE (VIEWS.xtype = 'V') AND (TABLES.xtype = 'U') ORDER BY VIEWS.name, TABLES.name Justin
I would like to create a view that joins these tables. Each row show a product and all the qualities it has, from quality 1 to quality 8. If there is no N'th quality, to show blank/null/zero.
The problem is how to get values from multiple rows (in table Qualities) in a single row in the view.
Dear friends, is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
Hi,I hope this is the right place to ask this question. If it is not,please let me know a more appropriate place.I have two tables.CREATE TABLE tblEmployees(EmployeeID int identity NOT NULL,LastName varchar(50) NULL,FirstName varchar(50) NULL,);CREATE TABLE tlkpDept(DeptID char(5) NULL,Name char(10) NULL,LongName char(50) NULL);Now I want to create a view called AssignedTo. [The application I'mdoing, will track the status of our customer requests. Each requestcan be assigned to either an individual employee or an entiredepartment]I want the view to have two columns, ID and LongName. ID should beeither the DeptID or the EmployeeID. The LongName column should beeither the LongName or the FirstName + LastName.I'm not even sure how to begin to write such a complex SQL.EnterpriseManager is being less than helpful too.Can anyone help?Thanks in advance.-Tom
If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat all the rows from table 2 into one column if that's possible.table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail- table2.row3.detail), table1.dateCreatedthxM@
We have created a view that selects from tables in two databases. it looks like something like this:
CREATE VIEW myview AS
SELECT col FROM db1.mytab
UNION SELECT col FROM db2.mytab;
Both tables have col as primary key.
When we executes SELECT col FROM myview WHERE col='SOMEVALUE' we receives the correct result but SQL server does not use any indexes. It performs a index scan on both tables. Wh and what can we do about it? The tables contains millions of rows so the performance is terrible.
The following works fine:
SELECT col FROM db1.mytab WHERE col ='SOMEVALUE'
UNION SELECT col FROM db2.mytab WHERE col ='SOMEVALUE';
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int. Vic Valentic CEO/President Open Door 2 Elite Dr. #33 Hamilton, Ontario L8W 2N3 905-389-7492 http://www.wlu.ca/next/opendoor
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1 Name: John HomePhone: 123-456-7890 WorkPhone: 123-456-7890 CellPhone: Pager: 123-456-7890 Fax: Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have. Thanks in advanced, Nathan Rover
Hi to all I have a View of 3 tables I would like to be able to update insert data into Views table that in return do the same in the original tables is that possible? Thank you
Hi. I am managing the SQL script files for constructing tables,views,and so on with Visual Source Sefe.
But once I run these scripts with a tool like Query Analyzer for the database in SQL Server, I do not know How to get the versions of the scripts which were installed now, with using Enterprise Manager or any tool associated with SQL Server2000.
Please tell me How are you managing and know which version of scripts have been installed on database in SQL Server.
I have a application that uses a MSDE database to store information. The application seems to hit into a database corruption problem. Now I would like to view all the datas and tables created in the database. How can I do that using OSQL ?? What are the commands to view all the tables in this database ? Or is there any other better applications to do the viewing ?? Please advise. Thanx a lot !!
Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name, COUNT(tbl_outbox.msgUserID), FROM tbl_adminuser INNER JOIN tbl_AdminCategory ON tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID AND tbl_outbox.msgUserID <> 0 GROUP BY tbl_outbox.msgUserID But I am getting error pls correct the view,
I am new with SQL and I am Oracle DBA, trying to learn SQL. Any way, in SQL, is there a view or tables that you can run to pull out a list of the databases inside an instance? I don't want to use the GUI though.
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows: 1.PerformanceDeficiencyNotice 2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below “Incorrect syntax near the keyword ‘INTO’�. Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT pdn.transactionid, pdn.employeenbr, pdn.lastname, pdn.firstname, pdn.processlevel, pl.facilityname as processlevelname, pdn.department, pdn.jobcode, pdn.title, pdn.supemployeenbr, pdn.managername, pdn.timeframe as pdn_timeframe, pdn.actualeffectivedate as pdn_startdate, /*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/ /*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/ case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then case pdn.timeframe when '30' then dateadd(month,1,pdn.actualeffectivedate) when '60' then dateadd(month,2,pdn.actualeffectivedate) when '90' then dateadd(month,3,pdn.actualeffectivedate) else null end end as pdn_enddate, pdn.status as pdn_status, status.description as pdn_statusdesc, pdn.managersignoff as pdn_managersignoff, pdn.managersignoffdate as pdn_managersignoffdate, pdn.associatesignoff as pdn_associatesignoff, pdn.associatesignoffdate as pdn_associatesignoffdate, pdn.witnessname as pdn_witnessname, /*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/ /*where the evaluationtype = 'X' (Extension Final).*/ /*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/ case when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate) else null end as ext_startdate, eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/ eval.evaluationtype as eval_evaluationtype, evaltype.description as eval_evaltypedesc, eval.status as eval_status, status2.description as eval_statusdesc, eval.effectivedate as eval_effectivedate, eval.managersignoff as eval_managersignoff, eval.managersignoffdate as eval_managersignoffdate, eval.associatesignoff as eval_associatesignoff, eval.associatesignoffdate as eval_associatesignoffdate, eval.witnessname as eval_witnessname into #pdninfo FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval on pdn.transactionid = eval.transactionid left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl on pdn.processlevel = pl.processlevel left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status on pdn.status = status.status and status.type = 'PDN' left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2 on eval.status = status2.status and status2.type = 'EVAL' left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype on eval.evaluationtype = evaltype.type /*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/ WHERE pdn.status in ('A','S') /*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/ /*'D' - In Progress, or 'N' - Not started)*/ OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/ /*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/ /* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/ /* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/ SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate into #perfreview FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview WHERE #pdninfo.employeenbr = perfreview.employeenbr AND perfreview.updatedate = (SELECT max(updatedate) FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2 WHERE perfreview2.employeenbr = perfreview.employeenbr AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/ SELECT 'orig' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/ SELECT 'ext' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
I have a view based on two tables. Now I want to update that view in such a manner that the columns of both the tables are going to update. Can you suggest me what code I should write so that I can update that view.
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
I have the following 2 tables structure whcih I need to collect information in a single consolidation table For that lest call Tabel1 as OnStart and table2 as OnEnd Here is how my data tables looks like in both table
OnStart :
Name value =============== str1 1 str2 2 str3 3 str4 4
OnEnd :
Name value =============== str5 11 str6 12 str7 13 str8 14
What I need to do is to return data from those 2 tables in following format :
Table results that should be return from procedure call