sql

SQL Query - Copy Values in Same Table

So I have a table that looks like this: ProjectID TaskID Date 000 001 10/28/09 000 002 12/1/09 000 003 02/24/10 001 001 04/14/10 001 002 07/5/10 001 003 02/24/11 002 001 06/07/10 002 002 07/7/10 002 ...

Adding a Column Programmatically to a SQL Server database

I've taken over an ASP.NET application that needs to be re-written. The core functionality of this application that I need to replicate modifies a SQL Server database that is accessed via ODBC from third party software. The third-party application creates files that represent printer labels, generated by a user. These label files dire...

SQL Conditional on Bit

Why does this.. DECLARE @SkyBlue Bit SET @SkyBlue = 1 IF @SkyBlue Select 'the sky is blue!' ELSE Select 'the sky is not blue!' Produce this "An expression of non-boolean type specified in a context where a condition is expected, near 'Select'." And is there a Boolean type in SQL2008? ...

SQL to find articles with ALL of a set of tags

Finding articles with ANY of a set of tags is a relatively simple join, and has been discussed already: http://stackoverflow.com/questions/900337/best-db-mysql-structure-articles-which-contain-favored-tags But what if I'm searching, and want to find articles with ALL of a set of tags? For specificity, assume the following tables: CREA...

(PostgreSQL) random number range 1 - 10

Hi all, since my approach for a test query which I worked on in this question did not work out I'm trying something else now. Is there a wayto tell pg's random() function to getme only numbers between 1 and 10? thx for your time K ...

Hyper-V snapshot backup strategy appropriate for production SQL server?

So SQL Server 2008 in Hyper-V is a supported configuration, and should perform well as long as you use fixed or pass-through disks and increase your processor/memory settings appropriately. My question is can I use snapshots as a reliable backup mechanism, or should I use the tried and trusted maintenance plan to do my backups? ...

SQL Server Database - Hidden Fields?!

Hello! I'm implementing CRUD on my silverlight application, however I don't want to implement the Delete functionality in the traditional way, instead I'd like to set the data to be hidden instead inside the database. Does anyone know of a way of doing this with an SQL Server Database? Help greatly appreciated. ...

Creating SQL UPDATE statements on the fly

I am in the midst of updating data in multiple tables. Currently I have a table that has one field, "sources", that is just a list of all tables that include the field "itemid". I also have a table that has 2 fields, "itemid" and "olditemid". In TSQL, I would like to iterate through the sources and create the update statements on the ...

Prevent Typed DataSet updating PK column

I have a typed dataset, and where I call TableAdapter.Update(DataRow), the SQL executed includes an update of the primary key column of the database. Unfortunately, when this code runs on a replicated database, the PK column has the rowguid property set, and I get the following error: System.Data.SqlClient.SqlException: Updating col...

Weird OpenEdge query behaviour

We have recently had to do some work with an OpenEdge database that a third party product uses, and today (after much hair-pulling), we finally identified why a view was returning no results. This view in question combines about 100 separate tables, and is then queried against (we have limited rights to this database). One of the fields ...

smo doesnot display installed instances in w2k8 sql2k8 cluster????

Hi I have cretaed an application via COM interoperability(using SMO and created a dll consume it in c++) which retrieves the local instances of sqlserver(basically try for network instances and from that trace out for local to make it as work for remote discovery of sqlinstances). it is worked in all cases except win2k8sql2k8 cluster i...

Merging identical tables but maintaining separate referential integrity

Consider a dimensional model with fact tables like (fk_dim1value, fk_dim2value, ..., value) where the fk_X columns are foreign keys into corresponding trivial dimension tables dim1value (id, value), dim2value (id, value), etc. These fact-and-dimension tables are collected automatically from disparate sources, so there's a lot of them .....

Can't create foreign key

I'm trying to add a foreign key to an existing table, and was having issues. I figured that I had an error in my syntax, so I updated my hibernate.cfg.xml file to auto-update. As it turns out, hibernate had the same error. Here's my SQL to add the foreign key: alter table pbi add index FKEA3F7BDE9BAB051 (FK_idP), add constraint FKEA3...

PHP/MySQL - SELECT various tables, with some row name

Hi, Which is the best aproach to select * from various tables but with same row name, cause normally i use $row['name'] but i have seven tables with the some name... how can i do this? tkz Roberto ...

Replicate LEFT OUTER JOIN in WHERE clause

How can I replicate a LEFT OUTER JOIN in a WHERE clause? Essentially, I want to use a cross rather than a join in the FROM clause and then filter the second table accordingly: SELECT A.KEY, B.KEY FROM A, B WHERE ?????; Note the key will not be null in either case and will be unique. ...

Oracle text indices on top of a materialized view

Hi I have a materialized view of a complex join, which I would like to have some of its columns be searcheable using Oracle Text. I've already created the indicies on the underlying table. Do you I have to create new indicies again for this materialized view? thank you, Joyce ...

Oracle Multiple Overlapping Between Statements

Will Oracle flatten multiple between clauses if they have overlapping data? In my application, users can create dynamic search terms, so it's possible that there could be overlapping data. Will Oracle optimize the SQL for me or do I have to calculate it before I create the SQL? i.e. Select id from xxtable WHERE id (BETWEEN 10 AND 20)...

Why is my query so slow? Trying to find the null fields on a left join in mysql

EDIT: Updated with suggestions from Bill Karwin below. Still very slow. I'm trying to write a query that will find all items on an order that are entered to a warehouse that doesn't have a record for that item in that warehouse. As an example, if item XYZ is entered for warehouse A, but warehouse A doesn't actually carry item XYZ, I w...

Arrays in Oracle SQL

Here's a simplified pseudo-code version of what I'd like to be able to do in PL-SQL (Oracle): DECLARE mylist as ARRAY BEGIN mylist (1) := '1' mylist (2) := '3' ... SELECT * FROM aTable WHERE aKey IN mylist; END; The SELECT should return the matching records for mylist(1), mylist(2) etc. It should be similar to ORing all ...

SQL Filter criteria in join criteria or where clause which is more efficient

I have a relatively simple query joining two tables. The "Where" criteria can be expressed either in the join criteria or as a where clause. I'm wondering which is more efficient. Query is to find max sales for a salesman from the beginning of time until they were promoted. Case 1 select salesman.salesmanid, max(sales.quantity) from...