Search Results

Search found 36186 results on 1448 pages for 'sql 11'.

Page 649/1448 | < Previous Page | 645 646 647 648 649 650 651 652 653 654 655 656  | Next Page >

• Why index_merge is not used here?

  - by user198729

  Setup: mysql> create table t(a integer unsigned,b integer unsigned); mysql> insert into t(a,b) values (1,2),(1,3),(2,4); mysql> create index i_t_a on t(a); mysql> create index i_t_b on t(b); mysql> explain select * from t where a=1 or b=4; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Is there something I'm missing?

  Read the article

• PostgreSQL JOIN with array type with array elements order, how to implement?

  - by Adiasz

  Hello I have two tables in database: CREATE TABLE items( id SERIAL PRIMARy KEY, ... some other fields ); This table contains come data row with unique ID. CREATE TABLE some_choosen_data_in_order( id SERIAL PRIMARy KEY, id_items INTEGER[], ); This table contains array type field. Each row contains values of IDs from table "items" in specyfic order. For example: {2,4,233,5}. Now, I want to get data from table "items" for choosen row from table "some_choosen_data_in_order" with order for elements in array type. The my attempt is JOIN: SELECT I.* FROM items AS I JOIN some_choosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ? Second attempt was subquery like: SELECT I.* FROM items AS I WHERE I.id = ANY (ARRAY[SELECT S.id_items FROM some_choosen_data_in_order WHERE id = ?]) But none of them keep IDs order in array field. Could You help me, how to get data from "items" table with correspond with array IDs order from "some_choosen_data_in_order" table for specyfic row?

  Read the article

• Wiki Database, is there one?

  - by Faiz

  I was searching the net for something like a wiki database, just like wikipedia but instead stores structured content, editable by users. What I was looking for was an online database accessible by everyone where people can design the schema and data with proper versioning of both schema and data. I couldn't find any such site. I am not sure if it is my search skills or if there really is no wiki database as of now. Does anyone out there know anything like this? I think there is a great potential for something like this. A possible example will be a website with a GUI for querying a MySQL DB where any website visitor can create DB objects and populate data.

  Read the article

• FIND_IN_SET exists in MySQL but how do I make it in SQLite?

  - by Pentium10

  In MySQL there exists FIND_IN_SET, that locates a match in a comma separated list. mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 How would I be able to do this in SQLite in query? Please note, that I can't define or add user defined functions, the Android platform doesn't allow it.

  Read the article

• Date arithmetic using integer values

  - by Dave Jarvis

  Problem String concatenation is slowing down a query: date(extract(YEAR FROM m.taken)||'-1-1') d1, date(extract(YEAR FROM m.taken)||'-1-31') d2 This is realized in code as part of a string, which follows (where the p_ variables are integers): date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''') d1, date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''') d2 This part of the query runs in 3.2 seconds with the dates, and 1.5 seconds without, leading me to believe there is ample room for improvement. Question What is a better way to create the date (presumably without concatenation)? Many thanks!

  Read the article

• Replace duplicate spaces with single space in TSQL

  - by Chris

  I need to ensure that a given field does not have more than one space (not concerned about all white space, just space) between characters. So 'single spaces only' Needs to turn into 'single spaces only' The below will not work select replace('single spaces only',' ',' ') as it would result in 'single spaces only' I would really prefer to stick with native TSQL rather than a CLR based solution. Thoughts?

  Read the article

• Count times ID appears in a table and return in row.

  - by Tyler

  SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id ORDER BY Boats.date DESC, river, river_company.company Returns everything I need. But how would I add a [Photos] table and count how many times Boats.id occurs in it and add that to the returned rows. So if there are 5 photos for boat #17 I want the record for boat #17 to say PhotoCount = 5

  Read the article

