Search Results

Search found 28985 results on 1160 pages for 'sql training'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 612/1160 | < Previous Page | 608 609 610 611 612 613 614 615 616 617 618 619  | Next Page >

  • Database Modeling - Either/Or in Many-to-Many

    - by EkoostikMartin
    I have an either/or type of situation in a many-to-many relationship I'm trying to model. So I have these tables: Message ---- *MessageID MessageText Employee ---- *EmployeeID EmployeeName Team ---- *TeamID TeamName MessageTarget ---- MessageID EmployeeID (nullable) TeamID (nullable) So, a Message can have either a list of Employees, or a list of Teams as a MessageTarget. Is the MessageTarget table I have above the best way to implement this relationship? What constraints can I place on the MessageTarget effectively? How should I create a primary key on MessageTarget table?

    Read the article

  • Are database triggers evil?

    - by WW
    Are database triggers a bad idea? In my experience they are evil, because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger. On the other hand, it seems like if you have logic that must occur evertime a new FOO is created in the database then the most foolproof place to put it is an insert trigger on the FOO table. The only time we're using triggers is for really simple things like setting the ModifiedDate.

    Read the article

  • Looping through recordset with VBA

    - by Robert
    I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner: Navigate to first Customer, assign the first SalesPerson to the Customer. Move to Next Customer. If rsSalesPersons is not at EOF, move to Next SalesPerson; if rsSalesPersons is at EOF, MoveFirst to loop back to the first SalesPerson. Assign this (current) SalesPerson to the (current) Customer. Repeat step 2 until rsCustomers is at EOF (EOF = True, i.e. End-Of-Recordset). It's been awhile since I dealt with VBA, so I'm a bit rusty, but here is what I have come up with, so far: Private Sub Command31_Click() 'On Error GoTo ErrHandler Dim intCustomer As Integer Dim intSalesperson As Integer Dim rsCustomers As DAO.Recordset Dim rsSalespeople As DAO.Recordset Dim strSQL As String strSQL = "SELECT CustomerID, SalespersonID FROM Customers WHERE SalespersonID Is Null" Set rsCustomers = CurrentDb.OpenRecordset(strSQL) strSQL = "SELECT SalespersonID FROM Salespeople" Set rsSalespeople = CurrentDb.OpenRecordset(strSQL) rsCustomers.MoveFirst rsSalespeople.MoveFirst Do While Not rsCustomers.EOF intCustomers = rsCustomers!CustomerID intSalesperson = rsSalespeople!SalespersonID strSQL = "UPDATE Customers SET SalespersonID = " & intSalesperson & " WHERE CustomerID = " & intCustomer DoCmd.RunSQL (strSQL) rsCustomers.MoveNext If Not rsSalespeople.EOF Then rsSalespeople.MoveNext Else rsSalespeople.MoveFirst End If Loop ExitHandler: Set rsCustomers = Nothing Set rsSalespeople = Nothing Exit Sub ErrHandler: MsgBox (Err.Description) Resume ExitHandler End Sub My tables are defined like so: Customers --CustomerID --Name --SalespersonID Salespeople --SalespersonID --Name With ten customers and 5 salespeople, my intended result would like like: CustomerID--Name--SalespersonID 1---A---1 2---B---2 3---C---3 4---D---4 5---E---5 6---F---1 7---G---2 8---H---3 9---I---4 10---J---5 The above code works for the intitial loop through the Salespeople recordset, but errors out when the end of the recordset is found. Regardless of the EOF, it appears it still tries to execute the rsSalespeople.MoveFirst command. Am I not checking for the rsSalespeople.EOF properly? Any ideas to get this code to work?

    Read the article

  • How the return type is determined in a method that uses Linq2Sql?

    - by Richard77
    Hello, I've usually have difficulties with return types when it comes to linq. I'll explain by the following examples. Let's say I have a Table Products with ProductID, Name, Category, and Price as columns : 1) IQueryable<Product public IQueryable<Product> GetChildrenProducts() { return (from pd in db.Products where pd.Category == "Children" select pd); } 2) Product public Product GetProduct(int id) { return (from pd in db.Products where pd.ProductID == id select pd).FirstOrDefault(); } Now, if I decide to select, for instance, only one column (Price or Name) or even 2 or 3 columns (Name and Price), but in any case, less than the 4 columns, what's going to be the return type? I mean this: public returnType GetSomeInformation() { return (from pd in db.Products select new { pd.Name, pd.Price } } What SHOULD BE the returnType for the GetSomeInformation()? Thanks for helping

    Read the article

  • Tables with no Primary Key

    - by Matt Hamilton
    I have several tables whose only unique data is a uniqueidentifier (a Guid) column. Because guids are non-sequential (and they're client-side generated so I can't use newsequentialid()), I have made a non-primary, non-clustered index on this ID field rather than giving the tables a clustered primary key. I'm wondering what the performance implications are for this approach. I've seen some people suggest that tables should have an auto-incrementing ("identity") int as a clustered primary key even if it doesn't have any meaning, as it means that the database engine itself can use that value to quickly look up a row instead of having to use a bookmark. My database is merge-replicated across a bunch of servers, so I've shied away from identity int columns as they're a bit hairy to get right in replication. What are your thoughts? Should tables have primary keys? Or is it ok to not have any clustered indexes if there are no sensible columns to index that way?

    Read the article

  • Can't return a List from a Compiled Query.

    - by Andrew
    I was speeding up my app by using compiled queries for queries which were getting hit over and over. I tried to implement it like this: Function Select(ByVal fk_id As Integer) As List(SomeEntity) Using db As New DataContext() db.ObjectTrackingEnabled = False Return CompiledSelect(db, fk_id) End Using End Function Shared CompiledSelect As Func(Of DataContext, Integer, List(Of SomeEntity)) = _ CompiledQuery.Compile(Function(db As DataContext, fk_id As Integer) _ (From u In db.SomeEntities _ Where u.SomeLinkedEntity.ID = fk_id _ Select u).ToList()) This did not work and I got this error message: Type : System.ArgumentNullException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Value cannot be null. Parameter name: value However, when I changed my compiled query to return IQueryable instead of List like so: Function Select(ByVal fk_id As Integer) As List(SomeEntity) Using db As New DataContext() db.ObjectTrackingEnabled = False Return CompiledSelect(db, fk_id).ToList() End Using End Function Shared CompiledSelect As Func(Of DataContext, Integer, IQueryable(Of SomeEntity)) = _ CompiledQuery.Compile(Function(db As DataContext, fk_id As Integer) _ From u In db.SomeEntities _ Where u.SomeLinkedEntity.ID = fk_id _ Select u) It worked fine. Can anyone shed any light as to why this is? BTW, compiled queries rock! They sped up my app by a factor of 2.

    Read the article

  • Oracle - Parameterized Query has EXECUTIONS = PARSE_CALLS

    - by Cory Grimster
    We have a .NET application talking to Oracle 10g. Our DBA recently pulled a list of queries where executions is equal to parse_calls. We assumed that this would help us find all of the unparameterized queries in our code. Unexpectedly, the following query showed up near the top of this list, with 1,436,169 executions and 1,436,151 parses: SELECT bar.foocolumn FROM bartable bar, baztable baz WHERE bar.some_id = :someId AND baz.another_id = :anotherId AND baz.some_date BETWEEN bar.start_date AND (nvl(bar.end_date, baz.some_date + (1/84600)) - (1/84600)) Why is executions equal to parse_calls for this query?

    Read the article

  • Why Banks or Financial Companies prefer Oracle than other RDBMS for their "Core" systems?

    - by edwin.nathaniel
    I'd like to know why most Banks or Financial companies prefer Oracle than other RDBMS for their core systems (the absolutely minimum features that a Bank must support). I found a few answers that didn't satisfy me. For example: Oracle has more features. But features for what? Can't you implement that in application level if you were not using Oracle? Could someone please describe a bit more technical but still on high-level overview of what the bank needs and how Oracle would solve it and the others can't or don't have the features yet? I came from the web-app (web 2.0) crowd who normally hear news about MySQL, PostgreSQL or even key-value/column-oriented storage solution. I have almost zero knowledge on how Banks or Financial companies operates from technical perspective. Thank you, Ed

    Read the article

  • How to designing a generic databse whos layout may change over time?

    - by mawg
    Here's a tricky one - how do I programatically create and interrogate a database who's contents I can't really foresee? I am implementing a generic input form system. The user can create PHP forms with a WYSIWYG layout and use them for any purpose he wishes. He can also query the input. So, we have three stages: a form is designed and generated. This is a one-off procedure, although the form can be edited later. This designs the database. someone or several people make use of the form - say for daily sales reports, stock keeping, payroll, etc. Their input to the forms is written to the database. others, maybe management, can query the database and generate reports. Since these forms are generic, I can't predict the database structure - other than to say that it will reflect HTML form fields and consist of a the data input from collection of edit boxes, memos, radio buttons and the like. Questions and remarks: A) how can I best structure the database, in terms of tables and columns? What about primary keys? My first thought was to use the control name to identify each column, then I realized that the user can edit the form and rename, so that maybe "name" becomes "employee" or "wages" becomes ":salary". I am leaning towards a unique number for each. B) how best to key the rows? I was thinking of a timestamp to allow me to query and a column for the row Id from A) C) I have to handle column rename/insert/delete. Foe deletion, I am unsure whether to delete the data from the database. Even if the user is not inputting it from the form any more he may wish to query what was previously entered. Or there may be some legal requirements to retain the data. Any gotchas in column rename/insert/delete? D) For the querying, I can have my PHP interrogate the database to get column names and generate a form with a list where each entry has a database column name, a checkbox to say if it should be used in the query and, based on column type, some selection criteria. That ought to be enough to build searches like "position = 'senior salesman' and salary 50k". E) I probably have to generate some fancy charts - graphs, histograms, pie charts, etc for query results of numerical data over time. I need to find some good FOSS PHP for this. F) What else have I forgotten? This all seems very tricky to me, but I am database n00b - maybe it is simple to you gurus?

    Read the article

  • Aggregate Functions on subsets of data based on current row values with SQL

    - by aasukisuki
    Hopefully that title makes sense... Let's say I have an employee table: ID | Name | Title | Salary ---------------------------- 1 | Bob | Manager | 15285 2 | Joe | Worker | 10250 3 | Al | Worker | 11050 4 | Paul | Manager | 16025 5 | John | Worker | 10450 What I'd like to do is write a query that will give me the above table, along with an averaged salary column, based on the employee title: ID | Name | Title | Salary | Pos Avg -------------------------------------- 1 | Bob | Manager | 15285 | 15655 2 | Joe | Worker | 10250 | 10583 3 | Al | Worker | 11050 | 10583 4 | Paul | Manager | 16025 | 15655 5 | John | Worker | 10450 | 10583 I've tried doing this with a sub-query along the lines of: Select *, (select Avg(e2.salary) from employee e2 where e2.title = e.title) from employee e But I've come to realize that the sub-query is executed first, and has no knowledge of the table alias'd e I'm sure I'm missing something REALLY obvious here, can anyone point me in the right diretion?

    Read the article

  • LINQtoSQL Custom Constructor off Partial Class?

    - by sah302
    Hi all, I read this question here: http://stackoverflow.com/questions/82409/is-there-a-way-to-override-the-empty-constructor-in-a-class-generated-by-linqtosq Typically my constructor would look like: public User(String username, String password, String email, DateTime birthday, Char gender) { this.Id = Guid.NewGuid(); this.DateCreated = this.DateModified = DateTime.Now; this.Username = username; this.Password = password; this.Email = email; this.Birthday = birthday; this.Gender = gender; } However, as read in that question, you want to use partial method OnCreated() instead to assign values and not overwrite the default constructor. Okay so I got this : partial void OnCreated() { this.Id = Guid.NewGuid(); this.DateCreated = this.DateModified = DateTime.Now; this.Username = username; this.Password = password; this.Email = email; this.Birthday = birthday; this.Gender = gender; } However, this gives me two errors: Partial Methods must be declared private. Partial Methods must have empty method bodies. Alright I change it to Private Sub OnCreated() to remove both of those errors. However I am still stuck with...how can I pass it values as I would with a normal custom constructor? Also I am doing this in VB (converted it since I know most know/prefer C#), so would that have an affect on this?

    Read the article

  • Not getting the right expected output for my Mysql Query?

    - by user1878107
    i've 4 tables as shown below doctors id name ------------ 1 Mathew 2 Praveen 3 Rosie 4 Arjun 5 Denis doctors_appointments id doctors_id patient_name contact date status -------------------------------------------------------------------------------------- 1 5 Nidhin 9876543210 2012-12-10 15:39:41 Registered 2 5 Sunny 9876543210 2012-12-18 15:39:48 Registered 3 5 Mani 9876543210 2012-12-12 15:39:57 Registered 4 2 John 9876543210 2012-12-24 15:40:09 Registered 5 4 Raj 9876543210 2012-12-05 15:41:57 Registered 6 3 Samuel 9876543210 2012-12-14 15:41:33 Registered 7 2 Louis 9876543210 2012-12-24 15:40:23 Registered 8 1 Federick 9876543210 2012-12-28 15:41:05 Registered 9 2 Sam 9876543210 2012-12-12 15:40:38 Registered 10 4 Sita 9876543210 2012-12-12 15:41:00 Registered doctors_dutyplan id doctor_id weeks time no_of_patients ------------------------------------------------------------------ 1 1 3,6,7 9:00am-1:00pm 10 2 2 3,4,5 1:00pm-4:00pm 7 3 3 3,6,7 10:00am-2:00pm 10 4 4 3,4,5,6 8:30am-12:30pm 12 5 5 3,4,5,6,7 9:00am-4:00pm 30 emp_leave id empid leavedate -------------------------------- 1 2 2012-12-05 14:42:36 2 2 2012-12-03 14:42:59 3 3 2012-12-03 14:43:06 4 3 2012-12-06 14:43:14 5 5 2012-12-04 14:43:24 My task is to find all the days in a month in which the doctor is available excluding the leave dates. My query what is wrote is given below: SELECT DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) AS Date, ROW +1 AS DayOfMonth FROM ( SELECT @row := @row +1 AS ROW FROM ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 )t1, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 )t2, ( SELECT @row := -1 )t3 LIMIT 31 )b WHERE DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) BETWEEN '2012-12-01' AND '2012-12-31' AND DAYOFWEEK( DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) ) =2 AND DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) NOT IN ( SELECT DATE_FORMAT( l.leavedate, '%Y-%m-%d' ) AS date FROM doctors_dutyplan d LEFT JOIN emp_leave AS l ON d.doctor_id = l.empid WHERE doctor_id =2 ) This works fine for all doctors who took any leave in a particular day in a month (here in the example it is Decemeber 2012). and the result is shown below: Date DayOfMonth ----------------------- 2012-12-10 10 2012-12-17 17 2012-12-24 24 2012-12-31 31 But on the other hand for the doctors who did'nt took any leave , for that my query is showing empty table, example for the doctor Mathew whose id is 1, my query returns an empty result can anyone please tell a solution for this problem. Thanks in advance.

    Read the article

  • Any way to speed up this hierarchical query?

    - by RenderIn
    I've got a serious performance problem with a hierarchical query that I can't seem to fix. I am modeling several organization charts in my database, each representing a virtual organization within our company. For example, we have several temporary committees that are created from time to time and there may be a Committee Organizer role at the top of this virtual hierarchy, with several people assigned to the Committee Member role beneath the organizer. Some of our virtual organizations have many levels and several branches at each level. I have a single table in which I represent all the role assignments. i.e. a ROLE_ID column and a PARENT_ROLE_ID column which is a foreign key to the ROLE_ID column. For each assignment we also store as a column the location in the company where this person has the assignment. For example, the Committee Organizer would have a company-level/ CEO assignment, while the committee members would have department-level assignments such as ACCOUNTING, MARKETING, etc. So to model the organizer/member relationship for two individuals we would have: ROLE_ID = 4 PARENT_ROLE_ID = NULL EMPLOYEE_NUMBER = 213423 COMPANY_LOCATION = CEO ROLE_ID = 5 PARENT_ROLE_ID = 4 EMPLOYEE_NUMBER = 838221 COMPANY_LOCATION = ACCOUNTING Here's where things get tricky. I have an application that every person in the organization can log in to. When they log in they should be able to view all the virtual organizations in our company. e.g. the committee members should be able to see the committee organizer and vice-versa. However, only the committee organizer should be able to edit the committee members. The difficulty is in determining whether an individual (who can have multiple role assignments) has edit access for each other assignment. While this seems simple in the example, consider a virtual organization in which we have President at the top, 5 departments directly beneath him, 2 subdepartments below each department. We only want people in the Accounting department to be able to edit individuals in the subdepartments belonging to the Accounting department. They should not have edit access to anybody in the Marketing department or its subdepartments. To determine edit access when a user views a virtual organization in our company I run a query that executes two inline views: A) Hierarchically query for all assignments in this virtual organization and using SYS_CONNECT_BY_PATH to store the entire path to each user/role/company_location and B) Hierarchically retrieve all the assignments the individual logged in has and using the SYS_CONNECT_BY_PATH to store the entire path to each of these assignments. The result of the query is all the records from A) plus a boolean determined by joining with B) which flags whether the logged in user has edit access for each record. Indexes don't seem to be helping... it simply appears that there is too much processing going on to separate all the records and then determine edit access. One issue is that I can't store the SYS_CONNECT_BY_PATH and index it... determining whether an individual record has edit access consists of comparing if: test_record_sys_path LIKE individual_record_sys_path || '%' Is a materialized view the answer?

    Read the article

  • How to debug issues with differing execution times in different contexts.

    - by Dave
    The following question seems to be haunting me more consistently than most other questions recently. What kinds of things would you suggest I suggest that they look for when trying to debug "performance issues" like this? ok, get this - running this in query analyzer takes < 1 second exec usp_MyAccount_Allowance_Activity '1/1/1900', null, 187128 debugging locally, this takes 10 seconds: DataSet allowanceBalance = SqlHelper.ExecuteDataset( WebApplication.SQLConn(), CommandType.StoredProcedure, "usp_MyAccount_Allowance_Activity", Params); same parameters

    Read the article

  • Best way to store sales tax information

    - by Seph
    When designing a stock management database system (sales / purchases) what would be the best way to store the various taxes and other such amounts? A few of the fields that could be saved are: Unit price excluding tax Unit price including tax Tax per item Total excluding tax (rounded to 2 decimals) Total including tax (rounded to 2 decimals) Total tax (rounded to 2 decimals) Currently the most reasonable solution so far is storing down (roughly) item, quantity, total excluding tax (rounded) and the total tax (rounded). Can anyone suggest some better way of storing this details for a generic system? Also, given the system needs to be robust, what should be done if there were multiple tax values (eg: state and city) which might need to be separated, in this case a separate table would be in order, but would it be considered excessive to just have a rowID and some taxID mapping to a totalTax column?

    Read the article

  • TooManyRowsAffectedException with encrypted triggers

    - by Jon Masters
    I'm using nHibernate to update 2 columns in a table that has 3 encrypted triggers on it. The triggers are not owned by me and I can not make changes to them, so unfortunately I can't SET NOCOUNT ON inside of them. Is there another way to get around the TooManyRowsAffectedException that is thrown on commit? Update 1 So far only way I've gotten around the issue is to step around the .Save routine with var query = session.CreateSQLQuery("update Orders set Notes = :Notes, Status = :Status where OrderId = :Order"); query.SetString("Notes", orderHeader.Notes); query.SetString("Status", orderHeader.OrderStatus); query.SetInt32("Order", orderHeader.OrderHeaderId); query.ExecuteUpdate(); It feels dirty and is not easily to extend, but it doesn't crater.

    Read the article

  • ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

    - by Nitin Gurram
    I am trying to update the table which has millions of records. However my update query will update around 2-3 millions of records. I am facing below error on executing the update query. I googled and found that I need to update the Table Space as DBA But is there any work around for executing the update without actually extending the UNDO table space or something dba is not required UPDATESERVICE SET CREATION_TIME = LAST_UPDATE_TIME WHERE CREATION_TIME is null

    Read the article

  • select rows with column that is not null?

    - by fayer
    by default i have one column in mysql table to be NULL. i want to select some rows but only if the field value in that column is not NULL. what is the correct way of typing it? $query = "SELECT * FROM names WHERE id = '$id' AND name != NULL"; is this correct?

    Read the article

  • MySQL query advice

    - by vasion
    I am lost in MySQL documentation. I have a table with votes - it has these columns id song_id user_id created I cannot find the query which will process the information and output the 10 most voted songs in a given time period. What is it?

    Read the article

  • Compiled Queries and "Parameters cannot be sequences"

    - by David B
    I thought that compiled queries would perform the same query translation as DataContext. Yet I'm getting a run-time error when I try to use a query with a .Contains method call. Where have I gone wrong? //private member which holds a compiled query. Func<DataAccess.DataClasses1DataContext, List<int>, List<DataAccess.TestRecord>> compiledFiftyRecordQuery = System.Data.Linq.CompiledQuery.Compile <DataAccess.DataClasses1DataContext, List<int>, List<DataAccess.TestRecord>> ((dc, ids) => dc.TestRecords.Where(tr => ids.Contains(tr.ID)).ToList()); //this method calls the compiled query. public void FiftyRecordCompiledQueryByID() { List<int> IDs = GetRandomInts(50); //System.NotSupportedException //{"Parameters cannot be sequences."} List<DataAccess.TestRecord> results = compiledFiftyRecordQuery (myContext, IDs); }

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 608 609 610 611 612 613 614 615 616 617 618 619  | Next Page >