Search Results

Search found 36773 results on 1471 pages for 'sql statement syntax'.

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 693/1471 | < Previous Page | 689 690 691 692 693 694 695 696 697 698 699 700  | Next Page >

  • Complex LINQ paging algorithm

    - by sharepointmonkey
    We have a list of projects that may or may not have a collection of subprojects. Our report needs to contain all the projects except those that are the parent project of a subproject. I need to page this into pages of, say, 25 rows. But if subprojects appear on that page then ALL the subprojects of that project must appear on the same page. So more than 25 items may appear if necessary. I've got as far as var pagedProjects = db.Projects.Where(x => !x.SubProjects.Any()).Skip( (pageNo -1) * pageSize).Take(pageSize); Obviously, this fails the second part of the requirements. As a further pain in the arse, I need to have a pager control on the report. So I'll need to be able to calculate the total number of pages. I could loop through the whole table of projects but the performance will suffer. Can anybody come up with a paged solution? EDIT - I should probably mention that SubProjects joins back onto Projects via a selfreferencing foreign key so the whole lot comes back as an IQueryable<Project>.

    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

  • joins- how to pass the parameter in stored procedure to execute a join query

    - by Ranjana
    i have used joins to get datas from two tables under comman name. as SELECT userValidity.UserName, userValidity.ValidFrom,userValidity.ValidUpTo,userValidity.TotalPoints, persons.SenderID FROM userValidity INNER JOIN persons ON userValidity.Username=tbl_persons.Username but i need to execute this query with oly the username which i pass as parameter in stored procedure.. how to pass the username in stored procedure in this joins. alter procedure GetNameIDUserInformation ( @user varchar(max) ) as begin SELECT userValidity.UserName, userValidity.ValidFrom,userValidity.ValidUpTo,userValidity.TotalPoints, persons.SenderID FROM userValidity INNER JOIN persons ON userValidity.Username=tbl_persons.Username end in this SP, where i have to pass the user parameter to get the single row of my user record

    Read the article

  • union on the same table

    - by StupidDeveloper
    I have a table: ID | Id1 | Id2 1 | 100 | 12 2 | 196 | 140 3 | 196 | 141 4 | 150 | 140 5 | 150 | 199 I want to write a query that will give me a table containing records with the same ID2 and with id1 equal to 196 or 150. I thought about union: select * from table where itemId = 196 union select * from table where itemId = 150 but that doesn't cover the ID2 requirement. How should I do that?

    Read the article

  • username and password check linq query in c#

    - by b0x0rz
    this linq query var users = from u in context.Users where u.UserEMailAdresses.Any(e1 => e1.EMailAddress == userEMail) && u.UserPasswords.Any(e2 => e2.PasswordSaltedHash == passwordSaltedHash) select u; return users.Count(); returns: 1 even when there is nothing in password table. how come? what i am trying to do is get the values of email and passwordHash from two separate tables (UserEMailAddresses and UserPasswords) linked via foreign keys to the third table (Users). it should be simple - checking if email and password mach from form to database. but it is not working for me. i get 1 (for count) even when there are NO entries in the UserPasswords table. is the linq query above completely wrong, or...?

    Read the article

  • Jasper error: Caused by SQLServerException: Transaction (Process ID 58) was deadlocked on thread | c

    - by Saky
    I got the above error in my jasper report mail. The query that is used in the report is quite complicated (for me). Reading different posts I conclude that to solve this the I have to change the query to SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION ... my query ... COMMIT TRANSACTION ? I wonder if this is the correct way to solve the error and that if it has any side effects? Has it happened to anyone in the Jasper reports? Does anyone know if there is a better solution exist to the problem? (Although that I have not yet tested the above solution, if anyone can give any insight on this will be helpful.)

    Read the article

  • Database table copying

    - by vbNewbie
    I am trying to rectify a previous database creation with tables that contains data that needs to be saved. Instead of recreating a completely new database since some of the tables are still reusable, I need to split a table that exists into 2 new tables which I have done. Now I am trying to insert the data into the 2 new tables and because of duplicate data in the old table I am having a hard time doing this. Old table structure: ClientProjects clientId PK clientName clientProj hashkey MD5 (clientname and clientProj) new table structures: client clientId PK clientName projects queryId PK clientId PK projectName I hope this makes sense. The problem is that in the old table for example you have clients with multiple clientIds.

    Read the article

  • Need some serious help with self join issue.

    - by kralco626
    Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!! The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html The view I want to apply this work around to is: create VIEW vw_lookup_test WITH SCHEMABINDING AS select count_big(*) as [count_all], awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm [owner], t.circt_cstdn_nm [tech], dvc.circt_nm, data_orgtn_yr from ((dbo.dvc join dbo.circt on dvc.circt_nm = circt.circt_nm) join dbo.circt_cstdn o on circt.circt_cstdn_user_id = o.circt_cstdn_user_id) join dbo.circt_cstdn t on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id group by awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm, t.circt_cstdn_nm, dvc.circt_nm, data_orgtn_yr go Any help would be greatly apreciated!!! Thanks so much in advance!

    Read the article

  • NULL-keys for key/value table

    - by user72185
    (Using Oracle) I have a table with key/value pairs like this: create table MESSAGE_INDEX ( KEY VARCHAR2(256) not null, VALUE VARCHAR2(4000) not null, MESSAGE_ID NUMBER not null ) I now want to find all the messages where key = 'someKey' and value is 'val1', 'val2' or 'val3' - OR value is null in which case there will be no entry in the table at all. This is to save space; there would be a large number of keys with null values if I stored them all. I think this works: SELECT message_id FROM message_index idx WHERE ((key = 'someKey' AND value IN ('val1', 'val2', 'val3')) OR NOT EXISTS (SELECT 1 FROM message_index WHERE key = 'someKey' AND idx.message_id = message_id)) But is is extremely slow. Takes 8 seconds with 700K records in message_index and there will be many more records and more search criteria when moving outside of my test environment. Primary key is key, value, message_id: add constraint PK_KEY_VALUE primary key (KEY, VALUE, MESSAGE_ID) And I added another index for message_id, to speed up searching for missing keys: create index IDX_MESSAGE_ID on MESSAGE_INDEX (MESSAGE_ID) I will be doing several of these key/value lookups in every search, not just one as shown above. So far I am doing them nested, where output id's of one level is the input to the next. E.g.: SELECT message_id from message_index WHERE (key/value compare) AND message_id IN ( SELECT ... and so on ) What can I do to speed this up?

    Read the article

  • How do these user/userParam references relate to the Customer and Account lookups?

    - by plath
    In the following code example how do the user/userParam references relate to the Customer and Account lookups and what is the relationship between Customer and Account? // PersistenceManager pm = ...; Transaction tx = pm.currentTransaction(); User user = userService.currentUser(); List<Account> accounts = new ArrayList<Account>(); try { tx.begin(); Query query = pm.newQuery("select from Customer " + "where user == userParam " + "parameters User userParam"); List<Customer> customers = (List<Customer>) query.execute(user); query = pm.newQuery("select from Account " + "where parent-pk == keyParam " + "parameters Key keyParam"); for (Customer customer : customers) { accounts.addAll((List<Account>) query.execute(customer.key)); } } finally { if (tx.isActive()) { tx.rollback(); } }

    Read the article

  • 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

  • What is the most efficient/elegant way to parse a flat table into a tree?

    - by Tomalak
    Assume you have a flat table that stores an ordered tree hierarchy: Id Name ParentId Order 1 'Node 1' 0 10 2 'Node 1.1' 1 10 3 'Node 2' 0 20 4 'Node 1.1.1' 2 10 5 'Node 2.1' 3 10 6 'Node 1.2' 1 20 What minimalistic approach would you use to output that to HTML (or text, for that matter) as a correctly ordered, correctly intended tree? Assume further you only have basic data structures (arrays and hashmaps), no fancy objects with parent/children references, no ORM, no framework, just your two hands. The table is represented as a result set, which can be accessed randomly. Pseudo code or plain English is okay, this is purely a conceptional question. Bonus question: Is there a fundamentally better way to store a tree structure like this in a RDBMS? EDITS AND ADDITIONS To answer one commenter's (Mark Bessey's) question: A root node is not necessary, because it is never going to be displayed anyway. ParentId = 0 is the convention to express "these are top level". The Order column defines how nodes with the same parent are going to be sorted. The "result set" I spoke of can be pictured as an array of hashmaps (to stay in that terminology). For my example was meant to be already there. Some answers go the extra mile and construct it first, but thats okay. The tree can be arbitrarily deep. Each node can have N children. I did not exactly have a "millions of entries" tree in mind, though. Don't mistake my choice of node naming ('Node 1.1.1') for something to rely on. The nodes could equally well be called 'Frank' or 'Bob', no naming structure is implied, this was merely to make it readable. I have posted my own solution so you guys can pull it to pieces.

    Read the article

  • Return order of MySQL SHOW COLUMNS

    - by rich
    Hey guys. Simple one this, but one I can't seem to find any information on so here goes. I need to find the columns in a specific table, which is no problem.... SHOW COLUMNS FROM tablename LIKE '%ColumnPrefix%'; But I need to know what order they will be returned, preferable by choosing to order the results ascending alphabetically. I have had no luck with using ORDER BY Field. Any ideas? Cheers!

    Read the article

  • GROUP BY as a way to pick the first row from a group of similar rows, is this correct, is there any

    - by FipS
    I have a table which stores test results like this: user | score | time -----+-------+------ aaa | 90% | 10:30 bbb | 50% | 9:15 *** aaa | 85% | 10:15 aaa | 90% | 11:00 *** ... What I need is to get the top 10 users: user | score | time -----+-------+------ aaa | 90% | 11:00 bbb | 50% | 9:15 ... I've come up with the following SELECT: SELECT * FROM (SELECT user, score, time FROM tests_score ORDER BY user, score DESC, time DESC) t1 GROUP BY user ORDER BY score DESC, time LIMIT 10 It works fine but I'm not quite sure if my use of ORDER BY is the right way to pick the first row of each group of sorted records. Is there any better practice to achieve the same result? (I use MySQL 5)

    Read the article

  • Swap unique indexed column values in database.

    - by Ramesh Soni
    I have a database table and one of the fields (not primary key) is having unique index on it. Now I want to swap values under this column for two rows. How could this be done? Two hack I know are: Delete both rows and re-insert them Update rows with some other value and swap and then update to actual value. But I don't want to go for these as they do not seem to be the appropriate solution to the problem. Could anyone help me out?

    Read the article

  • oracle plsql select pivot without dynamic sql to group by

    - by kayhan yüksel
    To whom it may respond to, We would like to use SELECT function with PIVOT option at a 11g r2 Oracle DBMS. Our query is like : "select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line));" As can be seen, XML is commented out, if run as PIVOT XML it gives the correct output in XML format, but we are required to get the data as unformatted pivot data, but this sentence throws error : ORA-00936: missing expression Any resolutions or ideas would be welcomed, Best Regards -------------if we can get the result of this to sys_refcursor using execute immediate it will be solved ------------------------ the procedure : PROCEDURE pr_test2 (deneme OUT sys_refcursor) IS v_sql NVARCHAR2 (4000) := ''; TYPE v_items IS TABLE OF NVARCHAR2 (30); v_pivot_items NVARCHAR2 (4000) := ''; BEGIN FOR i IN (SELECT DISTINCT (item_no) AS items FROM t_order_line) LOOP v_pivot_items := ',''' || i.items || '''' || v_pivot_items; END LOOP; v_pivot_items := LTRIM (v_pivot_items, ','); v_sql := 'begin select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and OL.ITEM_NO in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN (' || v_pivot_items || '));end;'; open DENEME for select v_sql from dual; Kayhan YÜKSEL

    Read the article

  • LINQ for Java tool

    - by Milhous
    Would a LINQ for java be a useful tool? I have been working on a tool that will allow a Java object to map to a row in a database. Would this be useful for Java programmers? What features would be useful?

    Read the article

  • (My)SQL performance: updating one field vs many unneccesary fields

    - by changokun
    i'm processing a form that has a lot of fields for a user who is editing an existing record. the user may have only changed one field, and i would typically do an update query that sets the values of all the fields, even though most of them don't change. i could do some sort of tracking to see which fields have actually changed, and only update the few that did. is there a performance difference between updating all fields in a record vs only the one that changed? are there other reasons to go with either method? the shotgun method is pretty easy...

    Read the article

  • Optimum size of transaction in Postgres?

    - by Joe
    I'm running a process that does a lot of updates ( 100,000) to a table. I have the choice between putting all the updates in a single transaction or committing transactions every 1000 or so. Ignore for the moment the case where a transaction fails and is aborted. I'm interested in the best size of transaction for memory and speed efficiency.

    Read the article

  • JPA native query join returns object but dereference throws class cast exception

    - by masato-san
    I'm using JPQL Native query to join table and query result is stored in List<Object[]>. public String getJoinJpqlNativeQuery() { String final SQL_JOIN = "SELECT v1.bitbit, v1.numnum, v1.someTime, t1.username, t1.anotherNum FROM MasatosanTest t1 JOIN MasatoView v1 ON v1.username = t1.username;" System.out.println("get join jpql native query is being called ============================"); EntityManager em = null; List<Object[]> out = null; try { em = EmProvider.getDefaultManager(); Query query = em.createNativeQuery(SQL_JOIN); out = query.getResultList(); System.out.println("return object ==========>" + out); System.out.println(out.get(0)); String one = out.get(0).toString(); //LINE 77 where ClassCastException System.out.println(one); } catch(Exception e) { } finally { if(em != null) { em.close; } } } The problem is System.out.println("return object ==========>" + out); outputs: return object ==========> [[true, 0, 2010-12-21 15:32:53.0, masatosan, 0.020], [false, 0, 2010-12-21 15:32:53.0, koga, 0.213]] System.out.println(out.get(0)) outputs: [true, 0, 2010-12-21 15:32:53.0, masatosan, 0.020] So I assumed that I can assign return value of out.get(0) which should be String: String one = out.get(0).toString(); But I get weird ClassCastException. java.lang.ClassCastException: java.util.Vector cannot be cast to [Ljava.lang.Object; at local.test.jaxrs.MasatosanTestResource.getJoinJpqlNativeQuery (MasatosanTestResource.java:77) So what's really going on? Even Object[] foo = out.get(0); would throw an ClassCastException :(

    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

  • Why does this query only select a single row?

    - by Joe
    SELECT * FROM tbl_houses WHERE (SELECT HousesList FROM tbl_lists WHERE tbl_lists.ID = '123') LIKE CONCAT('% ', tbl_houses.ID, '#') It only selects the row from tbl_houses of the last occuring tbl_houses.ID inside tbl_lists.HousesList I need it to select all the rows where any ID from tbl_houses exists within tbl_lists.HousesList

    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 | 689 690 691 692 693 694 695 696 697 698 699 700  | Next Page >