• How does Select statement works in a Dynamic Linq Query?

  - by Richard77

  Hello, 1) I've a Product table with 4 columns: ProductID, Name, Category, and Price. Here's the regular linq to query this table. public ActionResult Index() { private ProductDataContext db = new ProductDataContext(); var products = from p in db.Products where p.Category == "Soccer" select new ProductInfo { Name = p.Name, Price = p.Price} return View(products); } Where ProductInfo is just a class that contains 2 properties (Name and Price). The Index page Inherits ViewPage - IEnumerable - ProductInfo. Everything works fine. 2) To dynamicaly execute the above query, I do this: Public ActionResult Index() { var products = db.Products .Where("Category = \"Soccer\"") .Select(/* WHAT SOULD I WRITE HERE TO SELECT NAME & PRICE?*/) return View(products); } I'm using both 'System.Lind.Dynamic' namespace and the DynamicLibrary.cs (downloaded from ScottGu blog). Here are my questions: What expression do I use to select only Name and Price? (Most importantly) How do I retrieve the data in my view? (i.e. What type the ViewPage inherits? ProductInfo?)

  Read the article

• SSIS(sql server integration service) xml data flow

  - by swapna

  Hi, I have an xml file the content which i have to write to a Database table using ssis pacakge. I am using xml source nad oledb destination My issue now is this xml file generate multiple outputs .(event,produt,offer,form) etc. But i need to write all in one data row(more than one if 2 products are there for the event) in the database. But i do not know how to use this multiple outputs and make a single row for a event. I hav read numerous articles about this subject but not able to take a decision.what is the right way of doing this. 1) xml source ? (if i use this how do i merge the multiple outputs) 2) or a script task using xml objects read and write to the DB. or anything new ? Please provide me some solutions xml sample file * - ABc. 2009-06-07 2010-04-30 region test 1 contact - offertest product1 product1 187 * Thanks SNA

  Read the article

• Trouble with LINQ databind to GridView and RowDataBound

  - by Michael

  Greetings all, I am working on redesigning my personal Web site using VS 2008 and have chosen to use LINQ to create by data-access layer. Part of my site will be a little app to help manage my budget better. My first LINQ query does successfully execute and display in a GridView, but when I try to use a RowDataBound event to work with the results and refine them a bit, I get the error: The type or namespace name 'var' could not be found (are you missing a using directive or an assembly reference?) This interesting part is, if I just try to put in a var s = "s"; anywhere else in the same file, I get the same error too. If I go to other files in the web project, var s = "s"; compiles fine. Here is the LINQ Query call: public static IQueryable pubGetRecentTransactions(int param_accountid) { clsDataContext db; db = new clsDataContext(); var query = from d in db.tblMoneyTransactions join p in db.tblMoneyTransactions on d.iParentTransID equals p.iTransID into dp from p in dp.DefaultIfEmpty() where d.iAccountID == param_accountid orderby d.dtTransDate descending, d.iTransID ascending select new { d.iTransID, d.dtTransDate, sTransDesc = p != null ? p.sTransDesc : d.sTransDesc, d.sTransMemo, d.mTransAmt, d.iCheckNum, d.iParentTransID, d.iReconciled, d.bIsTransfer }; return query; } protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { this.prvLoadData(); } } internal void prvLoadData() { prvCtlGridTransactions.DataSource = clsMoneyTransactions.pubGetRecentTransactions(2); prvCtlGridTransactions.DataBind(); } protected void prvCtlGridTransactions_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { var datarow = e.Row.DataItem; var s = "s"; e.Row.Cells[0].Text = datarow.dtTransDate.ToShortDateString(); e.Row.Cells[1].Text = datarow.sTransDesc; e.Row.Cells[2].Text = datarow.mTransAmt.ToString("c"); e.Row.Cells[3].Text = datarow.iReconciled.ToString(); }//end if }//end RowDataBound My googling to date hasn't found a good answer, so I turn it over to this trusted community. I appreciate your time in assisting me.

  Read the article

• Acquiring Table Lock in Database - Interview Question

  - by harigm

  One of my interview Questions, if multiple users across the world are accessing the application, in which it uses a Table which has a Primary Key as Auto Increment Field. The Question how can you prevent the other user getting the Same Primary key when the other user is executing? My answer was I will obtain the Lock on the table and I will make the user to wait Until that user is released with the Primary key. But the Question How do you acquire the Table lock programmatically and implement this? If there are 1000 users coming every minute to the application, if you explicity hold the lock on the table, then the application will become slower? How do you manage this? Please suggest the possible answers for the above question

  Read the article

