Search Results

Search found 79588 results on 3184 pages for 'sql data storage'.

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 380/3184 | < Previous Page | 376 377 378 379 380 381 382 383 384 385 386 387  | Next Page >

  • LinqToSQL not updating database

    - by codegarten
    Hi. I created a database and dbml in visual studio 2010 using its wizards. Everything was working fine until i checked the tables data (also in visual studio server explorer) and none of my updates were there. using (var context = new CenasDataContext()) { context.Log = Console.Out; context.Cenas.InsertOnSubmit(new Cena() { id = 1}); context.SubmitChanges(); } This is the code i am using to update my database. At this point my database has one table with one field (PK) named ID. *INSERT INTO [dbo].Cenas VALUES (@p0) -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1* This is LOG from the execution (printed the context log into the console). The problem i'm having is that these updates are not persistent in the database. I mean that when i query my database (visual studio server explorer - new query) i see the table is empty, every time. I am using a SQL Server database file (.mdf).

    Read the article

  • Requested operation requires an OLE DB Session object... - Connecting Excel to SQL server via ADO

    - by Frank V
    I'm attempting to take Excel 2003 and connect it to SQL Server 2000 to run a few dynamicly generated SQL Queries which ultimately filling certain cells. I'm attempting to do this via VBA via ADO (I've tried 2.8 to 2.0) but I'm getting an error while setting the ActiveConnection variable which is inside the ADODB.Connection object. I need to resolve this pretty quick... Requested operation requires an OLE DB Session object, which is not supported by the current provider. I'm honestly not sure what this error means and right now I don't care. How can get this connection to succeed so that I can run my queries? Here is my VB code: Dim SQL As String, RetValue As String SQL = " select top 1 DateTimeValue from SrcTable where x='value' " 'Not the real SQL RetValue = "" Dim RS As ADODB.Recordset Dim Con As New ADODB.Connection Dim Cmd As New ADODB.Command Con.ConnectionString = "Provider=sqloledb;DRIVER=SQL Server;Data Source=Server\Instance;Initial Catalog=MyDB_DC;User Id=<UserName>;Password=<Password>;" Con.CommandTimeout = (60 * 30) Set Cmd.ActiveConnection = Con ''Error occurs here. ' I'm not sure if the rest is right. I've just coded it. Can't get past the line above. Cmd.CommandText = SQL Cmd.CommandType = adCmdText Con.Open Set RS = Cmd.Execute() If Not RS.EOF Then RetValue = RS(0).Value Debug.Print "RetValue is: " & RetValue End If Con.Close I imagine something is wrong with the connection string but I've tried over a dozen variations. Now I'm just shooting in the dark.... Note/Update: To make matters more confusing, if I Google for the error quote above, I get a lot of hits back but nothing seems relevant or I'm not sure what information is relevant.... I've got the VBA code in "Sheet1" under "Microsoft Excel Objects." I've done this before but usually put things in a module. Could this make a difference?

    Read the article

  • Custom Detail in Linq-to-SQL Master-Detail DataGridViews

    - by Andres
    Hi, looking for a way to create Linq-to-SQL Master-Detail WinForms DataGridViews where the Detail part of the Linq query is a custom one. Can do fine this: DataClasses1DataContext db = new DataClasses1DataContext(".\\SQLExpress"); var myQuery = from o in db.Orders select o; dataGridView1.DataSource = new BindingSource() { DataSource = myQuery }; dataGridView2.DataSource = new BindingSource() { DataSource = dataGridView1.DataSource, DataMember = "OrderDetails" }; but I'd like to have the Detail part under my precise control, like var myQuery = from o in db.Orders join od in db.OrderDetails on o.ID equals od.OrderID into MyOwnSubQuery select o; and use it for the second grid: dataGridView2.DataSource = new BindingSource() { DataSource = dataGridView1.DataSource, DataMember = "MyOwnSubQuery" // not working... }; the real reason I want it is a bit more complex (I'd like to have the Detail part to be some not-pre-defined join actually), but I'm hoping the above conveyed the idea. Can I only have the Detail part as a plain sub-table coming out of the pre-defined relation or can I do more complex stuff with the Detail part? Does anyone else feel this is kind of limited (if the first example is the best we can do)? Thanks!

    Read the article

  • SQL Inner Join : DB stuck

    - by SurfingCat
    I postet this question a few days ago but I didn't explain exactly what I want. I ask the question better formulated again: To clarify my problem I added some new information: I got an MySQL DB with MyISAM tables. The two relevant tables are: * orders_products: orders_products_id, orders_id, product_id, product_name, product_price, product_name, product_model, final_price, ... * products: products_id, manufacturers_id, ... (for full information about the tables see screenshot products (Screenshot) and screenshot orders_products (Screenshot)) Now what I want is this: - Get all Orders who ordered products with manufacturers_id = 1. And the product name of the product of this order (with manufacturers_id = 1). Grouped by orders. What I did so far is this: SELECT op.orders_id, p.products_id, op.products_name, op.products_price, op.products_quantity FROM orders_products op , products p INNER JOIN products ON op.products_id = p.products_id WHERE p.manufacturers_id = 1 AND p.orders_id > 10000 p.orders_id 10000 for testing to get only a few order_id's. But thies query takes much time to get executed if it even works. Two times the sql server stucked. Where is the mistake?

    Read the article

  • What is a good automated data import method for SQL Server?

    - by Joel Potter
    I'm in the process of porting some SQL Server 2005 databases to SQL Server 2008. One of these databases has an associated import application (Windows task) which uses SSIS with a DTS package to import a large dataset from an MS Access database nightly. In upgrading to SQL Server 2008, I discovered that I can't run the same console application which has been performing the imports due to the missing manageddts DLL in SQL Server 2008. It's several years old and in need of a rewrite for various reason, plus, I've been fairly unhappy with DTS in general. The original reason DTS was chosen was for speed (5 min import time compared to 30+ for ADO.NET). The format of the data to import is out of my control (the client likes Access). I would also like to be able to run the import from a machine completely separate from the server hosting SQL Server and preferably with minimal SQL features installed. Options I've considered: Creating an Access application to connect to both databases (SQL Server and Access) and perform the import (Ugh!) Revisiting ADO.NET to see if the original implementation was poorly written. Updated SSIS packages. What other technologies should I be considering for this job?

    Read the article

  • ADO.NET Data Services for MySQL

    - by Shalan
    Hey! I've searched high and low for this, and no luck. Is there a way that CRUD methods for a MySQL install (Linux box) be exposed via ADO.NET WCF Data Services? I would really love to leverage this in my WPF app :) Thank u!

    Read the article

  • SQL Update help

    - by Cyborgo
    I have a really simple question, is it possible to update a table with new values using just one update statement. Say for example I have a table with author, title, date, popularity. Now I got some new data which has author name, title corresponding new popularity. How do I update the table now in one statement. Note that author and title are not unique.

    Read the article

  • SSL over TDS, SQL Server 2005 Express

    - by reuvenab
    I capture packets sent/received by Win Xp machine when connecting to SQL Server 2005 Express using TLS encryption. Server and Client exchange Hello messages Server and Client send ChangeCipherSpec message Then Server and Client server send strange message that is not described in TLS protocol What is the message and if SSL over TDS is standard compliant at all? Server side capture: 16 **SSL Handshake** 03 01 00 4a 02 ServerHello 00 00 46 03 01 4b dd 68 59 GMT 33 13 37 98 10 5d 57 9d ff 71 70 dc d6 6f 9e 2c Random[00..13] cb 96 c0 2e b3 2f 9b 74 67 05 cc 96 Random[14..27] 20 72 26 00 00 0f db 7f d9 b0 51 c2 4f cd 81 4c Session ID 3f e3 d2 d1 da 55 c0 fe 9b 56 b7 6f 70 86 fe bb Session ID 54 Session ID 00 04 Cipher Suite 00 Compression 14 03 01 00 01 01 **ChangeCipherSpec** 16 03 01 ???? Finished ??? 00 20 d0 da cc c4 36 11 43 ff 22 25 8a e1 38 2b ???? ??? 71 ce f3 59 9e 35 b0 be b2 4b 1d c5 21 21 ce 41 ???? ??? 8e 24

    Read the article

  • New Marketing Assets Available

    - by swalker
    NEW translated demand generation materials available for the following Oracle Marketing Kits, designed to help partners generate sales around Oracle's solutions: Server & Storage: Improve Database Capacity Management with Oracle Storage and Hybrid Columnar Compression Server & Storage: Accelerating Database Test & Development with Sun ZFS Storage Appliance Server & Storage: Upgrade SAN Storage to Oracle Pillar Axiom Server & Storage: SPARC Refresh with Oracle Solaris Operating System Server & Storage: SPARC Server Refresh: The Next Level of Datacenter Performance with Oracle’s New SPARC Servers Server & Storage: Oracle Server Virtualization Server & Storage: Oracle Desktop Virtualization

    Read the article

  • Online graphing and data visualization frameworks

    - by Marques
    I have been looking around at web applications and websites with rich graphs, charts, and data visualization and for the most part have been able to determine which frameworks or tools websites are using. However I was looking over 'resumup.com' and couldn't determine what they are using. Does anyone know off hand or can you tell? It doesn't seem like any javascript framework i've seen unless its custom...is it some sort of flash or flex framework? Any help would be greatly appreciated. Thanks, Marques

    Read the article

  • ASP.net MVC Linq-To-SQL Many-To-Many Field Binding

    - by user336858
    Hi there, The short version of this question is "Is there a way to gracefully handle database insertion for an object that has a many-to-many field that has been set up in a partial class?" Apologies if it's been asked before. Example Suppose I have a typical MVC setup with the tables: Posts {PostID, ...} Categories {CategoryID, ...} A post can have more than one category, and a category can identify more than one post. Thus suppose further that I need an extra table: PostCategories {PostID, CategoryID, ...} This handles the many-to-many relationship between posts and categories. As far as I know, there's no way to do this in Linq-to-SQL right now so I have to shoehorn it in by adding a partial Post class to the project to add that functionality. Something like: public partial class Post { public IEnumerable<Category> Categories{ get { ... } set { ... } } } So I can now create a "Create" view that automatically populates a "Categories" UI item. This is where the trouble starts. So here's my question: How do you get automatic object model binding to work cleanly with an object that has a many-to-many relationship to control? The workaround that makes many-to-many relationships possible relies on the Post object having a PostID in order to be associated with CategoryID(s), which is only issued after the Post object has been submitted for validation and insertion. Bit of a Catch22 here. Any terminology, links, or tips you can provide would be tremendously helpful!

    Read the article

  • MVC4 Model in View has nested data - cannot get data in model

    - by Taersious
    I have a Model defined that gets me a View with a list of RadioButtons, per IEnumerable. Within that Model, I want to display a list of checkboxes that will vary based on the item selected. Finally, there will be a Textarea in the same view once the user has selected from the available checkboxes, with some dynamic text there based on the CheckBoxes that are selected. What we should end up with is a Table-per-hierarchy. The layout is such that the RadioButtonList is in the first table cell, the CheckBoxList is in the middle table cell, and the Textarea is ini the right table cell. If anyone can guide me to what my model-view should be to achieve this result, I'll be most pleased... Here are my codes: // // View Model for implementing radio button list public class RadioButtonViewModel { // objects public List<RadioButtonItem> RadioButtonList { get; set; } public string SelectedRadioButton { get; set; } } // // Object for handling each radio button public class RadioButtonItem { // this object public string Name { get; set; } public bool Selected { get; set; } public int ObjectId { get; set; } // columns public virtual IEnumerable<CheckBoxItem> CheckBoxItems { get; set; } } // // Object for handling each checkbox public class CheckBoxViewModel { public List<CheckBoxItem> CheckBoxList { get; set; } } // // Object for handling each check box public class CheckBoxItem { public string Name { get; set; } public bool Selected { get; set; } public int ObjectId { get; set; } public virtual RadioButtonItem RadioButtonItem { get; set; } } and the view @model IEnumerable<EF_Utility.Models.RadioButtonItem> @{ ViewBag.Title = "Connect"; ViewBag.Selected = Request["name"] != null ? Request["name"].ToString() : ""; } @using (Html.BeginForm("Objects" , "Home", FormMethod.Post) ){ @Html.ValidationSummary(true) <table> <tbody> <tr> <td style="border: 1px solid grey; vertical-align:top;"> <table> <tbody> <tr> <th style="text-align:left; width: 50px;">Select</th> <th style="text-align:left;">View or Table Name</th> </tr> @{ foreach (EF_Utility.Models.RadioButtonItem item in @Model ) { <tr> <td> @Html.RadioButton("RadioButtonViewModel.SelectedRadioButton", item.Name, ViewBag.Selected == item.Name ? true : item.Selected, new { @onclick = "this.form.action='/Home/Connect?name=" + item.Name + "'; this.form.submit(); " }) </td> <td> @Html.DisplayFor(i => item.Name) </td> </tr> } } </tbody> </table> </td> <td style="border: 1px solid grey; width: 220px; vertical-align:top; @(ViewBag.Selected == "" ? "display:none;" : "")"> <table> <tbody> <tr> <th>Column </th> </tr> <tr> <td><!-- checkboxes will go here --> </td> </tr> </tbody> </table> </td> <td style="border: 1px solid grey; vertical-align:top; @(ViewBag.Selected == "" ? "display:none;" : "")"> <textarea name="output" id="output" rows="24" cols="48"></textarea> </td> </tr> </tbody> </table> } and the relevant controller public ActionResult Connect() { /* TEST SESSION FIRST*/ if( Session["connstr"] == null) return RedirectToAction("Index"); else { ViewBag.Message = ""; ViewBag.ConnectionString = Server.UrlDecode( Session["connstr"].ToString() ); ViewBag.Server = ParseConnectionString( ViewBag.ConnectionString, "Data Source" ); ViewBag.Database = ParseConnectionString( ViewBag.ConnectionString, "Initial Catalog" ); using( var db = new SysDbContext(ViewBag.ConnectionString)) { var objects = db.Set<SqlObject>().ToArray(); var model = objects .Select( o => new RadioButtonItem { Name = o.Name, Selected = false, ObjectId = o.Object_Id, CheckBoxItems = Enumerable.Empty<EF_Utility.Models.CheckBoxItem>() } ) .OrderBy( rb => rb.Name ); return View( model ); } } } What I am missing it seems, is the code in my Connect() method that will bring the data context forward; at that point, it should be fairly straight-forward to set up the Html for the View. EDIT ** So I am going to need to bind the RadioButtonItem to the view with something like the following, except my CheckBoxList will NOT be an empty set. // // POST: /Home/Connect/ [HttpPost] public ActionResult Connect( RadioButtonItem rbl ) { /* TEST SESSION FIRST*/ if ( Session["connstr"] == null ) return RedirectToAction( "Index" ); else { ViewBag.Message = ""; ViewBag.ConnectionString = Server.UrlDecode( Session["connstr"].ToString() ); ViewBag.Server = ParseConnectionString( ViewBag.ConnectionString, "Data Source" ); ViewBag.Database = ParseConnectionString( ViewBag.ConnectionString, "Initial Catalog" ); using ( var db = new SysDbContext( ViewBag.ConnectionString ) ) { var objects = db.Set<SqlObject>().ToArray(); var model = objects .Select( o => new RadioButtonItem { Name = o.Name, Selected = false, ObjectId = o.Object_Id, CheckBoxItems = Enumerable.Empty<EF_Utility.Models.CheckBoxItem>() } ) .OrderBy( rb => rb.Name ); return View( model ); } } }

    Read the article

  • Delaying LINQ to SQL Select Query Execution

    - by Maxim Z.
    I'm building an ASP.NET MVC site that uses LINQ to SQL. In my search method that has some required and some optional parameters, I want to build a LINQ query while testing for the existence of those optional parameters. Here's what I'm currently thinking: using(var db = new DBDataContext()) { IQueryable<Listing> query = null; //Handle required parameter query = db.Listings.Where(l => l.Lat >= form.bounds.extent1.latitude && l.Lat <= form.bounds.extent2.latitude); //Handle optional parameter if (numStars != null) query = query.Where(l => l.Stars == (int)numStars); //Other parameters... //Execute query (does this happen here?) var result = query.ToList(); //Process query... Will this implementation "bundle" the where clauses and then execute the bundled query? If not, how should I implement this feature? Also, is there anything else that I can improve? Thanks in advance.

    Read the article

  • Entity Framework 4 and SQL Compact 4: How to generate database?

    - by David Veeneman
    I am developing an app with Entity Framework 4 and SQL Compact 4, using a Model First approach. I have created my EDM, and now I want to generate a SQL Compact 4.0 database to act as a data store for the model. I bring up the Generate Database Wizard and click the New Connection button to create a connection for the generated file. The Choose Data Source dialog appears, but SQL Compact 4.0 is not listed in the list of available data sources: I am running VS 2010 SP1 (beta) and I have installed the VS 2010 Tools for SQL Compact 4.0. I can create a SQL Compact 4.0 data connection from the Server Explorer. It is only in the Generate Database Wizard that the 4.0 option doesn't appear. BTW, my SQL Compact 4.0 installation does include System.Data.SqlServerCe.Entity.dll. So I should have the SQL Compact components I need. Am I doing something incorrectly, or is this a bug? Does anyone have a fix or a workaround? Thanks for your help.

    Read the article

  • Updating UISearchDisplayController with Core Data results using GCD

    - by Brian Halpin
    I'm having trouble displaying the results from Core Data in my UISearchDisplayController when I implement GCD. Without it, it works, but obviously blocks the UI. In my SearchTableViewController I have the following two methods: - (BOOL)searchDisplayController:(UISearchDisplayController *)controller shouldReloadTableForSearchString:(NSString *)searchString { // Tell the table data source to reload when text changes [self filterContentForSearchText:searchString]; // Return YES to cause the search result table view to be reloaded. return YES; } // Update the filtered array based on the search text -(void)filterContentForSearchText:(NSString*)searchText { // Remove all objects from the filtered search array [self.filteredLocationsArray removeAllObjects]; NSPredicate *predicate = [CoreDataMaster predicateForLocationUsingSearchText:@"Limerick"]; CoreDataMaster *coreDataMaster = [[CoreDataMaster alloc] init]; // Filter the array using NSPredicate self.filteredLocationsArray = [NSMutableArray arrayWithArray: [coreDataMaster fetchResultsFromCoreDataEntity:@"City" UsingPredicate:predicate]]; } You can probably guess that my problem is with returning the array from [coreDataMaster fetchResultsFromCoreDataEntity]. Below is the method: - (NSArray *)fetchResultsFromCoreDataEntity:(NSString *)entity UsingPredicate:(NSPredicate *)predicate { NSMutableArray *fetchedResults = [[NSMutableArray alloc] init]; dispatch_queue_t coreDataQueue = dispatch_queue_create("com.coredata.queue", DISPATCH_QUEUE_SERIAL); dispatch_async(coreDataQueue, ^{ NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init]; NSEntityDescription *entityDescription = [NSEntityDescription entityForName:entity inManagedObjectContext:self.managedObjectContext]; NSSortDescriptor *nameSort = [[NSSortDescriptor alloc] initWithKey:@"name" ascending:YES]; NSArray *sortDescriptors = [NSArray arrayWithObjects:nameSort, nil]; [fetchRequest setEntity:entityDescription]; [fetchRequest setSortDescriptors:sortDescriptors]; // Check if predicate is set if (predicate) { [fetchRequest setPredicate:predicate]; } NSError *error = nil; NSArray *fetchedManagedObjects = [self.managedObjectContext executeFetchRequest:fetchRequest error:&error]; for (City *city in fetchedManagedObjects) { [fetchedResults addObject:city]; } NSDictionary *userInfo = [NSDictionary dictionaryWithObject:[NSArray arrayWithArray:fetchedResults] forKey:@"results"]; [[NSNotificationCenter defaultCenter] postNotificationName:@"fetchResultsComplete" object:nil userInfo:userInfo]; }); return [NSArray arrayWithArray:fetchedResults]; } So the thread hasn't finished executing by the time it returns the results to self.filteredLocationsArray. I've tried added a NSNotification which passes the NSDictionary to this method: - (void)updateSearchResults:(NSNotification *)notification { NSDictionary *userInfo = notification.userInfo; NSArray *array = [userInfo objectForKey:@"results"]; self.filteredLocationsArray = [NSMutableArray arrayWithArray:array]; [self.tableView reloadData]; } I've also tried refreshing the searchViewController like [self.searchDisplayController.searchResultsTableView reloadData]; but to no avail. I'd really appreciate it if someone could point me in the right direction and show me where I might be going wrong. Thanks

    Read the article

  • Insert data in an object to a database.

    - by paul
    I am facing the following design/implementation dilemma. I have a class Customer which is below with getters and setters. I would like to insert the value of the Customer into a "Customer" table of a database. But Customer has an address which is of type "Address". How do I go about inserting this field into the database?(I am using sqlite3). I thought of writing a separate table "Address(customerId,doorNo,city,state,country,pinCode)". But I am having second thoughts about generating the primary key(customerId) which should be same for both the "customer" and "Address" table. Sqlite3 faq states that I can do "Integer Primary Key" to use the field to generate an auto number. But if I do that in customer table, I would have to retrieve the same Id to be used in Address table. This kinda looks wrong to me :-?. There should be an elegant method to solve this. Any ideas would be much appreciated. Thanks in advance. import java.io.*; import java.sql.*; class Customer { private String id; private String name; private Address address; private Connection connection; private ResultSet resultSet; private PreparedStatement preparedStatement; public void insertToDatabase(){ } } class Address{ private String doorNumber; private String streetName; private String cityName; private String districtName; private String stateName; private String countryName; private long pinCode; }

    Read the article

  • SQL Query to duplicate records based on If statement

    - by user328371
    Hi, I'm trying to write an SQL query that will duplicate records depending on a field in another table. I am running mySQL 5. (I know duplicating records shows that the database structure is bad, but I did not design the database and am not in a position to redo it all - it's a shopp ecommerce database running on wordpress.) Each product with a particular attribute needs a link to the same few images, so the product will need a row per image in a table - the database doesn't actually contain the image, just its filename. (the images are of clipart for a customer to select from) Based on these records... SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes' I want to do something like this.. For each record that matches that query, copy records 5134 - 5139 from wp_shopp_asset but change the id so it's unique and set the cell in column 'parent' to have the value of 'product' from the table wp_shopp_spec. This will mean 6 new records are created for each record matching the above query, all with the same value in 'parent' but with unique ids and every other column copied from the original (ie. records 5134-5139) Hope that's clear enough - any help greatly appreciated.

    Read the article

  • linq to sql with nservicebus table lock issue

    - by IGoor
    I am building a system using NServiceBus and my DataLayer is using Linq 2 SQL. The system is made up of 2 services. Service1 receives messages from NSB. It will query Table1 in my database and inserts a record into Table1 If a certain condition is met a new NSB message is sent to the 2nd service Service2 will update records also in Table1 when it receives messages from Service1 and it does some other non database related work. Service2 is a long running process. The problem I am having is the moment Service2 updates a record in Table1, the table is locked. The lock seems to be in place until Service2 has completed all it is processing. i.e. The lock is not released after my datacontext is disposed. This causes the query in Service1 to timeout. Once Service2 completes processing, Service1 resumes processing again without problem. So for example Service1 code may look like: int x =0; using (DataContext db = new DataContext()) { x = (from dp in db.Table1 select dp).Count(); // this line will timeout while service2 is processing Table1 t = new Table1(); t.Data = "test"; db.Table1.InsertOnSubmit(t); db.SubmitChanges(); } if(x % 50 == 0) CallService2(); The code in service2 may look like: using (DataContext db = new DataContext()) { Table1 t = db.Table1.Where(t => t.id == myId); t.Data = "updated"; db.SubmitChanges(); } // I would have expected the lock to have been released at this point, but this is not the case. DoSomeLongRunningTasks(); // lock will be released once service2 exits I don't understand why the lock is not released when the datacontext is disposed in Service2. To get around the problem I have been calling: db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"); and this works, but I am not happy using it. I want to solve this problem properly. Has any one experienced this sort of problem before and does any one know how to solve it? Why is the lock not released after the datacontext has been disposed? Thanks in advance. p.s. sorry for the extremely long post.

    Read the article

  • Oracle SQL: ROLLUP not summing correctly

    - by tommy-o-dell
    Hi guys, Rollup seems to be working correcly to count the number of units, but not the number of trains. Any idea what could be causing that? The output from the query looks like this. The sum of the Units column in yellow is 53 but the rollup is showing 51. The number of units adds up correctly though... And here's the oracle SQL query... select t.year, t.week, decode(t.mine_id,NULL,'PF',t.mine_id) as mine_id, decode(t.product,Null,'LF',t.product) as product, decode(t.mine_id||'-'||t.product,'-','PF',t.mine_id||'-'||t.product) as code, count(distinct t.tpps_train_id) as trains, count(1) as units from ( select trn.mine_code as mine_id, trn.train_tpps_id as tpps_train_id, round((con.calibrated_weight_total - con.empty_weight_total),2) as tonnes from widsys.train trn INNER JOIN widsys.consist con USING (train_record_id) where trn.direction = 'N' and (con.calibrated_weight_total-con.empty_weight_total) > 10 and trn.num_cars > 10 and con.consist_no not like '_L%' ) w, ( select to_char(td.datetime_act_comp_dump-7/24, 'IYYY') as year, to_char(td.datetime_act_comp_dump-7/24, 'IW') as week, td.mine_code as mine_id, td.train_id as tpps_train_id, pt.product_type_code as product from tpps.train_details td inner join tpps.ore_products op using (ore_product_key) inner join tpps.product_types pt using (product_type_key) where to_char(td.datetime_act_comp_dump-7/24, 'IYYY') = 2010 and to_char(td.datetime_act_comp_dump-7/24, 'IW') = 12 order by td.datetime_act_comp_dump asc ) t where w.mine_id = t.mine_id and w.tpps_train_id = t.tpps_train_id having t.product is not null or t.mine_id is null group by t.year, t.week, rollup( t.mine_id, t.product)

    Read the article

  • Distribute budget over for ranked components in SQL

    - by Lee
    Assume I have a budget of $10 (any integer) and I want to distribute it over records which have rank field with varying needs. Example: rank Req. Fulfilled? 1 $3 Y 2 $4 Y 3 $2 Y 4 $3 N Those ranks from 1 to 3 should be fulfilled because they are within budget. whereas, the one ranked 4 should not. I want an SQL query to solve that. Below is my initial script: CREATE TABLE budget ( id VARCHAR (32), budget INTEGER, PRIMARY KEY (id)); CREATE TABLE component ( id VARCHAR (32), rank INTEGER, req INTEGER, satisfied BOOLEAN, PRIMARY KEY (id)); INSERT INTO budget (id,budget) VALUES ('1',10); INSERT INTO component (id,rank,req) VALUES ('1',1,3); INSERT INTO component (id,rank,req) VALUES ('2',2,4); INSERT INTO component (id,rank,req) VALUES ('3',3,2); INSERT INTO component (id,rank,req) VALUES ('4',4,3); Thanks in advance for your help. Lee

    Read the article

  • Two radically different queries against 4 mil records execute in the same time - one uses brute force.

    - by IanC
    I'm using SQL Server 2008. I have a table with over 3 million records, which is related to another table with a million records. I have spent a few days experimenting with different ways of querying these tables. I have it down to two radically different queries, both of which take 6s to execute on my laptop. The first query uses a brute force method of evaluating possibly likely matches, and removes incorrect matches via aggregate summation calculations. The second gets all possibly likely matches, then removes incorrect matches via an EXCEPT query that uses two dedicated indexes to find the low and high mismatches. Logically, one would expect the brute force to be slow and the indexes one to be fast. Not so. And I have experimented heavily with indexes until I got the best speed. Further, the brute force query doesn't require as many indexes, which means that technically it would yield better overall system performance. Below are the two execution plans. If you can't see them, please let me know and I'll re-post then in landscape orientation / mail them to you. Brute-force query: Index-based exception query: My question is, based on the execution plans, which one look more efficient? I realize that thing may change as my data grows.

    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 | 376 377 378 379 380 381 382 383 384 385 386 387  | Next Page >