Search Results

Search found 31931 results on 1278 pages for 'sql statement'.

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 654/1278 | < Previous Page | 650 651 652 653 654 655 656 657 658 659 660 661  | Next Page >

  • Autmatically create table on MySQL server based on date?

    - by Anthony
    Is there an equivalent to cron for MySQL? I have a PHP script that queries a table based on the month and year, like: SELECT * FROM data_2010_1 What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table. I was wondering if I can just set something up on the MySQL server itself that will create the table (based on a default table) at the stroke of midnight on the first of the month. Update Based on the comments I've gotten, I'm thinking this isn't the best way to achieve my goal. So here's two more questions: If I have a table with thousands of rows added monthly, is this potentially a drag on resources? If so, what is the best way to partition this table, since the above is verboten? What are the potential problems with my home-grown method I originally thought up?

    Read the article

  • Using 'in' in Join

    - by Ruslan
    i have two selects a & b and i join them like: select * from ( select n.id_b || ',' || s.id_b ids, n.name, s.surname from names n, surnames s where n.id_a = s.id_a ) a left join ( select sn.id, sn.second_name ) b on b.id in (a.ids) in this case join doesn't work :( The problem is in b.id in (a.ids). But why if it looks like 12 in (12,24) and no result :(

    Read the article

  • How to save multiple column in database

    - by shamim
    I have a text file.I need to get data from this text file and show on grid , 1)After this user can update information from gridview, 2)Click on save button save data on database. Before clicking on button data don't save on database.How to do that?

    Read the article

  • Display another field in the referenced table for multiple columns with performance issues in mind

    - by israkir
    I have a table of edge like this: ------------------------------- | id | arg1 | relation | arg2 | ------------------------------- | 1 | 1 | 3 | 4 | ------------------------------- | 2 | 2 | 6 | 5 | ------------------------------- where arg1, relation and arg2 reference to the ids of objects in another object table: -------------------- | id | object_name | -------------------- | 1 | book | -------------------- | 2 | pen | -------------------- | 3 | on | -------------------- | 4 | table | -------------------- | 5 | bag | -------------------- | 6 | in | -------------------- What I want to do is that, considering performance issues (a very big table more than 50 million of entries) display the object_name for each edge entry rather than id such as: --------------------------- | arg1 | relation | arg2 | --------------------------- | book | on | table | --------------------------- | pen | in | bag | --------------------------- What is the best select query to do this? Also, I am open to suggestions for optimizing the query - adding more index on the tables etc... EDIT: Based on the comments below: 1) @Craig Ringer: PostgreSQL version: 8.4.13 and only index is id for both tables. 2) @andrefsp: edge is almost x2 times bigger than object.

    Read the article

  • Removing "Using temporary; Using filesort" from this MySQL select+join+group by query

    - by claytontstanley
    I have the following query: select t.Chunk as LeftChunk, t.ChunkHash as LeftChunkHash, q.Chunk as RightChunk, q.ChunkHash as RightChunkHash, count(t.ChunkHash) as ChunkCount from chunksubset as t join chunksubset as q on t.ID = q.ID group by LeftChunkHash, RightChunkHash And the following explain table: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE subsets ref PRIMARY,IDIndex,SubsetIndex SubsetIndex 767 const 522014 "Using where; Using temporary; Using filesort" 1 SIMPLE subsets eq_ref PRIMARY,IDIndex,SubsetIndex PRIMARY 771 sotero.subsets.Id,const 1 "Using where; Using index" 1 SIMPLE c ref IDIndex IDIndex 4 sotero.subsets.Id 12 "Using where" 1 SIMPLE c ref IDIndex IDIndex 4 sotero.subsets.Id 12 note the "using temporary; using filesort". When this query is run, I quickly run out of RAM (presumably b/c of the temp table), and then the HDD kicks in, and the query slows to a halt. I thought it might be an index issue, so I started adding a few that sort of made sense: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment chunks 0 PRIMARY 1 ChunkId A 17796190 NULL NULL BTREE chunks 1 ChunkHashIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 IDIndex 1 Id A 1483015 NULL NULL BTREE chunks 1 ChunkIndex 1 Chunk A 243783 NULL NULL BTREE chunks 1 ChunkTypeIndex 1 ChunkType A 2 NULL NULL BTREE chunks 1 chunkHashByChunkIDIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByChunkIDIndex 2 ChunkId A 17796190 NULL NULL BTREE chunks 1 chunkHashByChunkTypeIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByChunkTypeIndex 2 ChunkType A 261708 NULL NULL BTREE chunks 1 chunkHashByIDIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByIDIndex 2 Id A 17796190 NULL NULL BTREE But still using the temporary table. The db engine is MyISAM. How can I get rid of the using temporary; using filesort in this query? Just changing to InnoDB w/o explaining the underlying cause is not a particularly satisfying answer. Besides, if the solution is to just add the proper index, then that's much easier than migrating to another db engine.

    Read the article

  • Copying Some from a PostgreSQL Server to Another

    - by whollychao
    I am in need of an application that can periodically transmit select rows from a PostgreSQL database across a network to a second PostgreSQL server. Typically these will be the most recent row added, pulled and transmitted every 10-30 seconds. The primary servers run in a MS Windows environment with a high-latency, and occasionally intermittent, network connection. Therefore, any application would have to be tolerant of this and ideally automatically reconnect / resend data that could not be transmitted. Due to the environment and the requirements, a full-blown replication package would be unnecessary. I appreciate any help anyone has with this problem.

    Read the article

  • NHibernate complex order query

    - by manu08
    Here's my simplified domain public class Notification { public Guid ID { get; set; } public string Name { get; set; } public IEnumerable<Location> Locations { get; set; } } public class Location { public Guid Id { get; set; } public string Name { get; set; } public decimal Longitude { get; set; } public decimal Latitude { get; set; } } Notifications and Locations have a many-to-many relationship (defined in table LocationsOnNotification). What I'd like to do is query for the nearest 10 Notifications from a given longitude and latitude. I've been trying to use ICriteria, but I'm not sure how to specify the ordering correctly: return Session.CreateCriteria<Notification>() .SetFirstResult(firstIndex) .SetMaxResults(maxResults) .AddOrder(Order.Asc( WHAT GOES HERE! )) .List<Notification>(); What I've been thinking of so far is adding a formula property to the Location mapping; something like this: <property name='Distance' formula='lots of geometry'/> But I'm not sure if that can take in parameters (since I'd need to pass in the user's location to calculate the distance), plus I'm not sure how to specify it in the Order.Asc clause given that it's a property on a many-to-many association class. Any ideas? Or perhaps I should take a different approach altogether. Thanks in advance!

    Read the article

  • Postback Removing Styling from Page

    - by Roy
    Hi, Currently I've created a ASP.Net page that has a dropdown control with autopostback set to true. I've also added color backgrounds for individual listitems. Whenever an item is selected in the dropdown control the styling is completely removed from all of the list items. How can I prevent this from happening? I need the postback to pull data based on the dropdown item that is selected. Here is my code. aspx file: <asp:DropDownList ID="EmpDropDown" AutoPostBack="True" OnSelectedIndexChanged="EmpDropDown_SelectedIndexChanged" runat="server"> </asp:DropDownList> <asp:TextBox ID="MessageTextBox" TextMode="MultiLine" Width="550" Height="100px" runat="server"></asp:TextBox> aspx.cs code behind: protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GetEmpList(); } } protected void EmpDropDown_SelectedIndexChanged(object sender, EventArgs e) { GetEmpDetails(); } private void GetEmpList() { SqlDataReader dr = ToolsLayer.GetEmpList(); int currentIndex = 0; while (dr.Read()) { EmpDropDown.Items.Add(new ListItem(dr["Title"].ToString(), dr["EmpKey"].ToString())); if (dr["Status"].ToString() == "disabled") { EmpDropDown.Items[currentIndex].Attributes.Add("style", "background-color:red;"); } currentIndex++; } dr.Close(); } private void GetEmpDetails() { SqlDataReader dr = ToolsLayer.GetEmpDetails(EmpDropDown.SelectedValue); while (dr.Read()) { MessageTextBox.Text = dr["Message"].ToString(); } dr.Close(); } Thank You

    Read the article

  • Heterogeneous queries require the ANSI_NULLS

    - by Dezigo
    I wrote a trigger. USE [TEST] GO /****** Object: Trigger [dbo].[TR_POSTGRESQL_UPDATE_YC] Script Date: 05/26/2010 08:54:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[TR_POSTGRESQL_UPDATE_YC] ON [dbo].[BCT_CNTR_EVENTS] FOR INSERT AS BEGIN DECLARE @MOVE_TIME varchar(14); DECLARE @MOVE_TIME_FORMATED varchar(20); DECLARE @RELEASE_NOTE varchar(32); DECLARE @CMR_NUMBER varchar(15); DECLARE @MOVE_TYPE varchar(2); SELECT @MOVE_TIME = inserted.move_time ,@MOVE_TYPE = inserted.move_type ,@RELEASE_NOTE = inserted.release_note ,@CMR_NUMBER = inserted.cmr_number FROM inserted IF(@MOVE_TYPE = 'YC') BEGIN SET @MOVE_TIME_FORMATED = SUBSTRING(@MOVE_TIME,1,4) + '-' + SUBSTRING(@MOVE_TIME,5,2) + '-' + SUBSTRING(@MOVE_TIME,7,2) + ' 00:00:00' --UPDATE OpenQuery(POSTGRESQL_SERV,'SELECT visit_cmr,visit_timestamp,visit_pin FROM VISIT') -- SET visit_cmr = @RELEASE_NOTE -- WHERE visit_timestamp = @MOVE_TIME_FORMATED -- AND visit_pin = right(@CMR_NUMBER,5) -- AND visit_cmr IS NULL END SET NOCOUNT ON; END When I have inserted a row,I have get an error **Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.** Then I ofcourse SET SET ANSI_WARNINGS is ON but it`s not work for me. (trigger fo linked server postgresql) I have restarted a server. not work again.

    Read the article

  • How to select all parent objects into DataContext using single LINQ query ?

    - by too
    I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT. At first I was trying to fill as much LINQ objects as possible using LoadOptions, but AFAIK this method allows only single table to be linked in one query using LoadWith. So I have invented a solution to forcibly set all parent objects of entity which of list is to be fetched, although there is a problem of multiple SELECTS going to database - a single query results in two SELECTS with the same parameters in the same LINQ context. For this question I have simplified this query to popular invoice example: public static class Extensions { public static IEnumerable<T> ForEach<T>(this IEnumerable<T> collection, Action<T> func) { foreach(var c in collection) { func(c); } return collection; } } public IEnumerable<Entry> GetResults(AppDataContext context, int CustomerId) { return ( from entry in context.Entries join invoice in context.Invoices on entry.EntryInvoiceId equals invoice.InvoiceId join period in context.Periods on invoice.InvoicePeriodId equals period.PeriodId // LEFT OUTER JOIN, store is not mandatory join store in context.Stores on entry.EntryStoreId equals store.StoreId into condStore from store in condStore.DefaultIfEmpty() where (invoice.InvoiceCustomerId = CustomerId) orderby entry.EntryPrice descending select new { Entry = entry, Invoice = invoice, Period = period, Store = store } ).ForEach(x => { x.Entry.Invoice = Invoice; x.Invoice.Period = Period; x.Entry.Store = Store; } ).Select(x => x.Entry); } When calling this function and traversing through result set, for example: var entries = GetResults(this.Context); int withoutStore = 0; foreach(var k in entries) { if(k.EntryStoreId == null) withoutStore++; } the resulting query to database looks like (single result is fetched): SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 The question is why there are two queries and how can I fetch LINQ objects without such hacks?

    Read the article

  • MySQL db Audit Trail Trigger

    - by Natkeeran
    I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the procedure): The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set. Please let me know what could be the issue, or an alternate way to implement this. DROP PROCEDURE IF EXISTS addLogTrigger; DELIMITER $ CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255)) BEGIN SELECT CONCAT( 'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', GROUP_CONCAT( CONCAT( 'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO AuditLog (', 'TableName, ', 'RowPK, ', 'FieldName, ', 'OldValue, ', 'NewValue' ') VALUES ( ''', table_name, ''', NEW.', pkField, ', ''', column_name, ''', OLD.', column_name, ', NEW.', column_name, '); END IF;' ) SEPARATOR ' ' ), ' END;$' ) FROM information_schema.columns WHERE table_schema = database() AND table_name = tableName; END$ DELIMITER ;

    Read the article

  • How to get top 3 frequencies in MySQL?

    - by Amenhotep
    Hello, In MySQL I have a table called "meanings" with three columns: "person" (int), "word" (byte, 16 possible values) "meaning" (byte, 26 possible values). A person assigns one or more meanings to each word: person word meaning ------------------- 1 1 4 1 2 19 1 2 7 <-- second meaning for word 2 1 3 5 ... 1 16 2 Then another person, and so on. There will be thousands of persons. I need to find for each of the 16 words the top three meanings (with their frequencies). Something like: word 1: meaning 5 (35% of people), meaning 19 (22% of people), meaning 2 (13% of people) word 2: meaning 8 (57%), meaning 1 (18%), meaning 22 (7%) ... Is it possible to solve this with a single MySQL query? (If this problem is a classic one and has been answered elsewhere, I would appreciate if you could give me a link to the solution.) Thank you very much, ve

    Read the article

  • WPF/.NET data access models - resource recommendations

    - by jasonk
    We're in the early design/prep phases of transferring/updating a rather large "legacy" 3 tier client-server app to a new version. We’re looking at doing WPF over Winforms as it appears to be the direction Microsoft is pushing development of the future and we’d like the maximize the life cycle/span of the apps. That said during the rewrite we’d like to make as many changes to our data access/presentation model to improve performance as much as possible up front as many. I’ve been doing some research along that vein but the vast majority of the resources I've found that discuss WPF focus only simple data tracking apps or focus on the very basics UI design/controls. The few items that even discuss data presentation are fairly elementary in depth. Are there any books/articles/recommended reading/other resources recommended for development related to large enterprise level business apps? Any “gotchas” that should/could be avoided? General advice to minimize the time underwater

    Read the article

  • Need help with this SQL

    - by Bragaadeesh
    Hi guys, I am very very new to mysql. I am trying to execute the following query. update share set holder = 22 where SHARE_ID IN (select SHARE_ID from SHARE WHERE holder=1 LIMIT 10) When I try to execute the above query I am getting this error #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

    Read the article

  • rookie MySql question about paging; Is one query enough?

    - by Camran
    I have managed to get paging to work, almost. I want to display to the user, total nr of records found, and the currently displayed records. Ex: 4000 found, displaying 0-100. I am testing this with the nr 2 (because I don't have that many records, have like 20). So I am using LIMIT $start, $nr_results; Do I have to make two queries in order to display the results the way I want, one query fetching all records and then make a mysql_num_rows to get all records, then the one with the LIMIT ? I have this: mysql_num_rows($qry_result); $total_pages = ceil($num_total / $res_per_page); //$res_per_page==2 and $num_total = 2 if ($p - 10 < 1) { $pagemin=1; } else { $pagemin = $p - 10; } if ($p + 10 $total_pages) { $pagemax = $total_pages; } else { $pagemax = $p + 10; } Here is the query: SELECT mt.*, fordon.*, boende.*, elektronik.*, business.*, hem_inredning.*, hobby.* FROM classified mt LEFT JOIN fordon ON fordon.classified_id = mt.classified_id LEFT JOIN boende ON boende.classified_id = mt.classified_id LEFT JOIN elektronik ON elektronik.classified_id = mt.classified_id LEFT JOIN business ON business.classified_id = mt.classified_id LEFT JOIN hem_inredning ON hem_inredning.classified_id = mt.classified_id LEFT JOIN hobby ON hobby.classified_id = mt.classified_id ORDER BY modify_date DESC LIMIT 0, 2 Thanks, if you need more input let me know. Basically Q is, do I have to make two queries?

    Read the article

  • Linq for two tables

    - by Diana
    I need to do something like this, My two tables have the same signature, but different class so It suppose to work but it is not working. var myTable; if (booleanVariable == true) { myTable = table1; } else { myTable = table2; } var myLinq1 = from p in myTable join r in myOtherTable select p; In this case, I have to initialize myTable I have tried also, var myTablev= table2; if (booleanVariable == true) { myTable = table1; } var myLinq1 = from p in myTable join r in myOtherTable select p; then var is type table2, then it can't be changed to table1 type. I need help, I don't want to make a copy paste of all the code. the linq query is huge, and it s nested with 5 or 6 queries. also I have to do this on 12 different methods. Thanks a lot for your help.

    Read the article

  • Why does this MySQL function return null?

    - by Shore
    Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null. I think the code is self-explanatory: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE body varchar(600); DECLARE created DATETIME; DECLARE anonymous TINYINT(1); DECLARE screen_name varchar(64); DECLARE result TEXT; DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET result = ''; OPEN cur1; REPEAT FETCH cur1 INTO body, created, anonymous, screen_name; SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>'); UNTIL done END REPEAT; CLOSE cur1; RETURN result; END | DELIMITER ; mysql> DELIMITER ; mysql> select get_idiscussion_ask(1); +------------------------+ | get_idiscussion_ask(1) | +------------------------+ | NULL | +------------------------+ 1 row in set (0.01 sec) mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1; +------+---------------------+-----------+-------------+ | body | created | anonymous | screen_name | +------+---------------------+-----------+-------------+ | haha | 2009-05-27 04:57:51 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:52 | 0 | NULL | | haha | 2009-05-27 04:57:53 | 0 | NULL | +------+---------------------+-----------+-------------+ 4 rows in set (0.00 sec) For those who don't think the code is self-explanatory: Why the function returns NULL?

    Read the article

  • Nested joins hide table names

    - by Sergio
    Hi: I have three tables: Suppliers, Parts and Types. I need to join all of them while discriminating columns with the same name (say, "id") in the three tables. I would like to successfully run this query: CREATE VIEW Everything AS SELECT Suppliers.name as supplier, Parts.id, Parts.description, Types.typedesc as type FROM Suppliers JOIN (Parts JOIN Types ON Parts.type_id = Types.id) ON Suppliers.id = Parts.supplier_id; My DBMS (sqlite) complains that "there is not such a column (Parts.id)". I guess it forgets table names once the JOIN is done but then how can I refer to the column id that belongs to the table Parts?

    Read the article

  • Find the highest number of occurences in a column in SQL

    - by Ronnie
    Given this table: Order custName description to_char(price) A desa $14 B desb $14 C desc $21 D desd $65 E dese $21 F desf $78 G desg $14 H desh $21 I am trying to display the whole row where prices have the highest occurances, in this case $14 and $21 I believe there needs to be a subquery. So i started out with this: select max(count(price)) from orders group by price which gives me 3. after some time i didn't think that was helpful. i believe i needed the value 14 and 21 rather the the count so i can put that in the where clause. but I'm stuck how to display that. any help?

    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 | 650 651 652 653 654 655 656 657 658 659 660 661  | Next Page >