• .NET and Closing MSSQL connections

  - by user307076

  I am having a hard time figuring out why the following constructor will not close connnections. When I view the active connections. Here is the code that I have. Public Sub New(ByVal UserID As Integer) Dim oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString()) Dim cmd As New SqlCommand("stored proc", oConn) Dim sdr As SqlDataReader cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@userID", UserID) oConn.Open() sdr = cmd.ExecuteReader() Try If Not sdr.HasRows Then sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() Exit Sub End If 'User has account in WATS, proceed to load account information While sdr.Read _firstname = Convert.ToString(sdr("First Name")) _lastname = Convert.ToString(sdr("Last Name")) End While Catch ex As Exception 'Throw New Exception("User Error: " + ex.Message) Finally sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() End Try End Sub

  Read the article

• How to query two tables based on whether or not record exists in a third?

  - by Katherine

  I have three tables, the first two fairly standard: 1) PRODUCTS table: pid pname, etc 2) CART table: cart_id cart_pid cart_orderid etc The third is designed to let people save products they buy and keep notes on them. 3) MYPRODUCTS table: myprod_id myprod_pid PRODUCTS.prod_id = CART.cart_prodid = MYPRODUCTS.myprod_pid When a user orders, they are presented with a list of products on their order, and can optionally add that product to myproducts. I am getting the info necessary for them to do this, with a query something like this for each order: SELECT cart.pid, products.pname, products.pid FROM products, cart WHERE products.pid = cart_prodid AND cart_orderid=orderid This is fine the first time they order. However, if they subsequently reorder a product which they have already added to myproducts, then it should NOT be possible for them to add it to myproducts again - basically instead of 'Add to MyProducts' they need to see 'View in MyProducts'. I am thinking I can separate the products using two queries: Products never added to MyProducts By somehow identifying whether the user has the product in MyProducts already, and if so excluding it from the query above. Products already in MyProducts By reversing the process above. I need some pointers on how to do this though.

  Read the article

• How to get the last element by date of each "type" in LINQ or TSQL

  - by Mauro

  Imagine to have a table defined as CREATE TABLE [dbo].[Price]( [ID] [int] NOT NULL, [StartDate] [datetime] NOT NULL, [Price] [int] NOT NULL ) where ID is the identifier of an action having a certain Price. This price can be updated if necessary by adding a new line with the same ID, different Price, and a more recent date. So with a set of a data like ID StartDate Price 1 01/01/2009 10 1 01/01/2010 20 2 01/01/2009 10 2 01/01/2010 20 How to obtain a set like the following? 1 01/01/2010 20 2 01/01/2010 20

  Read the article

• Get sum of two columns in one LINQ query

  - by Axarydax

  Hi, let's say that I have a table called Items (ID int, Done int, Total int) I can do it by two queries: int total = m.Items.Sum(p=>p.Total) int done = m.Items.Sum(p=>p.Done) But I'd like to do it in one query, something like this: var x = from p in m.Items select new { Sum(p.Total), Sum(p.Done)}; Surely there is a way to call aggregate functions from LINQ syntax...?

  Read the article

• MySQL MyISAM table performance... painfully, painfully slow

  - by Salman A

  I've got a table structure that can be summarized as follows: pagegroup * pagegroupid * name has 3600 rows page * pageid * pagegroupid * data references pagegroup; has 10000 rows; can have anything between 1-700 rows per pagegroup; the data column is of type mediumtext and the column contains 100k - 200kbytes data per row userdata * userdataid * pageid * column1 * column2 * column9 references page; has about 300,000 rows; can have about 1-50 rows per page The above structure is pretty straight forwad, the problem is that that a join from userdata to page group is terribly, terribly slow even though I have indexed all columns that should be indexed. The time needed to run a query for such a join (userdata inner_join page inner_join pagegroup) exceeds 3 minutes. This is terribly slow considering the fact that I am not selecting the data column at all. Example of the query that takes too long: SELECT userdata.column1, pagegroup.name FROM userdata INNER JOIN page USING( pageid ) INNER JOIN pagegroup USING( pagegroupid ) Please help by explaining why does it take so long and what can i do to make it faster. Edit #1 Explain returns following gibberish: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE userdata ALL pageid 372420 1 SIMPLE page eq_ref PRIMARY,pagegroupid PRIMARY 4 topsecret.userdata.pageid 1 1 SIMPLE pagegroup eq_ref PRIMARY PRIMARY 4 topsecret.page.pagegroupid 1 Edit #2 SELECT u.field2, p.pageid FROM userdata u INNER JOIN page p ON u.pageid = p.pageid; /* 0.07 sec execution, 6.05 sec fecth */ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE u ALL pageid 372420 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 topsecret.u.pageid 1 Using index SELECT p.pageid, g.pagegroupid FROM page p INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid; /* 9.37 sec execution, 60.0 sec fetch */ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE g index PRIMARY PRIMARY 4 3646 Using index 1 SIMPLE p ref pagegroupid pagegroupid 5 topsecret.g.pagegroupid 3 Using where Moral of the story Keep medium/long text columns in a separate table if you run into performance problems such as this one.

  Read the article

