Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

Page 613/1156 | < Previous Page | 609 610 611 612 613 614 615 616 617 618 619 620  | Next Page >

• How to call a scalar function in a stored procedure

  - by Luke101

  I am wacking y head over the problem with this code. DECLARE @root hierarchyid declare @lastchild hierarchyid SELECT @root = NodeHierarchyID from NodeHierarchy where ID = 1 set @lastchild = getlastchild(@root) it says it does not recognize getlastchild function. What am I doing wrong here

  Read the article

• Django query: Count and Group BY

  - by Tyler Lane

  I have a query that I'm trying to figure the "django" way of doing it: I want to take the last 100 calls from Call. Which is easy: calls = Call.objects.all().order_by('-call_time')[:100] However the next part I can't find the way to do it via django's ORM. I want to get a list of the call_types and the number of calls each one has WITHIN that previous queryset i just did. Normally i would do a query like this: "SELECT COUNT(id),calltype FROM call WHERE id IN ( SELECT id FROM call ORDER BY call_time DESC LIMIT 100 ) GROUP BY calltype;" I can't seem to find the django way of doing this particular query. Here are my 2 models: class Call( models.Model ): call_time = models.DateTimeField( "Call Time", auto_now = False, auto_now_add = False ) description = models.CharField( max_length = 150 ) response = models.CharField( max_length = 50 ) event_num = models.CharField( max_length = 20 ) report_num = models.CharField( max_length = 20 ) address = models.CharField( max_length = 150 ) zip_code = models.CharField( max_length = 10 ) geom = models.PointField(srid=4326) calltype = models.ForeignKey(CallType) objects = models.GeoManager() class CallType( models.Model ): name = models.CharField( max_length = 50 ) description = models.CharField( max_length = 150 ) active = models.BooleanField() time_init = models.DateTimeField( "Date Added", auto_now = False, auto_now_add = True ) objects = models.Manager()

  Read the article

• Self-referencing tables in Linq2Sql

  - by J-Man

  Hi, I've seen a lot of questions on self-referencing tables in Linq2Sql and how to eagerly load all child records for a particular root object. I've implemented a temporary solution by accessing all underlying properties, but you can see that this doesn't do the performance any good. The thing is though, that all records are correlated with each-other using a correlation GUID. Example below: RootElement - Id: 1 - ParentId: null - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement1 - Id: 2 - ParentId: 1 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement2 - Id: 3 - ParentId: 2 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement1 - Id: 4 - ParentId: 2 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD In my case, I do have access to the correlationId, so I can retrieve all of my records by performing the following query: from element in db.Elements where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD' select element; But, of course, I want these elements associated with each other by executing this query: from element in db.Elements where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD' && element.ParentId == null select element; My question is: is it possible to combine the results the first query as some sort of 'caching mechanism' for the query where I get the root element? Thanks for the input. J.

  Read the article

• Querying tables based on other column values

  - by blcArmadillo

  Is there a way to query different databases based on the value of a column in the query? Say for example you have the following columns: id part_id attr_id attr_value_ext attr_value_int You then run a query and if the attr_id is '1' is returns the attr_value_int column but if attr_id is greater than '1' it joins data from another table based on the attr_value_ext.

  Read the article

• Is there a special character in mySql that would return always true in WHERE clauses?

  - by rm.

  Is there a character, say, $, SELECT * FROM Persons WHERE firstName='Peter' AND areaCode=$; such that the statement would return the same as SELECT * FROM Persons WHERE firstName='Peter' i.e. areaCode=$ would always return always true and, thus, effectively “turns of” the criteria areaCode=... I’m writing a VBA code in Excel that fetches some rows based on a number of criteria. The criteria can either be enabled or disabled. A character like $ would make the disabling so much easier.

  Read the article

• Help to choose NoSQL database for project

  - by potapuff

  There is a table: doc_id(integer)-value(integer) Approximate 100k doc_id and 27?? rows. Majority query on this table - searching documents similar to current document: select 10 documents with maximum of (count common to current document value)/(count ov values in document). Nowadays we use PostgreSQL. Table weight (with index) ~1,5 GB. Average query time ~0.5s. Should I transfer all this to NoSQL base, if so, what?

  Read the article

