MySQL: Is there a way to reset a table?
Is there a way to reset a table? I mean not only deleting all rows, but starting the auto_incrementing ID back to 0? ...
sql
Combine varchar column with int column
I have two columns in a SQL table, fooId (int) and fooName (varchar). Is there a way to select them both as one column with a space between them? select fooId + ' ' + fooName as fooEntity from mytable They're different types so I'm getting an error. This field will be databound directly in a control in the web app. SQL Server 200...
Is it possible to retrieve a raw query from Oracle OCI library?
I am looking to retrieve the raw query bytes from an Oracle statement and copy them directly into a POD C structure. The reason for this is due to legacy code that must be ported to using Oracle from Informix. I'll illustrate what I basically need below. I have been looking into using the Oracle Template Library (OTL) but I have been una...
SQL stored proc - help me write this one, please!
Hi Guys, Could you please help me with this one: In one of my scripts I am inserting some data in to some table "Item". One of the columns in the "Item" table is "ItemNumber". I want to be able to call some function (most probably a Stored proc) where it would return me a numeric number which I can use for the ItemNumber. I CAN NOT ...
sql query for sales summary again
this question is based on answer got from another SO question, can be found here. I have managed to write a query myself based answer provided there Select s.pName, s.ProductCode, min(s.Price) as MinPrice, sum(s.Quantity) as SalesQty, sum(s.Price * s.Quanti...
When is it not appropriate to use Derived tables?
This SO post details some benefits in performance regarding Derived vs. Temporary tables. Other than performance, what situations exist for which a Derived table would not be appropriate. One answer per post with an example would be helpful. ...
Use a VB variable in a SQL statement
I'm trying to create a sql statement but I require the use of a VB variable. The problem is, I keep getting an error about too few parameters when I try to just put the variable in. Is there some sort of format I need to use in order to add a VB variable into a SQL statement? Set rs = CurrentDb.OpenRecordset("SELECT StartTime " & _ ...
Is there anything like unique_together(max_occurences=3) ?
I have a model: class MyModel(models.Model): a = models.IntegerField() b = models.IntegerField() c = models.IntegerField() Now, I need something like unique_together(a,b, max_occurences=3) constraint to be added to the above model (so that there can be up to 3 values of c for each pair of (a,b), and ideally those 3 val...
What is the syntax to set the auto increment start value in schema.yml with Doctrine?
The title says it all. I want to set the start value for auto increment on a column to 1000000 (to avoid conflicts with a legacy application), but can't find any documentation that tells me how. Pretty standard table, here's what the relevant bit looks like: User: attributes: export: tables columns: id: type: inte...
Eventmachine / Em-http-request / SQL throughput and architecture
I'm trying to create some code to process the twitter spritzer stream, and from the advice I'd found it seem like using ruby's eventmachine/em-http-request is a good way to do it. The basic code is below, and I'm getting ok performance, but I don't believe its able to keep up with the stream (despite running on a fairly well spec'd AWS ...
SQL update/delete trigger not working properly
I have a trigger that is used for auditing inserted, updated and deleted rows. I am having an issue with acquiring the Old Values and New Values from the trigger. The trigger utilizes a loop to insert all values of any row that has been altered in any way (inserted, updated, deleted). However, my code is not returning the values but inst...
How to get two rows value
Using mysql I want to display two rows values in to single value ID Name --------- 01 Raja 02 Ravi Expected output: 01Raja 02Ravi How to make a query for this condition? ...
Possible to concatinate column values into a string?
Say I have the following table: id|myId|Name ------------- 1 | 3 |Bob 2 | 3 |Chet 3 | 3 |Dave 4 | 4 |Jim 5 | 4 |Jose ------------- Is it possible to use a recursive CTE to generate the following output: 3 | Bob, Chet, Date 4 | Jim, Jose I've played around with it a bit but haven't been able to get it working. Would I be bett...
Please will you help tune a 7-table-join mysql count query where tables contain 30,000+ rows?
I have an sql query that counts the number of results for a complex query. The actual select query is very fast when limiting to 20 results, but the count version takes about 4.5 seconds on my current tables after lots of optimizing. If I remove the two joins and where clauses on site tags and gallery tags, the query performs at 1.5 sec...
SQL stored proc - help me write this one, please! (part 2)
I have the following table with the value 501 in it.. CREATE TABLE _Numbers( Number numeric(20,0) NOT NULL PRIMARY KEY ) INSERT INTO _Numbers VALUES(501) How can I write a stored proc on this which returns me 501 and increments Number to next in sequence (i.e. 502)? I would like this behaviour repeated every time the stored proc i...
How can I create a simple Select for a self referential table?
For example, I have this table: CREATE TABLE perarea ( id_area INT primary key, nombre VARCHAR2(200), id_areapadre INT references perarea(id_area) ); Instead of showing: 1 IT null 2 Recursos Humanos null 3 Contabilidad 2 4 Legal 2 I want: 1 IT 2 Recursos Humanos 3 Contabilidad Recursos Humanos 4 Leg...
PhpPgAdmin Syntax error when creating View
I am attempting to create a View in PhpPgAdmin (PostGreSQL db) which has the following SQL statement: DELETE FROM myTable WHERE myTable.error IS NULL; PhpPgAdmin gives me the following error: ERROR: syntax error at or near "DELETE" at character 59 In statement: CREATE OR REPLACE VIEW "Schema1"."Delete empty errors" AS DELETE ...
SQL CE write operation
Hi, I am using MS SQL CE in a .NET desktop application. My application contains multiple threads which write to the database. I am having some issues in the write operations from these multiple threads. I am sure this is due to a synchronization issue and am looking at two solutions - Mutex or separate Process but do not know which one...
Matching BIT to DATETIME in CASE statement
I'm attempting to create a T-SQL case statement to filter a query based on whether a field is NULL or if it contains a value. It would be simple if you could assign NULL or NOT NULL as the result of a case but that doesn't appear possible. Here's the psuedocode: WHERE DateColumn = CASE @BitInput WHEN 0 THEN (all null dates) WHEN ...
Regarding Oracle Stored Procedure not compiling because table does not currently exist
All, I have the following Package Description: CREATE OR REPLACE PACKAGE ashish.PKG_Customer AUTHID CURRENT_USER AS TYPE cursorType IS REF CURSOR; PROCEDURE CreateCustomerTable; PROCEDURE SelectCustomers(o_ResultSet OUT cursorType); END PKG_Customer; and here is the package body: CREATE OR REPLACE PACKAGE BODY ashish.PKG_...