• LIKE operator with $variable

  - by skarama

  This is my first question here and I hope it is simple enough to get a quick answer! Basically, I have the following code: $variable = curPageURL(); $query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE '$variable' ; If I echo the $variable, it prints the current page's url( which is a javascript on my page) Ultimately, what I want, is to be able to make a search for which the search-term is the current page's url, with wildcards before and after. I am not sure if this is possible at all, or if I simply have a syntax error, because I get no errors, simply no result! I tried : $query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE '"echo $variable" ' ; But again, I'm probably missing or using a misplaced ' " ; etc. Please tell me what I'm doing wrong!

  Read the article

• Why does a conditional not affect query speed?

  - by Telos

  I have a stored procedure that was taking a "long" period of time to execute. The query only needs to return data in one case, so I figured I could check for that case and just return before hitting the actual query. The only problem is that it still takes the same amount of time to execute with an if statement. I have verified that the code inside the if is not executing, and that if I replace the complex query with a simple select the speed is fine... so now I'm confused. Why is the query being slowed down by code that doesn't get executed when the conditional is false? Here's the query itself: ALTER PROCEDURE [dbo].[pr_cbc_GetCokeInfo] @pa_record int, @pb_record int AS BEGIN SET NOCOUNT ON; declare @ticketRec int SELECT @ticketRec = TicketRecord FROM eservice_live..v_sdticket where TicketRecord=@pa_record AND serviceCompanyID = 1139 AND @pb_record IS NULL if @ticketRec IS NULL return select record = null, doc_ref = @pa_record, memo_type = 'I', memo = 'Bottler: ' + isnull(Bottler, '') + ' ' + 'Sales Loc: ' + isnull(SalesLocation, '') + ' ' + 'Outlet Desc: ' + isnull(OutletDesc, '') + ' ' + 'City: ' + isnull(OutletCity, '') + ' ' + 'EquipNo: ' + isnull(EquipNo, '') + ' ' + 'SerialNo: ' + isnull(SerialNo, '') + ' ' + 'PhaseNo: ' + isnull(cast(PhaseNo as varchar(255)), '') + ' ' + 'StaticIP: ' + isnull(StaticIP, '') + ' ' + 'Air Card: ' + isnull(AirCard, '') FROM eservice_live..v_SDExtendedInfoField ef JOIN eservice_live..CokeSNList csl ON ef.valueText=csl.SerialNo where ef.docType='CLH' AND ef.docref = @ticketRec AND ef.ExtendedDocNumber=5 SET NOCOUNT OFF; END

  Read the article

• LINQ: Create persistable Associations in Code, Without Foreign Key

  - by Alex

  Hello, I know that I can create LINQ Associations without a Foreign Key. The problem is, I've been doing this by adding the [Association] attribute in the DBML file (same as through the designer), which will get erased again after I refresh my database (and reload the entire table structure). I know that there is the MyData.cs file (as part of the DBML) in which I can place my partial extensions etc. to domain objects (to persist even after I refresh the DBML), but I don't know how to create an association there?

  Read the article

• what is the difference between triggers, assertions and checks (in database)

  - by Am1rr3zA

  Can anybody explain (or suggest a site or paper) the exact difference between triggers, assertions and checks, and also describe where I should use them? EDIT: I mean in database, not in any other system or language.

  Read the article

