Search Results

Search found 27530 results on 1102 pages for 'sql truncate'.

Page 542/1102 | < Previous Page | 538 539 540 541 542 543 544 545 546 547 548 549  | Next Page >

• Excel Spreadsheet - Best way to perform an Oracle Query on a cell

  - by Jamie

  Hi there, I have an Excel Spreadsheeet. There is a cell containing a concatenated name and surname (don't ask why), for example: Cell A2 BLOGGSJOE On this cell, I would like to run the following SQL and output it to cell A3, A4 and A5 SELECT i.id, i.forename, i.surname FROM individual i WHERE UPPER(REPLACE('" & A2 & "', ' ', '')) = UPPER(REPLACE(i.surname|| i.forename, ' ', '')) AND NVL(i.ind_efface, 'N') = 'N' Any idea how I could perform an oracle query on each cell and return the result? I have enabled an oracle datasource connection in Excel, just not sure what to do now. Is this a stupid approach, and can you recommend a better more proficient way? Thanks muchly! I lack the necessary experience in this type of thing! :-) EDIT: I am aware that I could just write a simple ruby/php/python/whatever script to loop through the excel spreadsheet (or csv file), and then perform the query etc. but i thought there might be a quick way in excel itself.

  Read the article

• populate object graph from database

  - by Rama

  Hi, I would like to know the best way to populate an object that has a collection of child objects and each child object may inturn have a collection of objects, from database without making multiple calls to the database to get child objects for each object. basically in hierarchical format something like for example a customer has orders and each order has order items. is it best to retrieve the data in xml format (SQL server 2005), or retrieve a dataset by joining the related tables together and then map that the data to the object? thanks in advance for your help.

  Read the article

• Automatically CONCATENATE text on data entry

  - by Bill T

  I am a newbie and need help. I have a table called "Employees". It has 2 fields [number] and [encode]. I want to automatically take whatever number is entered into [number] and store it in [encode] so that it is preceded by the appropriate amount of 0's to always make 12 digits. Example: user enters '123' into [number], '000000000123' is automatically stored in [encode] user enters '123456789' into [number], '000123456789' is automatically stored in [encode] I think i want to write a trigger to accomplish this. I think that would make it happen at the time of data entry. is that right? The main idea is would be something like this: variable1 = LENGTH [number] variable2 = REPEAT (0,12-variable1) variable3 = CONCATENATE (variable2, [number]) [encode] = variable3 I just don't know enough to make this happen ANY help would be FANTASTIC. I have SQL-SERVER 2005 and both fields are text

  Read the article

• LINQ to Entity, joining on NOT IN tables

  - by SlackerCoder

  My brain seems to be mush right now! I am using LINQ to Entity, and I need to get some data from one table that does NOT exist in another table. For example: I need the groupID, groupname and groupnumber from TABLE A where they do not exist in TABLE B. The groupID will exist in TABLE B, along with other relevant information. The tables do not have any relationship. In SQL it would be quite simply (there is a more elegant and efficient solution, but I want to paint a picture of what I need) SELECT GroupID, GroupName, GroupNumber, FROM TableA WHERE GroupID NOT IN (SELECT GroupID FROM TableB) Is there an easy/elegant way to do this? Right now I have a bunch of queries hitting the db, then comparing, etc. It's pretty messy. Thanks.

  Read the article

• c# reading integer fields from database

  - by arnoldino

  what is wrong with this code? try { if (cmd == null) getConnection(); cmd.CommandText = "Select id from HMonthInventary where machine_id=37"; SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows==true) { reader.Read(); string s = reader[0].ToString(); // return first element reader.Close(); return s; } reader.Close(); return null; } catch (Exception e) { MessageBox.Show("Caught exception: " + e.Message+"|"+cmd.CommandText); return null; } I checked the sql statement, it turns the right value. why can't I read it? the returnvalue is "".

  Read the article

• Best way to construct this query?

  - by Andrew

  I have two tables set up similar to this (simplified for the quest): actions- id - user_id - action - time users - id - name I want to output the latest action for each user. I have no idea how to go about it. I'm not great with SQL, but from what I've looked up, it should look something like the following. not sure though. SELECT `users`.`name`, * FROM users, actions JOIN < not sure what to put here > ORDER BY `actions`.`time` DESC < only one per user_id > Any help would be appreciated.

  Read the article

• Multi join query returns to many results and improperly matched

  - by Woot4Moo

  I have the following minimal schema in Oracle: http://sqlfiddle.com/#!4/c1ed0/14 The queries I have run yield too many results and this query: select cat.*, status.*, source.* from cats cat, status status, source source Left OUTER JOIN source source2 on source2.sourceid = 1 Right OUTER JOIN status status2 on status2.isStray =0 order by cat.name will yield incorrect results. What I am expecting is a table that looks like the following however I cannot seem to come up with the correct SQL. NAME AGE LENGTH STATUSID CATSOURCE ISSTRAY SOURCEID CATID Adam 1 25 null null null 1 2 Bill 5 1 null null null null null Charles 7 5 null null null null null Steve 12 15 1 1 1 1 1 In plain English what I am looking for is to return all known cats + their associated cat source + their cat status while retaining null values. The only information I will have is the source that I am curious about. I also only want the cats that have a status of either STRAY or UNKNOWN (null)

  Read the article

• How to set a filter for an estimated maximum price

  - by David

  I cannot figure out how to set an estimated maximum price for a collection of records. What I want to avoid is to simply use SQL MAX, because maybe there are records with exorbitant prices. For example, in the "computers-hardware" category of OLX (http://www.olx.com/computers-hardware-cat-240) the filter for maximum price is estimately set to $1400, but sorting by price, the first items are above $10000 Maybe they calculated the average and then estimated some maximum price... what do you think? And what about the stepping? How would you calculate it?

  Read the article

• How to position columns in select list based on a variable.

  - by Rohit

  I am creating a dynamic grid which is created by selecting the format defined in the below XML.My select list includes all columns in this XML,I want to position these columns columns on the basic of position attribute in XML. <gridFormat> <column property="CustomerID" dbName="CustName" HeaderText="Customer" IsVisible="0" Position="1" /> <column property="CustomerName" dbName="FacilityName" HeaderText="Facility" IsVisible="1" Position="3" /> <column property="FacilityInternalID" dbName="Pname" HeaderText="Patient" IsVisible="1" Position="2" /> </gridFormat> I cannot select them in the order specified by the position attribute. I can do it in C# by looping around the returned datatable and specifically position columns using datatable.columns.setordinal() method. Is there any better way in SQL or C# to accomplish this.

  Read the article

• T-SQL Better way to determine max of date (accounting for nulls)

  - by Josh

  I am comparing two dates and trying to determine the max of the two dates. A null date would be considered less than a valid date. I am using the following case statement, which works - but feels very inefficient and clunky. Is there a better way? update @TEMP_EARNED set nextearn = case when lastoccurrence is null and lastearned is null then null when lastoccurrence is null then lastearned when lastearned is null then lastoccurrence when lastoccurrence > lastearned then lastoccurrence else lastearned end; (This is in MS SQL 2000, FYI.)

  Read the article

• Printing All Entries in A PHP Table

  - by mgunawan

  I'm trying to insert a php excerpt with SQL (I understand this is outdated, but am trying to grasp the syntax first) into my HTML page, and I've got the following table: ID Name Element1 Element2 0 John John's 1st John's 2nd 1 Bill Bill's 1st Bill's 2nd 2 Steve Steven's 1st Steve's 2nd I'm trying to get the for loop that will essentially print out the following in my html page Name: Name where ID=0 Element1: Element1 where ID=0 Element2: Element2 where ID=0 Name: Name where ID=1 Element1: Element1 where ID=1 Element2: Element2 where ID=1 and so forth. Basically, I am trying to make this process automated so that whenever a new record is added into the table, the HTML page will automatically update with a new "profile". Thank you for your help!

  Read the article

• Data in column not changed

  - by shanks

  I have sql 2005 and when i run below query, data from RealTimeLog table transfer to History but when new data come in RealTimeLog table old data not changed by new one means OutTime data is not changed with new data from RealTimeLog. insert into History (UserID,UserName,LogDate, [InTime], [OutTime]) SELECT UserID,UserName,[LogDate],CONVERT(nvarchar,MIN(CONVERT(datetime, [LogTime], 108)), 108), CONVERT(nvarchar, MAX(CONVERT(datetime, [LogTime], 108)), 108) From RealTimeLog where not Exists (select * from History H Where H.UserID = RealTimeLog.UserID AND H.UserName=RealTimeLog.UserName AND H.LogDate=RealTimeLog.LogDate) GROUP BY UserID,UserName,[LogDate] ORDER BY UserID,[LogDate] for ex. 1 Shanks 02/05/2010 9:00 10:00 if new Max time generated suppose 11:00 in RealtimeLog then it is not inserted in History table and output remain same as above.

  Read the article

• Duplicate a database record with linq

  - by holz

  Is there a way to duplicate a db record with linq to sql in c#? Id [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Foo] [nvarchar](255) NOT NULL, [Bar] [numeric](28,12) NOT NULL, ... Given the table above, I would like to duplicate a record (but give it a different id), in a way that new fields added to the DB and the Linq dbml file at a later date will still get duplicated with out having to change that code that duplicates the record. ie I don't want to write newRecord.Foo = currentRecord.Foo; for all of the fields on the table.

  Read the article

• How does one SELECT block another?

  - by Krip

  I'm looking at output of SP_WhoIsActive on SQL Server 2005, and it's telling me one session is blocking another - fine. However they both are running a SELECT. How does one SELECT block another? Shouldn't they both be acquiring shared locks (which are compatible with one another)? Some more details: Neither session has an open transaction count - so they are stand-alone. The queries join a view with a table. They are complex queries which join lots of tables and results in 10,000 or so reads. Any insight much appreciated.

  Read the article

• Optimising (My)SQL Query

  - by Simon

  I usually use ORM instead of SQL and I am slightly out of touch on the different JOINs... SELECT `order_invoice`.*, `client`.*, `order_product`.*, SUM(product.cost) as net FROM `order_invoice` LEFT JOIN `client` ON order_invoice.client_id = client.client_id LEFT JOIN `order_product` ON order_invoice.invoice_id = order_product.invoice_id LEFT JOIN `product` ON order_product.product_id = product.product_id WHERE (order_invoice.date_created >= '2009-01-01') AND (order_invoice.date_created <= '2009-02-01') GROUP BY `order_invoice`.`invoice_id` The tables/ columns are logically names... it's an shop type application... the query works... it's just very very slow... I use the Zend Framework and would usually use Zend_Db_Table_Row::find(Parent|Dependent)Row(set)('TableClass') but I have to make lots of joins and I thought it'll improve performance by doing it all in one query instead of hundreds... Can I improve the above query by using more appropriate JOINs or a different implementation? Many thanks.

  Read the article

• C# Dynamic Query Without A Database Model

  - by hitopp

  I have been searching the web for a solution to dynamic queries. I have found many different solutions (e.g. Linq to Sql, Dynamic Linq Expressions, Dynamic Query), but all of these solutions involve some sort of previous knowledge of the database (like a model in code). Maybe what I am asking is way off the deep end, but is there any possible way to dynamically query a database without a model? For example, a database has a Customers table with the following columns: CustomerID Name FavoriteColor I want to create a query as SELECT Name FROM Customers WHERE @0 = @1, where the two placeholders are populated dynamically. The resulting data does not tie to a model class and I would prefer to use some sort of framework to build the queries, not simple string concatenation. The System.Linq.Dynamic namespace came really close to fulfilling this request, but it uses a database model. I realize this is crazy, but I was just curious.

  Read the article

• entity framework and dirty reads

  - by bryanjonker

  I have Entity Framework (.NET 4.0) going against SQL Server 2008. The database is (theoretically) getting updated during business hours -- delete, then insert, all through a transaction. Practically, it's not going to happen that often. But, I need to make sure I can always read data in the database. The application I'm writing will never do any types of writes to the data -- read-only. If I do a dirty read, I can always access the data; the worst that happens is I get old data (which is acceptable). However, can I tell Entity Framework to always use dirty reads? Are there performance or data integrity issues I need to worry about if I set up EF this way? Or should I take a step back and see about rewriting the process that's doing the delete/insert process?

  Read the article

• Exclusive filtering by tag

  - by KaptajnKold

  I'm using rails 3.0 and MySql 5.1 I have these three models: Question, Tag and QuestionTag. Tag has a column called name. Question has many Tags through QuestionTags and vice versa. Suppose I have n tag names. How do I find only the questions that have all n tags, identified by tag name. And how do I do it in a single query. (If you can convince me that doing it in more than one query is optimal, I'll be open to that) A pure rails 3 solution would be preferred, but I am not adverse to a pure SQL solution either.

  Read the article

• How to properly reserve identity values for usage in a database?

  - by esac

  We have some code in which we need to maintain our own identity (PK) column in SQL. We have a table in which we bulk insert data, but we add data to related tables before the bulk insert is done, thus we can not use an IDENTITY column and find out the value up front. The current code is selecting the MAX value of the field and incrementing it by 1. Although there is a highly unlikely chance that two instances of our application will be running at the same time, it is still not thread-safe (not to mention that it goes to the database everytime). I am using the ADO.net entity model. How would I go about 'reserving' a range of id's to use, and when that range runs out, grab a new block to use, and guarantee that the same range will not be used.

  Read the article

• Intime and OutTime for the Modified date

  - by Jash

  Question is already posted on June 4, but still am not get the Proper answer Again Table Structure: T_Person – Table 1 CARDNO 168 471 488 247 519 518 331 240 518 386 441 331 T_Cardevent – Table 2 CARDEVENTDATE CARDEVENTTIME 20090225 163932 20090225 164630 20090225 165027 20090225 165137 20090225 165147 20090225 165715 20090225 165749 20090303 162059 20090303 162723 20090303 155029 20090303 155707 20090303 162824 Query SELECT CARDNO, CARDEVENTDATE, (1000000 * CAST (CARDEVENTDATE AS BIGINT) + CAST (CARDEVENTTIME AS BIGINT) - 30001) / 1000000 AS CardEvenDateAdjusted, CARDEVENTTIME FROM T_CARDEVENT WHERE (CARDEVENTDATE > 20090601) GROUP BY CARDNO, CARDEVENTDATE, CARDEVENTTIME, (1000000 * CAST(CARDEVENTDATE AS BIGINT) + CAST(CARDEVENTTIME AS BIGINT) - 30001) / 1000000 ORDER BY CARDNO, CARDEVENDATEADJUSTED From this above query date is displaying correctly according to that time 03:00:01 to 03:00:00 How can I get min (time) and Max (time) for the adjusted date? I need the sql query for the above condition. Help me? Urgent Please

  Read the article

• Microsoft Sync Framework - How to reprovision a table (or entire scope) after schema changes?

  - by Rabbi

  I have already setup syncing with Microsoft Sync Framework, and now I need to add fields to a table. How do I re-provision the databases? The setup is exceedingly simple: Two SQL Express 2008 servers The scope includes the entire database Using Microsoft Sync Framework 2.0 Synchronizing by direct access. Using the standard new SqlSyncProvider Do I make the structural changes at both ends? Or do I only change one server and let Sync Framework somehow propagate the change? Do I need to delete the _tracking tables and/or the stored procedures? How about the triggers? Has anyone been using the Sync Framework? Please help.

  Read the article

• C# - Inserting multiple rows using a stored procedure

  - by user177883

  I have a list of objects, this list contains about 4 million objects. there is a stored proc that takes objects attributes as params , make some lookups and insert them into tables. what s the most efficient way to insert this 4 million objects to db? How i do : -- connect to sql - SQLConnection ... foreach(var item in listofobjects) { SQLCommand sc = ... // assign params sc.ExecuteQuery(); } THis has been really slow. is there a better way to do this?

  Read the article

• Using multiple databases within one application

  - by Alex

  I have a web application made for several groups of people not connected with each other. Instead of using one database for all of them, I'm thinking about making separate databases. This will improve the speed of the queries and make me free from checking to what group the user belongs. But since I'm working with LINQ to SQL, my classes are explicitly connected with the databases, so I will have to make separate DataContexts for all of the databases. So how can I solve this problem? Or should I just not bother and use one database only?

  Read the article

• ms-access: missing operator in query expression

  - by every_answer_gets_a_point

  i have this sql statement in access: SELECT * FROM (SELECT [Occurrence Number], [1 0 Preanalytical (Before Testing)], NULL, NULL,NULL FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalytical (Before Testing)] IS NULL) UNION SELECT [Occurrence Number], NULL, [2 0 Analytical (Testing Phase)], NULL,NULL FROM [Lab Occurrence Form] WHERE NOT ([2 0 Analytical (Testing Phase)] IS NULL) UNION SELECT [Occurrence Number], NULL, NULL, [3 0 Postanalytical ( After Testing)],NULL FROM [Lab Occurrence Form] WHERE NOT ([3 0 Postanalytical ( After Testing)] IS NULL) UNION SELECT [Occurrence Number], NULL, NULL,NULL [4 0 Other] FROM [Lab Occurrence Form] WHERE NOT ([4 0 Other] IS NULL) ) AS mySubQuery ORDER BY mySubQuery.[Occurrence Number]; everything was fine until i added the last line: SELECT [Occurrence Number], NULL, NULL,NULL [4 0 Other] FROM [Lab Occurrence Form] WHERE NOT ([4 0 Other] IS NULL) i get this error: syntax error (missing operator) in query expression 'NULL [4 0 Other]' anyone have any clues why i am getting this error?

  Read the article

• vs2010 Cache SQL data incorrect fields

  - by mickartz

  OK, I found a walkthrough on msdn for what I was after (offline database cache). However when I let the wizard create a local database from my online sql server the timespan fields are converted to a string?? Now I know the suggestion was to create my own local database and then use the MS Synch framework...however...this proclaims to do it "out of the box" However now I've a dataset which I've no idea how to use, and a database newly formed (for the synched cache) taht I will have to use Ling to Entities with(??) meanwhile I have this weird timespan to string conversion? should I give up now or push on? can i overwrite the the .designer.cs? typeof(string) to typeof(timespan)? damn wizards!!

  Read the article

< Previous Page | 538 539 540 541 542 543 544 545 546 547 548 549  | Next Page >