sql

Django: Generating a blog's active entry list. Is this efficient?

The following query does what I'd like it to do, however, I have no idea if it's efficient. I went through the Django aggregation documentation, threw it together, looked at the query and tilted my head sideways like a confused dog. What the query actually does is get published Entry's "name" and "name_slug" that have one or more approv...

Restoring a Backup to a different Server - User Permissions

I have backed up and restored a MS SQL Server 2005 database to a new server. What is the best way of recreating the login, the users, and the user permissions? On SQL Server 2000's Enterprise Manager I was able to script the logins, script the users and script the user permissions all seperately. I could then run one after the other a...

Persistence solutions for C++ (with a SQL database)?

Hi, I'm wondering what kind of persistence solutions are there for C++ with a SQL database? In addition to doing things with custom SQL (and encapsulating the data access to DAOs or something similar), are there some other (more general) solutions? Like some general libraries or frameworks (something like Hibernate & co for Java and .N...

SQL selection criteria on grouped aggregate

I'm trying to find a simple MySQL statement for the following two problems: I have 4 tables: Employees, Customers, Orders, Products (Each entry in Orders contains a date, a reference one product, a quantity and a reference to a customer, and a reference to an Employee). Now I'm trying to get all customers where the volume of sale (quan...

Is it possible to SELECT multiple constants into multiple resultset rows in SQL?

I know I can "SELECT 5 AS foo" and get the resultset: foo 5 (1 row) ...is there any way to "SELECT 5,6,7 AS foo" and get the resultset: foo 5 6 7 (3 rows) ...I'm well aware that this is not typical DB usage, and any conceivable usage of this is probably better off going w/ a more ordinary techniq...

What is the best way, algorithm, method to difference large lists of data?

I am receiving a large list of current account numbers daily, and storing them in a database. My task is to find added and released accounts from each file. Right now, I have 4 SQL tables, (AccountsCurrent, AccountsNew, AccountsAdded, AccountsRemoved). When I receive a file, I am adding it entirely to AccountsNew. Then running the below ...

shell script to truncate all MySql tables

Hi, I'm looking for a Unix shell script that will truncate all tables in a schema. A similar question was already asked, but I have some additional requirements which makes none of the provided answers satisfactory: Must be a Unix shell script (i.e. no python, perl, PHP) The script must truncate the tables in an order which respects f...

Locking in a SQL Server Stored Procedure

I have a stored procedure that manipulates an integer table field (which represents a sequence number) based on some criteria - the criteria can reset the field back to zero. In a multiuser environment there is a possibility that the field may be referenced by 1 user before it is updated by another user so I would like to prevent this by...

Is this implementation SQL-92 conformant?

Tony Andrews in another question gave an example of: IF p_c_courtesies_cd || p_c_language_cd || v_c_name || v_c_firstname || v_c_function || p_c_phone || p_c_mobile p_c_fax || v_c_email is not null THEN -- Do something END IF; as a clever (if not a tad obscure) alternative to the Oracle COALESCE functi...

Dynamic SQL in production systems

SO has plenty of Q&As about how to accomplish various tasks using dynamic SQL, frequently the responses are accompanied with warnings and disclaimers about the advisability of actually using the approach provided. I've worked in environments ranging from "knowing how to use a cursor is a bad sign" to "isn't sp_executesql neat!". When i...

When should you use stored procedures?

Possible Duplicate: What are the pros and cons to keeping SQL in Stored Procs versus Code When should you use a stored procedure (such as in MySQL) instead of writing OO or procedural code (such as in PHP, Ruby or Python) that may execute simple SQL queries and does other processing but performs the same task? ...

Using ORDER BY on SELECT DISTINCT in TSQL

I am trying to retrieve a list of date strings ordered by date like this... SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate FROM dbo.ProviderProcessGeneratedDate ORDER BY GeneratedDate This orders by the varchar that I converted the dates to. example... 02/01/2008 02/15/2008 02/21/2007 02/23/2007 02/29/2008 ...

How to apply the DRY principle to SQL Statements that Pivot Months

I'm wondering how others handle this situation... and how to apply the Don't Repeat Yourself (DRY) principle to this situation. I find myself constantly PIVOTing or writing CASE statements in T-SQL to present Months as columns. I generally have some fields that will include (1) a date field and (2) a value field. When I present this bac...

SQL Join Not Returning What I Expect

I have a temp table I am creating a query off of in the following format. That contains a record for every CustomerID, Year, and Month for several years. #T Customer | CustomerID | Year | Month ex. Foo | 12345 | 2008 | 12 Foo | 12345 | 2008 | 11 Bar | 11224 | 2007 | 7 When I join this temp table to another table of the followin...

Performance Tuning SQL - How?

How does one performance tune a SQL Query? What tricks/tools/concepts can be used to change the performance of a SQL Query? How can the benefits be Quantified? What does one need to be careful of? What tricks/tools/concepts can be used to change the performance of a SQL Query? Using Indexes? How do they work in practice? Normalis...

ALTER TABLE without locking the table?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked for the duration of the statement. If it's a big table, that means insert or update statements could be locked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process? ...

There are a method to paging using ANSI Sql only?

I know: Firebird FIRST and SKIP; MySql LIMIT; Sql Server ROWNUMBER(); Does someone knows a SQL ANSI way to perform result paging? ...

What the best resource to learn ANSI SQL?

I don't mean "Basic SQL", but strongly the specs and the difference between the specs and the implementations between great databases (like SQL Server, Oracle, etc). ...

Export basic sales data from JD Edwards

JD Edwards is sales and accounting software sold and supported by Oracle. We need to get daily or weekly exports from an affiliate using JD Edwards, which I can then import into our system. That means I don't want any answers that result in some sort of proprietary format. Surely there is an "Export" button, or a way to save reports a...

Assign function result to a table variable

The SQL Server (2000/2005) function gets 'table name' and 'field name' as a parameter and returns results from a dynamic query within the function. The results should be assigned to a Table variable which will be used further in a Stored procedure. How to achieve this? I am getting error: "Only functions and extended stored procedures ...