Search Results

Search found 28930 results on 1158 pages for 'sql ce'.

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 614/1158 | < Previous Page | 610 611 612 613 614 615 616 617 618 619 620 621  | Next Page >

  • How to optimise MySQL query containing a subquery?

    - by aidan
    I have two tables, House and Person. For any row in House, there can be 0, 1 or many corresponding rows in Person. But, of those people, a maximum of one will have a status of "ACTIVE", the others will all have a status of "CANCELLED". e.g. SELECT * FROM House LEFT JOIN Person ON House.ID = Person.HouseID House.ID | Person.ID | Person.Status 1 | 1 | CANCELLED 1 | 2 | CANCELLED 1 | 3 | ACTIVE 2 | 1 | ACTIVE 3 | NULL | NULL 4 | 4 | CANCELLED I want to filter out the cancelled rows, and get something like this: House.ID | Person.ID | Person.Status 1 | 3 | ACTIVE 2 | 1 | ACTIVE 3 | NULL | NULL 4 | NULL | NULL I've achieved this with the following sub select: SELECT * FROM House LEFT JOIN ( SELECT * FROM Person WHERE Person.Status != "CANCELLED" ) Person ON House.ID = Person.HouseID ...which works, but breaks all the indexes. Is there a better solution that doesn't? I'm using MySQL and all relevant columns are indexed. EXPLAIN lists nothing in possible_keys. Thanks.

    Read the article

  • Compare and find differences in two tables in Oracle

    - by Ruslan
    Hi! i have 2 tables: account: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE flex: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE I want to show differences comparing only by: AE_CCY, DRCR_IND, AMOUNT, MODULE and ACC by first 4 characters Example: ID ACC AE_CCY DRCR_IND AMOUNT MODULE -- --------- ------ -------- ------ ------ 1 734647674 USD D 100 OP and in flex: ID ACC AE_CCY DRCR_IND AMOUNT MODULE -- --------- ------ -------- ------ ------ 1 734647654 USD D 100 OP 2 734665474 USD D 100 OP 9 734611111 USD D 100 OP ID's 2 and 9 should be shown as differences. If I use FULL JOIN I'll get no differences as substr(account.ACC,1,4) = substr(flex.ACC,1,4) are equal and others are equal and MINUS doesn't work because ID's different. Thanks.

    Read the article

  • Complex SQL query, one to many relationship

    - by Ethan
    Hey SO, I have a query such that I need to get A specific dog All comments relating to that dog The user who posted each comment All links to images of the dog the user who posted each link I've tried a several things, and can't figure out quite how to work it. Here's what I have (condensed so you don't have to wade through it all): SELECT s.dog_id, s.name, c.comment, c.date_added AS comment_date_added, u.username AS comment_username, u.user_id AS comment_user_id, l.link AS link, l.date_added AS link_date_added, u2.username AS link_username, u2.user_id AS link_user_id FROM dogs AS d LEFT JOIN comments AS c ON c.dog_id = d.dog_id LEFT JOIN users AS u ON c.user_id = u.user_id LEFT JOIN links AS l ON l.dog_id = d.dog_id LEFT JOIN users AS u2 ON l.user_id = u2.user_id WHERE d.dog_id = '1' It's sorta close to working, but it'll only return me the first comment, and the first link all as one big array with all the info i requested. The are multiple comments and links per dog, so I need it to give me all the comments and all the links. Ideally it'd return an object with dog_id, name, comments(an array of the comments), links(an array of the links) and then comments would have a bunch of comments, date_added, username, and user_id and links would have a bunch of links with link, date_added, username and user_id. It's got to work even if there are no links or comments. I learned the basics of mySQL somewhat recently, but this is pretty far over my head. Any help would be wonderful. Thanks!

    Read the article

  • How to report DataContext.SubmitChanges() progress with LINQ2SQL

    - by kzen
    If there is a foreach loop that contains DataContext.Customer.InsertOnSubmit(cust) for example: foreach (Object obj in collection) { Customer cust = new Customer { Id = obj.Id, Name = obj.Name ... }; DataContext.Customer.InsertOnSubmit(cust); } And outside of the loop there is a call to: DataContext.SubmittChanges(); Is there a way to obtain the SubmittChanges progress in order to report the progress back to the user (or a different approach without moving the SubmittChanges into the loop)?

    Read the article

  • Get list of duplicate rows in MySql

    - by user347033
    Hi, i have a table like this ID nachname vorname 1 john doe 2 john doe 3 jim doe 4 Michael Knight I need a query that will return all the fields (select *) from the records that have the same nachname and vorname (in this case, records 1 and 2). Can anyone help me with this? Thanks

    Read the article

  • Error: A SQLParamenter wtih ParameterName @myparm is not contained by this SQLParameter Collection

    - by SidC
    Good Morning, I'm working on an ASP.NET 3.5 webforms application and have written the following code: Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Diel_inventoryConnectionString").ConnectionString Dim con As New SqlConnection(connectionString) Dim adapter1 As New SqlDataAdapter adapter1.SelectCommand = New SqlCommand adapter1.SelectCommand.CommandType = CommandType.StoredProcedure adapter1.SelectCommand.CommandText = "PartSproc" Dim parmNSN As New SqlParameter("@NSN", SqlDbType.NVarChar) Dim parmName As New SqlParameter("@PartName", SqlDbType.NVarChar) txtNSN.Text = adapter1.SelectCommand.Parameters("@NSN").Value txtSearch.Text = adapter1.SelectCommand.Parameters("@PartName").Value Dim dt As New DataTable() adapter1.Fill(dt) MySearch.DataSource = dt MySearch.DataBind() End Sub When I run the page, I receive the error A SQLParameter with @NSN is not contained by this SQLParameter Collection. I tried using apostrophes around the @NSN and @PartName but that does not work either and presents expression expected error. How might I rectify the above code so that it references the @NSN and @PartName parameters correctly? Thanks, Sid

    Read the article

  • Create link on page w/ web address stored in database

    - by RememberME
    This seems like it should be easy, but I can't seem to figure it out. All of my google searches lead me to linking to databases which isn't what I want to do. I'm a complete web development newb. I've roughly followed the NerdDinner tutorial in creating my web app. One of my stored fields is a web address. On the Index and Details pages, when I display the info from my record, I want the web address to be a clickable link to the website. It's currently displayed as: <%= Html.Encode(Model.Subcontract.company1.website) %>

    Read the article

  • Date format error in vb.net ?

    - by ahmed
    I get this error when I run the application Incorrect syntax near 12, on debugging I found that this error is caused due to the # along with the date. Dim backdate as datetime backdate = DateTime.Now.AddDays(-1) on binding the data to the grid to filter the backdate records this error is caused Incorrect syntax near 12. myqry = " select SRNO,SUBJECT,ID where datesend =" backdate Now i am trying to extract only the date or shall I divide the date into day , month and year with DATEPART and take into a variable or convert the date or what should i do , Please help ???

    Read the article

  • Problems getting foreign keys working in MySQL

    - by thehuby
    I've been trying to get a delete to cascade and it just doesn't seem to work. I'm sure I am missing something obvious, can anyone help me find it? I would expect a delete on the 'articles' table to trigger a delete on the corresponding rows in the 'article_section_lt' table. CREATE TABLE articles ( id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, url_stub VARCHAR(255) NOT NULL UNIQUE, h1 VARCHAR(60) NOT NULL UNIQUE, title VARCHAR(60) NOT NULL, description VARCHAR(150) NOT NULL, summary VARCHAR(150) NOT NULL DEFAULT "", html_content TEXT, date DATE NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )ENGINE=INNODB; CREATE TABLE article_sections ( /* blog, news etc */ id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, url_stub VARCHAR(255) NOT NULL UNIQUE, h1 VARCHAR(60) NOT NULL, title VARCHAR(60) NOT NULL, description VARCHAR(150) NOT NULL, summary VARCHAR(150) NOT NULL DEFAULT "", html_content TEXT NOT NULL DEFAULT "" )ENGINE=INNODB; CREATE TABLE article_section_lt ( fk_article_id INTEGER UNSIGNED NOT NULL REFERENCES articles(id) ON DELETE CASCADE, fk_article_section_id INTEGER UNSIGNED NOT NULL )ENGINE=INNODB;

    Read the article

  • SYSDATE - 1 error on pl/sql function

    - by ayo
    Hi curtisk/all, I have an issue: when i issue this function below ti gives me the following error: select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS=>DBMS_LOGMNR.NEW);' from v\$archived_log where name is not null; select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS=>DBMS_LOGMNR.ADDFILE);' from v\$archived_log where name is not null; EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => SYSDATE - 1, ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL); Error: * ERROR at line 1: ORA-01291: missing logfile ORA-06512: at "SYS.DBMS_LOGMNR", line 58 ORA-06512: at line 1 But i have added all the archived logs for several days before and my sysdate is at today. Kindly help out on this issue. thanks. Reagrds Ayo

    Read the article

  • How to switch from MSSQL to MySQL for using with Excel pivot

    - by Sim
    Hi all, I have a table that contains sales transaction (~20 mil rows). Previously I used MSSQL and export it to an Excel pivot. Data refresh took 10-15 minutes but still do-able. However, after I migrated to MySQL (using XamppLite & ODBC), it took forever to refresh the data in the Excel pivot. Maybe I didn't optimize the MySQL good enough ? Anyone can share some thoughts 'bout this ? Thanks

    Read the article

  • Select Statements in Jobs

    - by Andrew Vogel
    I have inherited a few jobs and I am trying to understand why select statements would be in their steps. I would think that select statements would be pointless in an automated job that displays nothing for an end user.

    Read the article

  • Receiving a NullPointerException when calling a cursor in Android

    - by LordSnoutimus
    I am creating an application which tracks the users location using GPS, stores the longitude and latitude in a database using a content provider then output the first long and lat to a mapview. I am able to create the cursor using this line of code: Cursor c = getContentResolver().query(GPSContentProvider.CONTENT_URI, null, null, null, null); startManagingCursor(c); However, when I make a call to move to the first row in the database or even try to close the cursor using c.close(); I receive a NullPointerException.

    Read the article

  • How to reuse results with a schema for end of day stock-data

    - by Vishalrix
    I am creating a database schema to be used for technical analysis like top-volume gainers, top-price gainers etc.I have checked answers to questions here, like the design question. Having taken the hint from boe100 's answer there I have a schema modeled pretty much on it, thusly: Symbol - char 6 //primary Date - date //primary Open - decimal 18, 4 High - decimal 18, 4 Low - decimal 18, 4 Close - decimal 18, 4 Volume - int Right now this table containing End Of Day( EOD) data will be about 3 million rows for 3 years. Later when I get/need more data it could be 20 million rows. The front end will be asking requests like "give me the top price gainers on date X over Y days". That request is one of the simpler ones, and as such is not too costly, time wise, I assume. But a request like " give me top volume gainers for the last 10 days, with the previous 100 days acting as baseline", could prove 10-100 times costlier. The result of such a request would be a float which signifies how many times the volume as grown etc. One option I have is adding a column for each such result. And if the user asks for volume gain in 10 days over 20 days, that would require another table. The total such tables could easily cross 100, specially if I start using other results as tables, like MACD-10, MACD-100. each of which will require its own column. Is this a feasible solution? Another option being that I keep the result in cached html files and present them to the user. I dont have much experience in web-development, so to me it looks messy; but I could be wrong ( ofc!) . Is that a option too? Let me add that I am/will be using mod_perl to present the response to the user. With much of the work on mysql database being done using perl. I would like to have a response time of 1-2 seconds.

    Read the article

  • Speeding up inner-joins and subqueries while restricting row size and table membership

    - by hiffy
    I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it will be added to the metadata table (so that a user isn't presented with material they have already read), and deleted from the stream table. Every three minutes, a background process will repopulate the Stream table with new entries (i.e. whenever the daemon adds new entries after the checks the rss feeds for updates). Problem: The query I came up with is hella slow. More importantly, the Stream table only needs to hold one hundred unread entries at a time; it'll reduce duplication, make processing faster and give me some flexibility with how I display the entries. The query (takes about 9 seconds on 3600 items with no indexes): insert into stream(entry_id, user_id) select entries.id, subscriptions_users.user_id from entries inner join subscriptions_users on subscriptions_users.subscription_id = entries.subscription_id where subscriptions_users.user_id = 1 and entries.id not in (select entry_id from metadata where metadata.user_id = 1) and entries.id not in (select entry_id from stream where user_id = 1); The query explained: insert into stream all of the entries from a user's subscription list (subscriptions_users) that the user has not read (i.e. do not exist in metadata) and which do not already exist in the stream. Attempted solution: adding limit 100 to the end speeds up the query considerably, but upon repeated executions will keep on adding a different set of 100 entries that do not already exist in the table (with each successful query taking longer and longer). This is close but not quite what I wanted to do. Does anyone have any advice (nosql?) or know a more efficient way of composing the query?

    Read the article

  • Oracle Database Enforce CHECK on multiple tables

    - by GigaPr
    I am trying to enforce a CHECK Constraint in a ORACLE Database on multiple tables CREATE TABLE RollingStocks ( Id NUMBER, Name Varchar2(80) NOT NULL, RollingStockCategoryId NUMBER NOT NULL, CONSTRAINT Pk_RollingStocks Primary Key (Id), CONSTRAINT Check_RollingStocks_CategoryId CHECK ((RollingStockCategoryId IN (SELECT Id FROM FreightWagonTypes)) OR (RollingStockCategoryId IN (SELECT Id FROM LocomotiveClasses))) ); ...but i get the following error: *Cause: Subquery is not allowed here in the statement. *Action: Remove the subquery from the statement. Can you help me understanding what is the problem or how to achieve the same result?

    Read the article

  • MYSQL Convert rows to columns performance problem

    - by Tarski
    I am doing a query that converts rows to columns similar to this post but have encountered a performance problem. Here is the query:- SELECT Info.Customer, Answers.Answer, Answers.AnswerDescription, Details.Code1, Details.Code2, Details.Code3 FROM Info LEFT OUTER JOIN Answers ON Info.AnswerID = Answers.AnswerID LEFT OUTER JOIN (SELECT ReferenceNo, MAX(CASE DetailsIndicator WHEN 'cde1' THEN DetailsCode ELSE NULL END ) Code1, MAX(CASE DetailsIndicator WHEN 'cde2' THEN DetailsCode ELSE NULL END ) Code2, MAX(CASE DetailsIndicator WHEN 'cde3' THEN DetailsCode ELSE NULL END ) Code3 FROM DetailsData GROUP BY ReferenceNo) Details ON Info.ReferenceNo = Details.ReferenceNo There are less than 300 rows returned, but the Details table is about 180 thousand rows. The query takes 45 seconds to run and needs to take only a few seconds. When I type show processlist; into MYSQL it is hanging on "Sending Data". Any thoughts as to what the performance problem might be?

    Read the article

  • Best Way to Generate Unique and consecutives numbers in Oracle

    - by RRUZ
    I need to generate unique and consecutive numbers (for use on an invoice), in a fast and reliable way. currently use a Oracle sequence, but in some cases generated numbers are not consecutive because of exceptions that may occur. I thought a couple of solutions to manage this problem, but neither of they convincing me. What solution do you recommend? Use a select max () SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices Use a table to store the last number generated for the invoice. UPDATE docs_numbers SET last_invoice = last_invoice + 1 Another Solution?

    Read the article

  • Subset sum problem

    - by MadBoy
    I'm having a problem with counting which is continuation of this question. I am not really a math person so it's really hard for me to figure out this subset sum problem which was suggested as resolution. I'm having 4 ArrayList in which I hold data: alId, alTransaction, alNumber, alPrice Type | Transaction | Number | Price 8 | Buy | 95.00000000 | 305.00000000 8 | Buy | 126.00000000 | 305.00000000 8 | Buy | 93.00000000 | 306.00000000 8 | Transfer out | 221.00000000 | 305.00000000 8 | Transfer in | 221.00000000 | 305.00000000 8 | Sell | 93.00000000 | 360.00000000 8 | Sell | 95.00000000 | 360.00000000 8 | Sell | 126.00000000 | 360.00000000 8 | Buy | 276.00000000 | 380.00000000 In the end I'm trying to get what's left for customer and what's left I put into 3 array lists: alNew (corresponds to alNumber), alNewPoIle (corresponds to alPrice), and alNewCo (corrseponds to alID) ArrayList alNew = new ArrayList(); ArrayList alNewPoIle = new ArrayList(); ArrayList alNewCo = new ArrayList(); for (int i = 0; i < alTransaction.Count; i++) { string tempAkcjeCzynnosc = (string) alTransaction[i]; string tempAkcjeInId = (string) alID[i]; decimal varAkcjeCena = (decimal) alPrice[i]; decimal varAkcjeIlosc = (decimal) alNumber[i]; int index; switch (tempAkcjeCzynnosc) { case "Transfer out": case "Sell": index = alNew.IndexOf(varAkcjeIlosc); if (index != -1) { alNew.RemoveAt(index); alNewPoIle.RemoveAt(index); alNewCo.RemoveAt(index); } else { ArrayList alTemp = new ArrayList(); decimal varAkcjeSuma = 0; for (int j = 0; j < alNew.Count; j ++) { string akcjeInId = (string) alNewCo[j]; decimal akcjeCena = (decimal) alNewPoIle[j]; decimal akcjeIlosc = (decimal) alNew[j]; if (tempAkcjeInId == akcjeInId && akcjeCena == varAkcjeCena) { alTemp.Add(j); varAkcjeSuma = varAkcjeSuma + akcjeIlosc; } } if (varAkcjeSuma == varAkcjeIlosc) { for (int j = alTemp.Count -1 ; j >=0 ; j --) { int tempIndex = (int) alTemp[j]; alNew.RemoveAt(tempIndex); alNewPoIle.RemoveAt(tempIndex); alNewCo.RemoveAt(tempIndex); } } } break; case "Transfer In": case "Buy": alNew.Add(varAkcjeIlosc); alNewPoIle.Add(varAkcjeCena); alNewCo.Add(tempAkcjeInId); break; } } Basically I'm adding and removing things from Array depending on Transaction Type, Transaction ID and Numbers. I'm adding numbers to ArrayList like 156, 340 (when it is TransferIn or Buy) etc and then i remove them doing it like 156, 340 (when it's TransferOut, Sell). My solution works for that without a problem. The problem I have is that for some old data employees were entering sum's like 1500 instead of 500+400+100+500. How would I change it so that when there's Sell/TransferOut or Buy/Transfer In and there's no match inside ArrayList it should try to add multiple items from thatArrayList and find elements that combine into aggregate. Inside my code I tried to resolve that problem with simple summing everything when there's no match (index == 1) if (index != -1) { alNew.RemoveAt(index); alNewPoIle.RemoveAt(index); alNewCo.RemoveAt(index); } else { ArrayList alTemp = new ArrayList(); decimal varAkcjeSuma = 0; for (int j = 0; j < alNew.Count; j ++) { string akcjeInId = (string) alNewCo[j]; decimal akcjeCena = (decimal) alNewPoIle[j]; decimal akcjeIlosc = (decimal) alNew[j]; if (tempAkcjeInId == akcjeInId && akcjeCena == varAkcjeCena) { alTemp.Add(j); varAkcjeSuma = varAkcjeSuma + akcjeIlosc; } } if (varAkcjeSuma == varAkcjeIlosc) { for (int j = alTemp.Count -1 ; j >=0 ; j --) { int tempIndex = (int) alTemp[j]; alNew.RemoveAt(tempIndex); alNewPoIle.RemoveAt(tempIndex); alNewCo.RemoveAt(tempIndex); } } But it only works if certain conditions are met, and fails for the rest.

    Read the article

  • rails: date type and GetDate

    - by cbrulak
    This is a follow up to this question: http://stackoverflow.com/questions/2930256/unique-responses-rails-gem I'm going to create an index based on the user id, url and a date type. I want date type (not datetime type) because I want the day, the 24 hour day to be part of the index to avoid duplication of page views counts on the same day. In other words: A view only counts once in a day by a visitor. I also want the default value of that column (viewdate) to be the function GETDATE(). This is what I have in my migration: execute "ALTER TABLEpage_viewsADD COLUMN viewdate datetime DEFAULTGETDATE()`" But the value viewdate is always empty. What am I missing? (as an aside, any other suggestions for accomplishing this goal?)

    Read the article

  • Why am I getting "Enter Parameter Value" when running my MS Access query?

    - by DanM
    In my query, I use the IIF function to assign either "Before" or "After" to a field named BeforeOrAfter using AS. When I run this query, however, the "Enter Parameter Value" dialog appears, requesting a value for BeforeOrAfter. If I remove BeforeOrAfter DESC from the ORDER BY clause, I don't get the dialog. Here is the offending query: SELECT d.Scenario, e.Event, IIF(d.LogTime < e.Time, 'Before','After') AS BeforeOrAfter, d.HeartRate FROM Data d INNER JOIN Events e ON d.Scenario = e.Scenario WHERE e.Include = Yes ORDER BY d.Scenario, e.Id, BeforeOrAfter DESC Question: Why is my AS BeforeOrAfter not being recognized by the ORDER BY clause? Why does it ask me to enter a parameter value for "BeforeOrAfter" when I run this query? Note: I tried using brackets, single quotes, double quotes, etc., but none of that made any difference.

    Read the article

  • Advice Needed To Normalise Database

    - by c11ada
    hey all, im trying to create a database for a feedback application in ASP.net i have the following database design. Username (PK) QuestionNo (PK) QuestionText FeedbackNo (PK) Username UserFeedbackNo (PK) FeedbackNo (FK) QuestionNo (FK) Answer Comment a user has a unique username a user can have multiple feedbacks i was wondering if the database design i have here is normalised and suitable for the application

    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 | 610 611 612 613 614 615 616 617 618 619 620 621  | Next Page >