• filtering for multiple values on one column. All values must exist, else - return zero

  - by Andrew

  Hello All, I would like to filter one column in a table for couple values and show results only if all those values are there. If one or more is missing, then return zero results. example table +----+--------+----------+ | id | Fruit | Color | +----+--------+----------+ | 1 | apple | red | | 2 | mango | yellow | | 3 | banana | yellow | +----+--------+----------+ example "wrong" code: (this must return 3 rows) select Fruit FROM table WHERE Color = red AND Color = yellow but select Fruit FROM table WHERE Color = red AND Color = green must return 0 rows. (If i use select Fruit FROM table WHERE Color = red OR Color = green i get 1 row which is not what i need) I am using PHP with form where user checks different checkboxes that represent different values of the same column. So when he selects multiple checkboxes, all those values should be in the result set, otherwise no result should be given. Thank you, Andrew

  Read the article

• Order by text and then by number

  - by Chaim Chaikin

  I have data like: Audio 1 File 10 Audio 2 Audio 3 File 11 Audio 13 Audio 22 File 20 Test 22 Audio 10 File 1 File 2 I need it order first by the text (i.e. Audio, File, Test) and then by number (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 etc.) The problem is that sorting it returns something like this: Audio 1 Audio 10 Audio 13 Audio 2 Audio 22 Audio 3 File 1 File 10 File 11 File 2 File 20 Test 22 While the result I want is: Audio 1 Audio 2 Audio 3 Audio 10 Audio 13 Audio 22 File 1 File 2 File 10 File 11 File 20 Test 22 If they were just numbers (i.e. without the audio, file, test) then I could just sort numerically. However, how can I sort here first by text and then by number.

  Read the article

• Integer comparison as string

  - by J Pollack

  Hi I have an integer column and I want to find numbers that start with specific digits. For example they do match if I look for '123': 1234567 123456 1234 They do not match: 23456 112345 0123445 Is the only way to handle the task by converting the Integers into Strings before doing string comparison? Also I am using Postgre regexp_replace(text, pattern, replacement) on numbers which is very slow and inefficient way doing it. The case is that I have large amount of data to handle this way and I am looking for the most economical way doing this.

  Read the article

• Advice about insert into SQLCE

  - by Alexander

  i am inserting about 1943 records by these function into SQLCE.This is my insert function.Parameters come from StringReader(string comes from webservice).This function executes 1943 times and takes about 20 seconds.I dropped table's indexes,what can i do to improve it?I create just 1 time mycomm and sqlceresultset. Public Function Insert_Function(ByVal f_Line() As String, ByRef myComm As SqlCeCommand, ByRef rs As SqlCeResultSet) As String Try Dim rec As SqlCeUpdatableRecord = rs.CreateRecord() rec.SetInt32(0, IIf(f_Line(1) = "", DBNull.Value, f_Line(1))) rec.SetInt32(1, IIf(f_Line(2) = "", DBNull.Value, f_Line(2))) rec.SetInt32(2, IIf(f_Line(3) = "", DBNull.Value, f_Line(3))) rec.SetInt32(3, IIf(f_Line(4) = "", DBNull.Value, f_Line(4))) rec.SetValue(4, IIf(f_Line5(5) = "", DBNull.Value, f_Line(5))) rs.Insert(rec) rec = Nothing Catch ex As Exception strerr_col = ex.Message End Try Return strerr_col End Function

  Read the article

• Connect sortable lists together and update SQL using jQuery UI

  - by rebellion

  I'm using jQuery UI's sortable lists to sort items in a todo list, and the reordering of the lists works like a charm. I have several UL lists for each todo category, i.e. Design, Development, etc. I want to be able to move an item from one category to another, and jQuery UI's sortable lists plugin allows me to do this with the connectWith option parameter. But I can't seem to find how to update the database as well. The todo database table has a field for a reference to the category which is laying in it's own table. This is the current code: $('.sortable').sortable({ axis: 'y', opacity: .5, cancel: '.no-tasks', connectWith: '.sortable', update: function(){ $.ajax({ type: 'POST', url: basepath + 'task/sort_list', data: $(this).sortable('serialize') }); } }); Do I need to add another value to the AJAX's data parameter, or do the sortable plugin this for me in the serialize function?

  Read the article

< Previous Page | 645 646 647 648 649 650 651 652 653 654 655 656  | Next Page >