• Embedding generic sql queries into c# program

  - by Pooja Balkundi

  Okay referring to my first question code in the main, I want the user to enter employee name at runtime and then i take this name which user has entered and compare it with the e_name of my emp table , if it exists i want to display all information of that employee , how can I achieve this ? using System; using System.Collections.Generic; using System.Linq; using System.Windows.Forms; using MySql.Data.MySqlClient; namespace ConnectCsharppToMySQL { public class DBConnect { private MySqlConnection connection; private string server; private string database; private string uid; private string password; string name; //Constructor public DBConnect() { Initialize(); } //Initialize values private void Initialize() { server = "localhost"; database = "test"; uid = "root"; password = ""; string connectionString; connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";"; connection = new MySqlConnection(connectionString); } //open connection to database private bool OpenConnection() { try { connection.Open(); return true; } catch (MySqlException ex) { //When handling errors, you can your application's response based //on the error number. //The two most common error numbers when connecting are as follows: //0: Cannot connect to server. //1045: Invalid user name and/or password. switch (ex.Number) { case 0: MessageBox.Show("Cannot connect to server. Contact administrator"); break; case 1045: MessageBox.Show("Invalid username/password, please try again"); break; } return false; } } //Close connection private bool CloseConnection() { try { connection.Close(); return true; } catch (MySqlException ex) { MessageBox.Show(ex.Message); return false; } } //Insert statement public void Insert() { string query = "INSERT INTO emp (e_name, age) VALUES('Pooja R', '21')"; //open connection if (this.OpenConnection() == true) { //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(query, connection); //Execute command cmd.ExecuteNonQuery(); //close connection this.CloseConnection(); } } //Update statement public void Update() { string query = "UPDATE emp SET e_name='Peachy', age='22' WHERE e_name='Pooja R'"; //Open connection if (this.OpenConnection() == true) { //create mysql command MySqlCommand cmd = new MySqlCommand(); //Assign the query using CommandText cmd.CommandText = query; //Assign the connection using Connection cmd.Connection = connection; //Execute query cmd.ExecuteNonQuery(); //close connection this.CloseConnection(); } } //Select statement public List<string>[] Select() { string query = "SELECT * FROM emp where e_name=(/*I WANT USER ENTERED NAME TO GET INSERTED HERE*/)"; //Create a list to store the result List<string>[] list = new List<string>[3]; list[0] = new List<string>(); list[1] = new List<string>(); list[2] = new List<string>(); //Open connection if (this.OpenConnection() == true) { //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { list[0].Add(dataReader["e_id"] + ""); list[1].Add(dataReader["e_name"] + ""); list[2].Add(dataReader["age"] + ""); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return list; } else { return list; } } public static void Main(String[] args) { DBConnect db1 = new DBConnect(); Console.WriteLine("Initializing"); db1.Initialize(); Console.WriteLine("Search :"); Console.WriteLine("Enter the employee name"); db1.name = Console.ReadLine(); db1.Select(); Console.ReadLine(); } } }

  Read the article

• Nullable Integer ? (working with linq)

  - by nCdy

  I've got exception about convert NULL to Int32. I've got a table from database with nullable tinyint [Column(Storage="_StatType", DbType="tinyint NULL")] public StatType : int { get { _StatType; } } (to get C# code just replace variable's type) and after making linq select def StartLinq = linq <#from lpi in _CfgListParIzm where lpi.ID_ListParIzm==drr1 select (lpi.StatType) #> ; StartLinq.ToArray()[0] can't be readed if that is null :-/ mutable STT : int = 0; try { _=int.TryParse(StartLinq.ToArray()[0].ToString(), out STT); } catch { | _ is Exception => () /* I don't care*/ } upper code is very poor trick :( I wont use it.

  Read the article

• MySQL index building performance

  - by Christian

  I tried to build an index over a two columns of a 30,000,000 entry database. I canceled the process after ~60hr as it didn't seem to work. For some reason MySQL takes only 22 mb ram instead of using the RAM fully. Is index building an operation that needs no Ram or is there some way to tell MySQL to use more RAM to be faster?

  Read the article

• I need some help optimizing my database schema

  - by Steffan

  Here's a layout of my data: Heading 1: Sub heading Sub heading Sub heading Sub heading Sub heading Heading 2: Sub heading Sub heading Sub heading Sub heading Sub heading Heading 3: Sub heading Sub heading Sub heading Sub heading Sub heading Heading 4: Sub heading Sub heading Sub heading Sub heading Sub heading Heading 5: Sub heading Sub heading Sub heading Sub heading Sub heading These headings need to have a 'Completion Status' boolean value which gets linked to a user Id. Currently, this is how my table looks: id | userID | field_1 | field_2 | field_3 | field_4 | etc... ----------------------------------------------------------------------- 1 | 1 | 0 | 0 | 1 | 0 | ----------------------------------------------------------------------- 2 | 2 | 1 | 0 | 1 | 1 | Each field represents one Sub Heading. Having this many columns in my table looks awfully inefficient... How can I go about optimizing this? I can't think of any way to neaten it up :/

  Read the article

• Best solution for a comment table for multiple content types

  - by KRTac

  I'm currently designing a comments table for a site I'm building. Users will be able to upload images, link videos and add audio files to the profile. Each of these types of content must be commentable. Now I'm wondering what's the best approach to this. My current options are: 1. to have one big comments table and a link tables for every content type (comments_videos, ...) with comment_id and _id. 2. to have comments separated by the type of content their for. So each type of content would have his own comments table with the comments for that type.

  Read the article

• 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

• Which data type saves more space TINYTEXT or VARCHAR for variable data length in MySQL?

  - by John M.

  I need to store a data into MySQL. Its length is not fixed, it could be 255 or 2 characters long. Should I use TINYTEXT or VARCHAR in order to save space (speed is irrelevant)?

  Read the article

• Problem with interface implementation in partial classes.

  - by Bas

  I have a question regarding a problem with L2S, Autogenerated DataContext and the use of Partial Classes. I have abstracted my datacontext and for every table I use, I'm implementing a class with an interface. In the code below you can see I have the Interface and two partial classes. The first class is just there to make sure the class in the auto-generated datacontext inherets Interface. The other autogenerated class makes sure the method from Interface is implemented. namespace PartialProject.objects { public interface Interface { Interface Instance { get; } } //To make sure the autogenerated code inherits Interface public partial class Class : Interface { } //This is autogenerated public partial class Class { public Class Instance { get { return this.Instance; } } } } Now my problem is that the method implemented in the autogenerated class gives the following error: - Property 'Instance' cannot implement property from interface 'PartialProject.objects.Interface'. Type should be 'PartialProjects.objects.Interface'. <- Any idea how this error can be resolved? Keep in mind that I can't edit anything in the autogenerated code. Thanks in advance!

  Read the article

• Stored Procedures last access time?

  - by Ardman

  Is it possible to find out when a Stored Procedure was last accessed? I tried the following: SELECT * FROM sys.dm_db_index_usage_stats WHERE [database_id] = DB_ID() AND [object_id] = OBJECT_ID('stored procedure name') and it returns a blank resultset.

  Read the article

• SQLCE checksum change when select queries

  - by Patrice Pezillier

  Why when I only do SELECT on a .sdf file, its checksum changes ? How can I avoid that ?

  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

• Representing Sparse Data in PostgreSQL

  - by Chris S

  What's the best way to represent a sparse data matrix in PostgreSQL? The two obvious methods I see are: Store data in a single a table with a separate column for every conceivable feature (potentially millions), but with a default value of NULL for unused features. This is conceptually very simple, but I know that with most RDMS implementations, that this is typically very inefficient, since the NULL values ususually takes up some space. However, I read an article (can't find its link unfortunately) that claimed PG doesn't take up data for NULL values, making it better suited for storing sparse data. Create separate "row" and "column" tables, as well as an intermediate table to link them and store the value for the column at that row. I believe this is the more traditional RDMS solution, but there's more complexity and overhead associated with it. I also found PostgreDynamic, which claims to better support sparse data, but I don't want to switch my entire database server to a PG fork just for this feature. Are there any other solutions? Which one should I use?

  Read the article

• Historical / auditable database

  - by Mark

  Hi all, This question is related to the schema that can be found in one of my other questions here. Basically in my database I store users, locations, sensors amongst other things. All of these things are editable in the system by users, and deletable. However - when an item is edited or deleted I need to store the old data; I need to be able to see what the data was before the change. There are also non-editable items in the database, such as "readings". They are more of a log really. Readings are logged against sensors, because its the reading for a particular sensor. If I generate a report of readings, I need to be able to see what the attributes for a location or sensor was at the time of the reading. Basically I should be able to reconstruct the data for any point in time. Now, I've done this before and got it working well by adding the following columns to each editable table: valid_from valid_to edited_by If valid_to = 9999-12-31 23:59:59 then that's the current record. If valid_to equals valid_from, then the record is deleted. However, I was never happy with the triggers I needed to use to enforce foreign key consistency. I can possibly avoid triggers by using the extension to the "PostgreSQL" database. This provides a column type called "period" which allows you to store a period of time between two dates, and then allows you to do CHECK constraints to prevent overlapping periods. That might be an answer. I am wondering though if there is another way. I've seen people mention using special historical tables, but I don't really like the thought of maintainling 2 tables for almost every 1 table (though it still might be a possibility). Maybe I could cut down my initial implementation to not bother checking the consistency of records that aren't "current" - i.e. only bother to check constraints on records where the valid_to is 9999-12-31 23:59:59. Afterall, the people who use historical tables do not seem to have constraint checks on those tables (for the same reason, you'd need triggers). Does anyone have any thoughts about this? PS - the title also mentions auditable database. In the previous system I mentioned, there is always the edited_by field. This allowed all changes to be tracked so we could always see who changed a record. Not sure how much difference that might make. Thanks.

  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

• MySQL Select problems

  - by John Nuñez

  Table #1: qa_returns_items Table #2: qa_returns_residues I have a long time trying to get this Result: item_code - item_quantity 2 - 1 3 - 2 IF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 11 THEN item_quantity = qa_returns_items.item_quantity - qa_returns_residues.item_quantity ELSEIF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 12 THEN item_quantity = qa_returns_items.item_quantity + qa_returns_residues.item_quantity ELSE show diferendes END IF I tried this Query: select SubQueryAlias.item_code, total_ecuation, SubQueryAlias.item_unitprice, SubQueryAlias.item_unitprice * total_ecuation as item_subtotal, item_discount, (SubQueryAlias.item_unitprice * total_ecuation) - item_discount as item_total from ( select ri.item_code , case status_code when 11 then ri.item_quantity - rr.item_quantity when 12 then ri.item_quantity + rr.item_quantity end as total_ecuation , rr.item_unitprice , rr.item_quantity , rr.item_discount * rr.item_quantity as item_discount from qa_returns_residues rr left join qa_returns_items ri on ri.item_code = rr.item_code WHERE ri.returnlog_code = 1 ) as SubQueryAlias where total_ecuation > 0 GROUP BY (item_code); The query returns this result: item_code - item_quantity 1 - 2 2 - 2

  Read the article

• How can we secure our data from DBA?

  - by KoolKabin

  Hi guys, I have very confidential data in my database. I am trying to secure my data from dba. I am a member of development team. We develop our software and delpoy in a server which has its own dba. We have limited control over the server. In this scenario how can i deny dba of the server to lookup my data and deny making changes to them. Is it possible?

  Read the article

• What are the benefits of left outer join vs nested aggregate selects to find the newest rows in a table?

  - by RenderIn

  I'm doing: select * from mytable y where y.year = (select max(yi.year) from mytable yi where yi.person = y.person) Is that better or worse from a performance aspect than: select y.* from mytable y left outer join mytable y2 on y.year < y2.year and y.person = y2.person where y2.year is null The explain plan/anecdotal evidence is inconclusive so I am wondering if in general one is better than the other.

  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

• 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

< Previous Page | 609 610 611 612 613 614 615 616 617 618 619 620  | Next Page >