What Looks Like A Basic SQL Query Still Not Resolved
Nov 23, 2005
ARRRRRRRGGGGGHHHHH!!
Please can you help, I'm going round the bend with this.
I have a simple and small table called STOCKCATS, which I need to query to
get back a dataset in a particular order, but although it looks simple I
can't get it to work. My table schema plus sample data to see the problem
is as follows:
DROP TABLE IF EXISTS `STOCKCATS`;
CREATE TABLE `STOCKCATS` (
`CATID` varchar(30) NOT NULL default '',
`LEVEL` varchar(30) default NULL,
As you can see from the table structure, this table consists of 2 field
values. The 1st is the category code and the 2nd is the level is at. If a
catid has a level of nothing, eg '', then it means that it is a root level
category. If a catid has a another cat's catid in it's level, eg B01 has
P01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.
All I want to do is query this table and bring back the data so that
alphabetically it goes root level cat A1, then all the sub-cats for this
root level, then root level A2, then all sub-cats for this root level and so
on. An example using the above would be as follows:
^ ^ A to G of root level cats plus their sub-cats....
HEATING << root level
BB << sub-cat of heating
FS << sub-cat of heating
WM << sub-cat of heating
/ / I to Z of root level cats plus their sub-cats....
A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),
CATID and I thought this had done it, but I was looking at the ('A001',''),
('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')
entries as these naturally fell into place. If you use this order command
on the above you will see that ('P01','') and it's associated ('B01','P01')
sub-cat just don't come together.
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
I am trying to combine 2 queries, each with their own 'order by' and I am having trouble.
This is just an example, not what I'm trying to do, my query is more elaberate but looks simular to this.
SELECT TOP 10 *, 'FieldA' AS SortedBy FROM TableA Order By FieldA Desc UNION ALL SELECT TOP 10 Precent *, 'FieldB' AS SortedBy FROM TableA Order By FieldB Asc
Anyway not to get the following error? Server: Msg 156, Level 15, State 1, Line 34 Incorrect syntax near the keyword 'UNION'.
Hi Everyone This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE, CC.[COMPANY], CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE] FROM CUSTOMERCONTACTNOTES AS CCN, (SELECT * FROM CUSTOMER) AS CC, (SELECT MAX([INVOICE DATE]) AS LASTORDERDATE, [ACCOUNT REFERENCE] FROM INVOICEDATA GROUP BY [ACCOUNT REFERENCE]) AS INVDATE WHERE CCN.[COMPANY] = CC.[COMPANY] AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA) AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE] GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE] ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment. Mitesh
Suppose we get two following tables: Table A: [USER_ID] [varchar] (11) NOT NULL , [COURSE_ID] [varchar] (11) NOT NULL , Table B: [COURSE_ID] [varchar] (11) NOT NULL , [COURSE_NAME] [varchar] (50) NOT NULL , [COURSE_NO] [varchar] (15) NULL , [BEGIN_DATE] [datetime] NULL , [END_DATE] [datetime] NULL , [CREATER] [varchar] (11) NOT NULL , and during the execution of my program, I can get the current use's id (USER_ID), say U0001. How can I retrieve the result set containing [COURSE_NAME], [COURSE_ID], but the current user's id (U0001) have Not been assigned in Table A. Thanks in advance. Ricky.
I'm not getting it. The query below "e.g." is exactly as I think it should be except for the <Join Clause> as there is only the match on site name. I tried joing on the site name, but only got the sites in the log. I want all the site names even if they don't have a log item for a given date range.
I'd post my real query if it would help, it's just that I'm chopping up some strings and converting some time stamps to dates as varchar for readability for the final report, which is an MSExcel pivot table.
My original post lies below.
TIA
jEfFp...
Reply... Date: Basic Query Help (reply) RickD (Rick.Davis@Schroders.com) 7/11/01 6:41:26 AM
Make log an outer join to site.
e.g
select s.name, l.ondate from site s RIGHT OUTER JOIN log l ON <Join Clause> where substring(id,3,6) in(select name from site) and ondate between getdate()-8 and getdate()-1
You didn't seem to have any logical join between the tables, but i'm sure you have, just put this in the <Join Clause>.
Oh, and read BoL, it helps no end.....
------------ Original Post... Jeff Proctor at 7/10/01 11:18:31 PM
my 7 day query....
select s.name, l.ondate from site s, log l where substring(id,3,6) in(select name from site) and ondate between getdate()-8 and getdate()-1
This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.
I have 2 tables...
table1 sites name smithj anandt burtot proctj etc....
I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID WHERE MerApp.ActiveStatus=1 GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.
This didn't work for me: SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID WHERE MerApp.ActiveStatus=1 GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows
Ok, I'm a beginner so forgive me for my ignorance. Could someone helpme with this?tbl_x has two fields xid and xlistxlist being a list of numbers.....1, 5, 6, 8i want to create a new table from tbl_x that converts each number inthat xlist into a row in tbl_newso for exampletbl_x has 3 rowsxid xlist1 1, 4, 52 2, 3, 73 2, 1, 7i need a query or sql script that will convert that table with a listinto the following....tbl_newid xid xlid1 1 12 1 43 1 54 2 25 2 36 2 77 3 28 3 19 3 7
Hi I am having two very basic problems which are very frustrating. I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.
This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:
string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg"; SqlCeResultSet resultSet = null; SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);
resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.
Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.
I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.
What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.
Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,
How would I convert this query to ANSI 92 standard?
select p.InternalID, n.LastName from tblPatient p, tblName n where p.PatientID = n.EntID and n.EffDt = ( select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate() )
i am learning SQL basic commands ( like , where, having, group by, order by , join ,and simple sub-query) and I got one exersice I cannot solve
table RECIPE has 3 columns , ( pizza ,ingredient,amount) each pizza could use 1 / many ingredient, each ingredient could be used by 0 / 1/ many pizza, amount means the quantity of that ingredient used in certain pizza
question: (List the ingredients, and for each ingredient, also list the pizza that contains the largest amount of this ingredient.)
============================ Raw data :
recipe ====== pizza ingredient amount -------------------------------- margarita spice 5 margarita cheese 120 ham ham 150 ham spice 5 napolitana anchovies 100 napolitana olives 75 napolitana spice 10 hawaiian ham 100 hawaiian pineapple 100 hawaiian spice 5 cabanossi cabanossi 150 cabanossi spice 10 siciliano onion 50 siciliano capsicum 75 siciliano olives 50 siciliano anchovies 50 siciliano spice 15 americano salami 120 americano pepperoni 75 americano spice 10 mexicano onion 75 mexicano capsicum 75 mexicano mushroom 50 mexicano chilli 25 mexicano spice 20 seafood seafood 200 seafood spice 5 garlic garlic 25 garlic spice 10 vegetarian onion 50 vegetarian capsicum 50 vegetarian mushroom 50 vegetarian peas 50 vegetarian tomato 50 vegetarian spice 5 mushroom mushroom 100 mushroom spice 5 special cheese 25 special tomato 25 special ham 25 special anchovies 25 special olives 25 special mushroom 25 special bacon 25 special egg 25 special pineapple 25 special cabanossi 25 special salami 25 special capsicum 25 special onion 25 special peas 25 special seafood 25 special spice 10 stagiony ham 75 stagiony mushroom 50 stagiony olives 50 stagiony anchovies 25 stagiony spice 10
Hi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL, topic.TOPIC_NAME, topic.TOPIC_URL FROM tbl_CCF_Headlines headline INNER JOIN tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID WHERE (headline.HEADLINE_DATE IN (SELECT TOP 3 HEADLINE_DATE FROM tbl_CCF_HEADLINES GROUP BY HEADLINE_DATE ORDER BY HEADLINE_DATE DESC)) ORDER BY headline.HEADLINE_DATE DESC
I am using a function to fill a listbox in asp.net If I run it using the sql statement in the code, it works. if I use the sp I get an error saying the sp expects @strBookingNo which wasn't supplied. I have stepped through the function and there is a value in txNewBookingNo.text. Here's my function - the part commented out is the part that works,but I'd rather do it through an sp. I've included my sp too
Function GetFacilitiesBookings()
Dim MySQL As String = "spRB_GetFacilitiesBookings" Dim MyConn As New SqlConnection(strConn) Dim Cmd As New SqlCommand(MySQL, MyConn) Cmd.CommandType = CommandType.StoredProcedure Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text))) Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn) Dim ddlDataSet As New DataSet
'strSQL = "Select * from tblRB_FacilitiesBookings where FB_BookingNo = " & CInt(Me.txtNewBookingNo.Text) & " and FB_Cancelled =0" 'Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConn) 'Dim ddlDataset As New DataSet 'ddlDataset.Clear()
I have an sp which I should return the latest date in my sql table. Howver I am getting this error 'Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query. ' Here's my sp and my code which calls the sp.
CREATE PROCEDURE spRB_MaxDate ( @MaxDate datetime OUTPUT ) AS SELECT @MaxDate =MAX(BD_DateRequired) FROM tblRB_BookingDates
RETURN GO
Here's how I call it
Dim MySQL1 As String = "spRB_MaxDate" Dim myConn1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim Cmd1 As New SqlCommand(MySQL1, myConn1) Cmd1.CommandType = CommandType.StoredProcedure
'Add the parameters
Dim outParm As SqlParameter = New SqlParameter("@MaxDate", 0)
I have installed SSRS and created a .net application... now when I try to open the report viewer, I get the error " the permission granted to user 'domainuser' are insufficient for performing this operation. (rsAccessDenied)" I have searched the net hi and low and cant seem to find an adequate answer... something to do with roles or permissions? I have assigned permissions to this user on the ssrs database, yet I still cant seem to view the report. It works fine in my test environment (as it usually does ) any help on this would be greatly appreciated as to be honest, I dont really know what im doing here.
I am selecting from two tables, with a one-to-many relationship.
Select t2.* From Table1 t1 inner join Table2 on t1.id = t2.id where t2.Field2 in (1,3)
There are times when there are 2 or more entries in table2 that relate to table1. I want to only display 1 of these entries, (either will do), I just dont want to display all.
Hi we recently depoyed SSRS 2005 on two servers along with our web application.
the extranet setup
IIS Web server (app server) in DMZ linked to database server sat behind our corporate firewall. SSL installed on IIS server. SSRS app is on the IIS, with its db on the main database server behind corproate firewall.
we have a simple link going from our app to the reporting services link
however when we enter a login (incl admin) it fails with the message 'The remote name could not be resolved: "ourdomain.com"
Hi, I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes. To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls. As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started. I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader? Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))
Public Overrides Function GetAllCategories() As List(Of Category) Dim sqlCmd As SqlCommand = New SqlCommand() SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
Return categoryList End Function
Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category If Id <= DefaultValues.GetCategoryIdMinValue() Then Throw New ArgumentOutOfRangeException("Id") End If
Dim sqlCmd As SqlCommand = New SqlCommand() AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id) SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
If categoryList.Count > 0 Then Return categoryList(0) Else Return Nothing End If End Function
Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category)) Do While returnData.Read()
Dim actualDuration As Decimal = 0 If Not returnData("CategoryActualDuration") Is DBNull.Value Then actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration")) End If
Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _ CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _ CInt(returnData("ProjectId"))) categoryList.Add(category) Loop End Sub Hope this makes sense. Sorry if I have posted this in a wrong section..! Cheers,Nirav
Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.
However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?
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 reading over a job description and I am unsure of that the employer is refering to when they say "Instance Parameters".
Here is the context that it is used in. * Tune and optimize SQL statements, Instance Parameters, and Data Storage including the identification of performance related issues.
You can't have but one default contraint in a DB named df_revisiondate Default getdate()
I thought that sense they were being created on different Tables that it would not matter. It was a way to maintain a type of consistency by giving them the same name. However, it SEEMS to have caused all my tables to crash.
Let say i have 3 tables. Let's name it Main, table2, table3. in table Main, there are 4 colums(M_id,M_name M_tbl2, M_tbl3). In table2 and table3, each has 3 colums. For table2 the colums are id, tbl2Name, tbl2Desc and for table3(id, tbl3Name, tbl3Desc).
M_tbl2 will refer to id in table2 and M_tbl3 will refer to id in table3. How am i going to get M_id, M_name, tbl2Name, tbl3Name in one query?
I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?
I have test tables with key fields that increment by one each time a record is added. If I delete all the records in a table (using a delete query) is there a way I can make the key start at 1 again.
The reason I ask is that by using test data in these tables (and there are a lot of records),when I clear them out and append the live data the key field starts at something like 764525
Sorry if this sounds dumb but I can't find the answer . I have created a scheduled job on my test database using the wizard. How do I edit it or delete it ? Step by step instructions please
I have never done this before so am just experimenting at the moment.
I have a stored procedure where I would like to use substring in the where clause, if possible.
CREATE PROCEDURE GetJobList @PlantId char(3)
Select ...... From ...... WHERE (dbo.Source.CompanySourceId = 'PROD' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo and (if @PlantId <> ' ' then SUBSTRING(dbo.Job.CompanyJobId, 1,3) = @PlantId))
I get syntax error "Incorrect syntax near 'if, incorrect sysntax near 'then'....
