Search Results

Search found 28024 results on 1121 pages for 'sql 2014'.

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 650/1121 | < Previous Page | 646 647 648 649 650 651 652 653 654 655 656 657  | Next Page >

  • Best indexing strategy for several varchar columns in Postgres

    - by Corey
    I have a table with 10 columns that need to be searchable (the table itself has about 20 columns). So the user will enter query criteria for at least one of the columns but possibly all ten. All non-empty criteria is then put into an AND condition Suppose the user provided non-empty criteria for column1 and column4 and column8 the query would be: select * from the_table where column1 like '%column1_query%' and column4 like '%column4_query%' and column8 like '%column8_query%' So my question is: am I better off creating 1 index with 10 columns? 10 indexes with 1 column each? Or do I need to find out what sets of columns are queried together frequently and create indexes for them (an index on cols 1,4 and 8 in the case above). If my understanding is correct a single index of 10 columns would only work effectively if all 10 columns are in the condition. Open to any suggestions here, additionally the rowcount of the table is only expected to be around 20-30K rows but I want to make sure any and all searches on the table are fast. Thanks!

    Read the article

  • mysql: get all rows into 1 column

    - by andufo
    hi, i have 3 tables: post (id_post, title) tag (id_tag, name) post_tag (id_post_tag, id_post, id_tag) Lets suppose that id_post 3 has 4 linked tags 1,2,3,4 (soccer, basket, tennis and golf). Is there a way to return something like this in ONE row? col 1 id_post = 3 col 2 tags = soccer basket tennis golf Thanks

    Read the article

  • performing more than one Where in query return null!!! why? how to fix this?

    - by Sadegh
    hi, i have wrote a method that filters output with provided query and return it. when one Where excuted; it return correct output but when more than one Where excuted; output is null and Exception occured with message "Enumeration yielded no results". why? how i can fix it? public IQueryable<SearchResult> PerformSearch(string query, int skip = 0, int take = 5) { if (!string.IsNullOrEmpty(query)) { var queryList = query.Split('+').ToList(); var results = GENERATERESULTS(); string key; foreach (string _q in queryList) { if (_q.StartsWith("(") && _q.EndsWith(")")) { key = _q.Replace("(", "").Replace(")", ""); results = results.Where(q => q.Title.Contains(key, StringComparison.CurrentCultureIgnoreCase)); } else if (_q.StartsWith("\"") && _q.EndsWith("\"")) { key = _q.Replace("\"", "").Replace("\"", ""); results = results.Where(q => q.Title.Contains(key, StringComparison.CurrentCulture)); } else if (_q.StartsWith("-(") && _q.EndsWith(")")) { key = _q.Replace("-(", "").Replace(")", ""); results = results.Where(q=> !q.Title.Contains(key, StringComparison.CurrentCultureIgnoreCase)); } else { key = _q; results = results.Where(q => q.Title.Contains(key, StringComparison.CurrentCulture)); } } this._Count = results.Count(); results = results.Skip(skip).Take(take); this._EndOn = DateTime.Now; this.ExecutionTime(); return results; } else return null; } thanks in advance ;)

    Read the article

  • jpa join query on a subclass

    - by Brian
    I have the following relationships in JPA (hibernate). Object X has two subclasses, Y and Z. Object A has a manyToOne relationship to object X. (Note, this is a one-sided relationship so object X cannot see object A). Now, I want to get the max value of a column in object A, but only where the relationship is of a specific subtype, ie...Y. So, that equates to...get the max value of column1 in object A, across all instances of A where they have a relationship with Y. Is this possible? I'm a bit lost as how to query it. I was thinking of something like: String query = "SELECT MAX(a.columnName) FROM A a join a.x; Query query = super.entityManager.createQuery(query); query.execute(); However that doesn't take account of the subclass of X...so I'm a bit lost. Any help would be much appreciated.

    Read the article

  • Table for each region in MySQL

    - by King Wu
    There are four regions with more than one million records total. Should I create a table with a region column or a table for each region and combine them to get the top ranks? If I combine all four regions, none of my columns will be unique so I will need to also add an id column for my primary key. Otherwise, name, accountId & characterId would be candidate keys or should I just add an id column anyways. Table: ---------------------------------------------------------------- | name | accountId | iconId | level | characterId | updateDate | ----------------------------------------------------------------

    Read the article

  • Why is this postgresql query so slow?

    - by user315975
    I'm no database expert, but I have enough knowledge to get myself into trouble, as is the case here. This query SELECT DISTINCT p.* FROM points p, areas a, contacts c WHERE ( p.latitude > 43.6511659465 AND p.latitude < 43.6711659465 AND p.longitude > -79.4677941889 AND p.longitude < -79.4477941889) AND p.resource_type = 'Contact' AND c.user_id = 6 is extremely slow. The points table has fewer than 2000 records, but it takes about 8 seconds to execute. There are indexes on the latitude and longitude columns. Removing the clause concering the resource_type and user_id make no difference. The latitude and longitude fields are both formatted as number(15,10) -- I need the precision for some calculations. There are many, many other queries in this project where points are compared, but no execution time problems. What's going on?

    Read the article

  • Best way to model Customer <--> Address

    - by Jen
    Every Customer has a physical address and an optional mailing address. What is your preferred way to model this? Option 1. Customer has foreign key to Address Customer (id, phys_address_id, mail_address_id) Address (id, street, city, etc.) Option 2. Customer has one-to-many relationship to Address, which contains a field to describe the address type Customer (id) Address (id, customer_id, address_type, street, city, etc.) Option 3. Address information is de-normalized and stored in Customer Customer (id, phys_street, phys_city, etc. mail_street, mail_city, etc.) One of my overriding goals is to simplify the object-relational mappings, so I'm leaning towards the first approach. What are your thoughts?

    Read the article

  • How to use SQL trigger to record the affected column's row number

    - by Freeman
    I want to have an 'updateinfo' table in order to record every update/insert/delete operations on another table. In oracle I've written this: CREATE TABLE updateinfo ( rnumber NUMBER(10), tablename VARCHAR2(100 BYTE), action VARCHAR2(100 BYTE), UPDATE_DATE date ) DROP TRIGGER TRI_TABLE; CREATE OR REPLACE TRIGGER TRI_TABLE AFTER DELETE OR INSERT OR UPDATE ON demo REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN if inserting then insert into updateinfo(rnumber,tablename,action,update_date ) values(rownum,'demo', 'insert',sysdate); elsif updating then insert into updateinfo(rnumber,tablename,action,update_date ) values(rownum,'demo', 'update',sysdate); elsif deleting then insert into updateinfo(rnumber,tablename,action,update_date ) values(rownum,'demo', 'delete',sysdate); end if; -- EXCEPTION -- WHEN OTHERS THEN -- Consider logging the error and then re-raise -- RAISE; END TRI_TABLE; but when checking updateinfo, all rnumber column is zero. is there anyway to retrieve the correct row number?

    Read the article

  • Converting time/date from XML file into MYSQL format

    - by IconicDigital
    One of the affiliate networks provides a feed with the following time/date format. <startDate>1349992800000</startDate> <endDate>1355266799999</endDate> My problem is I am trying to convert this to a MYSQL format, I have tried mktime and strtotime with no luck the date seems to come out wrong. I know this is the time since the Equinox, I am just not sure how to convert this to a MYSQL format.

    Read the article

  • Why is SQLite3 using covering indices instead of the indices I created?

    - by Geoff
    I have an extremely large database (contacts has ~3 billion entries, people has ~280 million entries, and the other tables have a negligible number of entries). Most other queries I've run are really fast. However, I've encountered a more complicated query that's really slow. I'm wondering if there's any way to speed this up. First of all, here is my schema: CREATE TABLE activities (id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE contacts ( id INTEGER PRIMARY KEY, person1_id INTEGER NOT NULL, person2_id INTEGER NOT NULL, duration REAL NOT NULL, -- hours activity_id INTEGER NOT NULL -- FOREIGN_KEY(person1_id) REFERENCES people(id), -- FOREIGN_KEY(person2_id) REFERENCES people(id) ); CREATE TABLE people ( id INTEGER PRIMARY KEY, state_id INTEGER NOT NULL, county_id INTEGER NOT NULL, age INTEGER NOT NULL, gender TEXT NOT NULL, -- M or F income INTEGER NOT NULL -- FOREIGN_KEY(state_id) REFERENCES states(id) ); CREATE TABLE states ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, abbreviation TEXT NOT NULL ); CREATE INDEX activities_name_index on activities(name); CREATE INDEX contacts_activity_id_index on contacts(activity_id); CREATE INDEX contacts_duration_index on contacts(duration); CREATE INDEX contacts_person1_id_index on contacts(person1_id); CREATE INDEX contacts_person2_id_index on contacts(person2_id); CREATE INDEX people_age_index on people(age); CREATE INDEX people_county_id_index on people(county_id); CREATE INDEX people_gender_index on people(gender); CREATE INDEX people_income_index on people(income); CREATE INDEX people_state_id_index on people(state_id); CREATE INDEX states_abbreviation_index on states(abbreviation); CREATE INDEX states_name_index on states(name); Note that I've created an index on every column in the database. I don't care about the size of the database; speed is all I care about. Here's an example of a query that, as expected, runs almost instantly: SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; Here's the troublesome query: SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); Now, what I think would happen is that each subquery would use each relevant index I've created to speed this up. However, when I show the query plan, I see this: sqlite> EXPLAIN QUERY PLAN SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); 0|0|0|SEARCH TABLE contacts USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 2|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 2|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 2|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person1_id_index (person1_id=?) (~12 rows) 3|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 3|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 3|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person2_id_index (person2_id=?) (~12 rows) 1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT) In fact, if I show the query plan for the first query I posted, I get this: sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; 0|0|1|SEARCH TABLE states USING COVERING INDEX states_abbreviation_index (abbreviation=?) (~1 rows) 0|1|0|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) Why is SQLite using covering indices instead of the indices I created? Shouldn't the search in the people table be able to happen in log(n) time given state_id which in turn is found in log(n) time?

    Read the article

  • why datetime.now not work when I didn't use tolist?

    - by MemoryLeak
    When I use datacontext.News .Where(p => p.status == true) .Where(p => p.date <= DateTime.Now) .ToList(); the system will return no results; When I use datacontext.News .Where(p => p.status == true) .ToList() .Where(p => p.date <= DateTime.Now) .ToList(); system will return expected results. Can anyone tell me what's up? Thanks in advance !

    Read the article

  • Deleting rows from different tables

    - by Ross
    Here is what i'm trying to do: Delete the project from projects table and all the images associated with that project in the images table Lets say $del_id = 10 DELETE FROM projects, images WHERE projects.p_id = '$del_id' AND images.p_id = '$del_id' What is wrong with this query

    Read the article

  • Stop invalid data in a attribute with foreign key constraint using triggers?

    - by Eternal Learner
    How to specify a trigger which checks if the data inserted into a tables foreign key attribute, actually exists in the references table. If it exist no action should be performed , else the trigger should delete the inserted tuple. Eg: Consider have 2 tables R(A int Primary Key) and S(B int Primary Key , A int Foreign Key References R(A) ) . I have written a trigger like this : Create Trigger DelS BEFORE INSERT ON S FOR EACH ROW BEGIN Delete FROM S where New.A <> ( Select * from R;) ); End; I am sure I am making a mistake while specifying the inner sub query within the Begin and end Blocks of the trigger. My question is how do I make such a trigger ?

    Read the article

  • Ways to implement tags - pros and cons of each

    - by bobobobo
    Related Using SO as an example, what is the most sensible way to manage tags if you anticipate they will change often? Way 1: Seriously denormalized (comma delimited) table posts +--------+-----------------+ | postId | tags | +--------+-----------------+ | 1 | c++,search,code | Here tags are comma delimited. Pros: Tags are retrieved at once with a single select query. Updating tags is simple. Easy and cheap to update. Cons: Extra parsing on tag retrieval, difficult to count how many posts use which tags. (alternatively, if limited to something like 5 tags) table posts +--------+-------+-------+-------+-------+-------+ | postId | tag_1 | tag_2 | tag_3 | tag_4 | tag_5 | +--------+-------+-------+-------+-------+-------+ | 1 | c++ |search | code | | | Way 2: "Slightly normalized" (separate table, no intersection) table posts +--------+-------------------+ | postId | title | +--------+-------------------+ | 1 | How do u tag? | table taggings +--------+---------+ | postId | tagName | +--------+---------+ | 1 | C++ | | 1 | search | Pros: Easy to see tag counts (count(*) from taggings where tagName='C++'). Cons: tagName will likely be repeated many, many times. Way 3: The cool kid's (normalized with intersection table) table posts +--------+---------------------------------------+ | postId | title | +--------+---------------------------------------+ | 1 | Why is a raven like a writing desk? | table tags +--------+---------+ | tagId | tagName | +--------+---------+ | 1 | C++ | | 2 | search | | 3 | foofle | table taggings +--------+---------+ | postId | tagId | +--------+---------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | Pros: No repeating tag names. More girls will like you. Cons: More expensive to change tags than way #1.

    Read the article

  • Run SSIS Package from T-SQL

    - by Dr. Zim
    I noticed you can use the following stored procedures (in order) to schedule a SSIS package: msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' msdb.dbo.sp_add_job ... msdb.dbo.sp_add_jobstep ... msdb.dbo.sp_update_job ... msdb.dbo.sp_add_jobschedule ... msdb.dbo.sp_add_jobserver ... (You can see an example by right clicking a scheduled job and selecting "Script Job as- Create To".) AND you can use sp_start_job to execute the job immediately, effectively running SSIS packages on demand. Question: does anyone know of any msdb.dbo.[...] stored procedures that simply allow you to run SSIS packages on the fly without using sp_cmdshell directly, or some easier approach?

    Read the article

  • Select *, max(date) works in phpMyAdmin but not in my code

    - by kdobrev
    OK, my statement executes well in phpMyAdmin, but not how I expect it in my php page. This is my statement: SELECT egid , group_name , limit , MAX( date ) FROM employee_groups GROUP BY egid ORDER BY egid DESC ; This is may table: CREATE TABLE employee_groups ( egid int(10) unsigned NOT NULL, date date NOT NULL, group_name varchar(50) NOT NULL, limit smallint(5) unsigned NOT NULL, PRIMARY KEY (egid,date) ) ENGINE=MyISAM DEFAULT CHARSET=cp1251; I want to extract the most recent list of groups, e.g. if a group has been changed I want to have only the last change. And I need it as a list (all groups).

    Read the article

  • sqlite3 date operations when joining two tables in a view?

    - by duncan
    In short, how to add minutes to a datetime from an integer located in another table, in one select statement, by joining them? I have a table P(int id, ..., int minutes) and a table S(int id, int p_id, datetime start) I want to generate a view that gives me PS(S.id, P.id, S.start + P.minutes) by joining S.p_id=P.id The problem is, if I was generating the query from the application, I can do stuff like: select datetime('2010-04-21 14:00', '+20 minutes'); 2010-04-21 14:20:00 By creating the string '+20 minutes' in the application and then passing it to sqlite. However I can't find a way to create this string in the select itself: select p.*,datetime(s.start_at, formatstring('+%s minutes', p.minutes)) from p,s where s.p_id=p.id; Because sqlite as far the documentation tells, does not provide any string format function, nor can I see any alternative way of expressing the date modifiers.

    Read the article

  • HQl equivalent of sql query

    - by kash
    String SQL_QUERY = "SELECT count(*) FROM (SELECT * FROM Url as U where U.pageType=" + 1 + " group by U.pageId having count(U.pageId) = 1)"; query = session.createQuery(SQL_QUERY); I am getting an error org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 23 [ SELECT count() FROM (SELECT * FROM Url as U where U.pageType = 2 group by U.pageId having count(U.pageId) = 1)]

    Read the article

  • trouble connecting to MySql DB (PHP)

    - by user332817
    Hi I have the following PHP code to connect to my db. <?php ob_start(); $host="localhost"; // Host name $username="root"; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $tbl_name="members"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); ?> however I get the following error: Warning: mysql_connect() [function.mysql-connect]: [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in C:\Program Files (x86)\EasyPHP-5.3.2i\www\checklogin.php on line 11 Warning: mysql_connect() [function.mysql-connect]: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. in C:\Program Files (x86)\EasyPHP-5.3.2i\www\checklogin.php on line 11 Fatal error: Maximum execution time of 30 seconds exceeded in C:\Program Files (x86)\EasyPHP-5.3.2i\www\checklogin.php on line 11 I am able to add a db/tables via phpmyadmin but I cant connect using php. here is a screenshot of my phpmyadmin page: http://img294.imageshack.us/img294/1589/sqls.jpg any help would be appreciated, thanks in advance.

    Read the article

  • Reading from an write-only(OUT) parameter in pl/sql

    - by sqlgrasshopper5
    When I tried writing to an read-only parameter(IN) of a function, Oracle complains with an error. But that is not the case when reading from an write-only(OUT) parameter of a function. Oracle silently allows this without any error. What is the reason for this behaviour?. The following code executes without any assignment happening to "so" variable: create or replace function foo(a OUT number) return number is so number; begin so := a; --no assignment happens here a := 42; dbms_output.put_line('HiYA there'); dbms_output.put_line('VAlue:' || so); return 5; end; / declare somevar number; a number := 6; begin dbms_output.put_line('Before a:'|| a); somevar := foo(a); dbms_output.put_line('After a:' || a); end; / Here's the output I got: Before a:6 HiYA there VAlue: After a:42

    Read the article

  • advanced select in Stored Procedure

    - by Auro
    Hey i got this Table: CREATE TABLE Test_Table ( old_val VARCHAR2(3), new_val VARCHAR2(3), Updflag NUMBER, WorkNo NUMBER ); and this is in my Table: INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 30',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('3',' 30',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0); now my Table Looks like this: Row Old_val New_val Updflag WorkNo 1 '1' ' 20' 0 0 2 '2' ' 20' 0 0 3 '2' ' 30' 0 0 4 '3' ' 30' 0 0 5 '4' ' 40' 0 0 6 '5' ' 40' 0 0 (if the value in the new_val column are same then they are together and the same goes to old_val) so in the example above row 1-4 are together and row 5-6 at the moment i have in my Stored Procedure a cursor: SELECT t1.Old_val, t1.New_val, t1.updflag, t1.WorkNo FROM Test_Table t1 WHERE t1.New_val = ( SELECT t2.New_val FROM Test_Table t2 WHERE t2.Updflag = 0 AND t2.Worknr = 0 AND ROWNUM = 1 ) the output is this: Row Old_val New_val Updflag WorkNo 1 1 20 0 0 2 2 20 0 0 my Problem is, i dont know how to get row 1 to 4 with one select. (i had an idea with 4 sub-querys but this wont work if its more data that matches together) does anyone of you have an idea?

    Read the article

  • Round date to 10 minutes interval

    - by Peter Lang
    I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below). I managed to do it by truncating the seconds and then subtracting the last digit of minutes. WITH test_data AS ( SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual ) -- #end of test-data SELECT d, TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data And here is the result: 01.01.2010 10:00:00    01.01.2010 10:00:00 01.01.2010 10:05:00    01.01.2010 10:00:00 01.01.2010 10:09:59    01.01.2010 10:00:00 01.01.2010 10:10:00    01.01.2010 10:10:00 01.01.2099 10:00:33    01.01.2099 10:00:00 Works as expected, but is there a better way? EDIT: I was curious about performance, so I did the following test with 500.000 rows and (not really) random dates. I am going to add the results as comments to the provided solutions. DECLARE t TIMESTAMP := SYSTIMESTAMP; BEGIN FOR i IN ( WITH test_data AS ( SELECT SYSDATE + ROWNUM / 5000 d FROM dual CONNECT BY ROWNUM <= 500000 ) SELECT TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data ) LOOP NULL; END LOOP; dbms_output.put_line( SYSTIMESTAMP - t ); END; This approach took 03.24 s.

    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 | 646 647 648 649 650 651 652 653 654 655 656 657  | Next Page >