I am trying to run a select query and getting the following error:
Server: Msg 1013, Level 15, State 1, Line 12
Tables or functions 'PatientVisit' and 'PatientVisit' have the same exposed names. Use correlation names to distinguish them.
Not sure what I am doing wrong! Here is the query. Can anyone point me int the right direction?
SELECT Batch.Entry AS [Date Of Entry], PatientProfile.[Last] + ', ' + PatientProfile.[First] AS Name,
MedLists.Description AS [Adjustment Type], PatientVisit.TicketNumber, PatientVisitProcs.Code AS [Procedure Code],
TransactionDistributions.Amount AS [Adjustment Amount], PatientVisitProcs.DateOfServiceFrom,
ISNULL(CONVERT(varchar(255), Transactions.Note), ' ') AS Notes,
DoctorFacility.DotID, DoctorFacility.ListName as DrName, PatientVisit.DoctorID as DrID
FROM PaymentMethod
INNER JOIN VisitTransactions ON PaymentMethod.PaymentMethodId = VisitTransactions.PaymentMethodId
INNER JOIN Transactions ON VisitTransactions.VisitTransactionsId = Transactions.VisitTransactionsId
INNER JOIN TransactionDistributions ON Transactions.TransactionsId = TransactionDistributions.TransactionsId
INNER JOIN Batch ON PaymentMethod.BatchId = Batch.BatchId
INNER JOIN PatientVisit ON VisitTransactions.PatientVisitid = PatientVisit.PatientVisitId
Inner Join PatientVisit ON DoctorFacility.DoctorFacilityID = PatientVisit.DoctorID
INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
INNER JOIN MedLists ON Transactions.ActionTypeMId = MedLists.MedListsId
INNER JOIN PatientVisitProcs ON TransactionDistributions.PatientVisitProcsId = PatientVisitProcs.PatientVisitProcsId
I have a "case" table describing what type of social worker case and who performed work on the case. Four case workers are referenced within each case and all workers belong to the "worker" table. I am trying to present the case worker name on a detailsView rather then the much simpler task of showing the worker's index stored within the case. I can't seem to get the correlation names working.
SelectCommand="SELECT [ReportID], [RecCreated], tblVCaseType.Description AS cType, [IntakeDate], tblVCounty.Description AS County, [Abuse], [Neglect], [Dependency], [AfterHours], [Screenout], tblVScreenOutcome.Description AS Outcome, [Closed], [ClosedDate], iWorker.[Worker Name], cWorker.[Worker Name] , tblVAssessmentType.Description AS aType, tblVTimeframe.Description AS tFrame, tblVDisposition.Description AS Dispo , tblVProgramXfer.Description AS Xfer,
[Code] ...
Getting only one worker name is easy. But, I need all four. I have tried many versions without success. The error from this version is repeated 4X:
The multi-part identifier "tblVWorker.ID" could not be bound.
SELECT dbo.Job.CompanyJobId, dbo.Item.UnitOfMeasure, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Region.CompanyRegionID, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.UnitOfMeasure = 'TN' and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, fMeasure, dbo.Region.CompanyRegionID, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active
Now I need to inser another join like this:
dbo.Event ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid INNER JOIN
and I get error:
Server: Msg 1013, Level 15, State 1, Line 15 Tables or functions 'dbo.Event' and 'dbo.Event' have the same exposed names. Use correlation names to distinguish them.
I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.
I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.
In my employee table has the following fields empid, empFname, empLname, email, city Say it has data like follows: 1, Lucy, Sam, l@some.com, city1 2. Sam, Wite, l@some.com, city2 3. Laura, Mac, l@some.com, city2 4. Stacy, Soo, s@no.com , city1 So in my case I want to show all the column but I want to eliminate multiple email addresses. I tried Distinct but its not workin because here every column is not distinct. So what should I use? In my case I only want to show empID 1, 3, 4. I want to show all the columns
I'm have the biggest issue here that I cannot resolve. I have a select command that I'm building myself in code behind. When it gets up to the third line of code as shown below I get an error that says "Tables or functions 'Owner' and 'Owner' have the same exposed names. Use correlation names to distinguish them." I have been searching for the past few hours on this and not comming up with anything. If someone could please help me on what I'm doing wrong I would appreciate it. Thank You Dim strProjectOwnerSelectCommand As String = "SELECT ProjectOwner.ID, ProjectOwner.CreateDateTime, ProjectOwner.ProjectID, ProjectOwner.OwnerID, Owner.Name AS OwnerDescription FROM ProjectOwner WITH(NOLOCK) INNER JOIN Owner ON ProjectOwner.OwnerID = Owner.ID WHERE (ProjectOwner.ProjectID = " & hdfProjectID.Value & ") AND (ProjectOwner.OwnerID = " & intUserID & ")" sdsProjectOwner.SelectCommand = strProjectOwnerSelectCommand Dim lblTasksID As Label = CType(gvTasks.Rows(hdfTaskID.Value).FindControl("lblTasksID"), Label)
One of my friend showed me a correlation matrix rendered from STATISTICA. This matrix represents correlations between the questions of an exam that has 24 questions. Teacher asked them to discover the relationships between each question that both answered true or false.. Can we make a graph or something like that in AS?
Here is the screenshot extracted from Word.http://www.imageturk.com/goster.php?res=1ee9826a924e7c51a44er.jpg
I am new to this forum and pretty new to running queries in SQL Server. I have been doing it for years on an iSeries platform and the following update statement would definitely work in SQL/400....but it does not in SQL Server 2000. Any help would be appreciated.
UPDATE TESTDTA.F0101 X SET (ABAC07, ABAC12, ABAC28) = (SELECT AIAC07, AIAC12, AIAC28 FROM TESTDTA.F03012 WHERE AIAN8 = X.ABAN8) WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012 WHERE AIAN8 = X.ABAN8)
...and here are the errors
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'X'. Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'WHERE'.
Im trying to find the error in this statement:CREATE PROCEDURE STP_selectmainASselect a.inventoryid, b.firstname, b.lastname, art.title, art.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions,a.reference, art.provenance, sum(c.restorationcost),sum(d.framingcost), sum(e.cost)from art as a left outer join artist as b on a.artistid =b.artistid,a left outer join restoration as c on a.inventoryid =c.inventoryid,a left outer join outframing as d on a.inventoryid =d.inventoryid,a left outer join basiccosts as e on a.inventoryid =e.inventoryidgroup by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,a.provenanceorder by a.inventoryid descGOeveytime I do a syntax check on it I get this error.error 1012: the correlation name 'a' has the same exposed name astable 'a'.Whats the syntax to fix this?thanks-Jim
here a quick rundown of what i want to do: database database1
database hold all my good information. database 2 is the copy for breaking. Im too a point that i need to get my 2nd DB to by like the first. I want to truncate all the tables in database2, and then export, or select from insert into. I just need the data, not triggers or anything like that.
So: How can i find the name of my tables (not views) in that database
For each (select table_name from system where database="database") copy database.table_name database1.table_name;
i hope that makes sense. I just need to restore my devel DB back to the original so i can mess it up again, Thanks
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated. Randy
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say Select OrderId from Order. But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have the following select statement within a stored procedure:
DECLARE @first_date DATETIME
select @first_date = top 1 (g_collectdate) from ESS_Coefficients es, ProgramXRef where es.Program_Id = ProgramXRef.Program_Id AND @In_Program = ProgramXRef.Program_Name AND es.Scope LIKE @In_Scope order by g_collectdate asc
The syntax check tells me "Error 156: Incorrect syntax near keyword 'top'".
I took out the @first_date = portion, substituted in values and ran the query from SQL query analyzer and it worked fine.
select top 1 (g_collectdate) from ESS_Coefficients es, ProgramXRef where es.Program_Id = ProgramXRef.Program_Id AND 'ABC' = ProgramXRef.Program_Name AND es.Scope LIKE 'abc/%' order by g_collectdate asc
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT? protected void Button1_Click(object sender, EventArgs e) { SqlDataSource2.Select (); } protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e) {string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString(); } <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected"> <selectparameters> <asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" /> </selectparameters> </asp:SqlDataSource>
The problem I'm having is described below all this code.---------------------------------------------------------My content page has a SqlDataSource: <asp:SqlDataSource ID="sqlGetUserInfo" runat="server" ConnectionString="<%$ ConnectionStrings:RemoteNotes_DataConnectionString %>" SelectCommand="SELECT [UserFirstName], [UserLastName] FROM [Users] WHERE ([UserGUID] = @UserGUID)" onselecting="sqlGetUserInfo_Selecting"> <SelectParameters> <asp:Parameter Name="UserGUID" Type="String" /> </SelectParameters> </asp:SqlDataSource> -----------------------------------------------------Inside my OnSelecting event, I have: protected void sqlGetUserInfo_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { sqlGetUserInfo.SelectParameters["UserGUID"].DefaultValue = Membership.GetUser().ProviderUserKey.ToString(); } ---------------------------------------------------------------------And, inside my Page_Load, the relevant code is: //String strUserFirstName = ((DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty)).Table.Rows[0]["UserFirstName"].ToString(); DataView dvUserDetails = (DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty); if ((dvUserDetails != null) && (dvUserDetails.Count > 0)) { DataRow drUserInfo = dvUserDetails.Table.Rows[0]; lblHelloMessage.Text = "Hello, " + drUserInfo["UserFirstName"].ToString() + ((drUserInfo["UserLastName"].ToString() == "") ? "" : " " + drUserInfo["UserLastName"].ToString()); } ---------------------Now, here's the problem. My "if" statement is never executing because "dvUserDetails" is null. However, when I break the execution and put awatch on the actual Select() statement, it shows a DataView with the correct return rows! You can see the commented line where I tried tobypass the DataView thing (just as a test), but I get an object reference is null error.The weird thing is that it was working fine one minute, then started getting "funky" (working, then not, then working, then not), and now it just doesn't work at all. All thiswithout me changing one bit of my code because I was checking out some UI flow and stopping and restarting the application. I've tracked downthe temporary files directory the localhost web server runs from, deleted all those files, and cleaned my solution. I've even triedrebooting, and nothing seems to make it work again.My relevant specs are VS2008 9.0.21022.8 RTM on Vista Enterprise x64.
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors: Accounting Computer Science Mathematics
and the user already had Mathematics in his/her profile I'd like it to display only:
Accounting Mathematics
Below is the layout of the tables, my attempt at the query, and then below that some example data.
Code Snippet
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Majors]( [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED ( [MajorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
SELECT MajorID FROM Majors majs WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0)) dbo.Majors MajorID Accounting Computer Science Mathematics
Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
