I frequently need to compare SQL procedures to determine what has changed in the newest version. The problem is, everyone has their own style of formatting, and SQL doesn't (usually) care about where one puts their newlines (e.g. where clauses all on one line vs. newline before each AND). This makes it very difficult (especially for lo...
I have a SQL problem I am trying to digest. I am using SQL Server 2005. In a table I have data as such: ID Type 1 A 2 A 3 A 3 B 4 B I need to find all of the IDs that have a Type of both A and B. This is not a homework problem. It's a real work issue I'm trying to resolve. Thanks ...
Hello All, How do you query a nested set model with multiple roots, such trees in the same table? Currently, I added an extra column called the "Root" indicating the ID of the root node for all sub-tree nodes, however, I can't figure out the sql to retrieve them in the proper order I'm referring to this article Normally, the query t...
I'm working with a client who had a SQL Server 2008 converted from a SQL Server 2000 DB and one of the queries has quite dramatically increased in time since it was on SQL Server 2000. However, if I change the compatibility level to 2008 in the DB, the query goes like a rocket (40-50 times faster). The query does use a number of UDFs. ...
This is an issue which keeps coming up for me when using random strings. This is basically the process. Generate random string Check if it already exists in the database If it doesn't use it, else generate another one So how would I do this using PHP? ...
I'm having some trouble updating changes I made to a datatable via a dataadapter. I am getting "Concurrency violation: the UpdateCommand affected 0 of 10 rows" 'Get data Dim Docs_DistributedTable As New DataTable("Docs_Distributed") Dim sql = "SELECT DISTINCT CompanyID, SortKey, OutputFileID, SequenceNo, DeliveredDate, IsDeliveryCodeCou...
The concern with the legacy of the SQL statements is a constant in my head. Especially when SCRUM is used, where the code has no owner, that is, all must be able to repair and maintain each piece. Optimizing SQL procedures usually means converting it into a set-based commands and using special operators. I need tips to keep the code work...
I have a query below that is returning individual sales records with amounts for each order placed for a specific product SKU. How would I go about summing the total amount? The column is "extprice" that I need to sum. Any help would be appreciated, thanks... select partno4pt,orders.orderdate,orders.processdate,orderdetails.qty,o...
I want to set a variable to use in a mysqli query. This doesn't quite work. Prior to mysqli I used to set query calls. I played around with db->multi_query($sql) with no luck. Anyone out there have an idea how to make this work including a set statement? $sql = 'SET @rownum := 0;'; $sql .= 'SELECT @rownum :=@rownum + 1 AS Rank, User...
Hello I need to add a Column "CurrentLifeCycleId" [int] into 73 tables and also add a Foreign Key from this new column to another single table "LifeCycle" Id column...is there a simple way I can do this in a few statements rather than go through each table one by one. The column does not need to start off being NULL as I will delete al...
Can I optimize a Core Data query when searching for matching words in a text? (This question also pertains to the wisdom of custom SQL versus Core Data on an iPhone.) I'm working on a new (iPhone) app that is a handheld reference tool for a scientific database. The main interface is a standard searchable table view and I want as-you-t...
I am using following query to bulk insert from one table to another. INSERT INTO billitems SELECT * FROM billitems_old; I want that if insert fails on any row, it must skip that row and proceed. Is there anything that I can include in the above query to skip errors. ...
I'm creating a simple set of tables to store a number of lists. The first table, describes the lists that I have and second describes items on those lists - the tables are defined like so: CREATE TABLE `listman_list` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `title` varchar(100) NOT NULL, `description` varchar(1000...
In one of my MySQL tables, I have following columns: Skills varchar(80) Industry varchar(40) Address varchar(100) Skills might include text like: C/C++, MS Office, Linux etc. Industry might include text like: Finance, IT, etc. Address contains complete postal address along with city name. There is no separate city column....
Hey, My function won't add any entry to my existing sql database. Any ideas? sqlite3 *database; sqlite3_stmt *compiledStatement; if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { NSString *tmpSQLStatement = [NSString stringWithFormat:@"INSERT INTO data (test) VALUES ('teststring');"]; const c...
My problem is fairly simple. I have table sets that store product sets (more products looking like one on the outside - computer, mouse and keyboard for ex.) it's connected M:N using sets_products table to products table. Each product can have parameters (connected again M:N). I have a procedure, that generates all parameters as string...
I get the below error message. What other compression function(other than SHA1) should use for oracle? Warning: oci_execute() [function.oci-execute]: ORA-00904: "SHA1": invalid identifier in /user_auth_fns.php on line 2 $result = oci_parse($conn, "select * from user where username='$username' and passwd = sha1('$password')"); $r = oc...
I want to create a stored procedure that performs insert or update operation on a column if that column does not contains a value that already exists in database it should allow insert when COUNT(field) = 0 or update when COUNT(field)=0 or 1 And I should know that either of these operation is performed or not. Please solve my problem us...
I was impressed with the nice-looking database diagrams on this web page. Does anybody know what package made them? http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/ ...
Given the table snippet: id | name | age I am trying to form a query that will return 10 people within a certain age range. However, if there are not enough people in that range, I want to extend the range until I can find 10 people. For instance, if I only find 5 people in a range of 30-40, I would find 5 others in a 25-45 range. I...