sql

Create Unqiue case-insensitive constraint on two varchar fields

In Oracle 10g, how do I add a unique case-insensitive constraint on two varchar fields? For example, given the following records already in the table: "Stephen", "Swensen" "John", "Smith" The following inserts would be invalid: "stephen", "Swensen" "John", "smith" "stephen", "swensen" But the following inserts would be valid: "St...

postgres spatial indexing

I can't seem to find much documentation on this. What's the simplest way to create a database/table on postgres supporting a query like this SELECT * FROM table WHERE distance(POINT(0,0), table.location) <= 1000m; Where POINT(0,0) and table.location should be latitude/longitude pair, and 1000m is 1000 meters. And how should I go about i...

Adding Excel Spreadsheet to SQL Database

How can I import an excel file into my SQL database? I have two options, MSSQL or MySQL. Thank you ...

SQL Server stored procedure error while executing from php

I am trying to execute a stored procedure from php. In the php code iam sending a integer parameter to the stored procedure $orderId =824; $result =mssql_bind($sp, "@orderID", $orderId, SQLINT1, true, false); I getting an error mssql_execute() [function.mssql-execute]: message: The formal parameter "@orderID" was not declared as an...

Join + Count per row in postgresql

Current "Attempts" table: ID QUESTION_ID CORRECT 1 1 FALSE 2 2 TRUE 3 4 FALSE 4 3 FALSE 5 1 TRUE 6 1 TRUE 7 4 TRUE 8 3 TRUE 9 4 FALSE 10 1 TRUE 11 2 TRUE 11 1 FALSE 11...

Increment special chapter on index (book index style - 1.2.3.2) by sql

Hi I have a column holding a varchar2 value in book index style - means something like 1.2.3.2.1 I need a to incement a special chapter for recursive select. I want to match 1.2.1 with 1.3.1 and 1.2.2 with 1.3.2 and so on. I am wondering if this can be done without making a pl/sql function. I have tried to do this by regexp but no suc...

Best practice: best database naming convention for JPA?

In Java the naming convention for properties en classes (entities) are done the CamelCase way: @Entity public class UserMessage implements Serializable { @Id private Integer id; private String shortTitle; private String longTitle; private String htmlMessage; } But in the SQL world it’s considered a best pract...

TSQL - SET vs. SELECT when assigning variables?

What are the differences between SET vs. SELECT statement when assigning variables in T-SQL? ...

Developer: how to get reporting data from about 20 databases?

I'm a junior developer in our team. In one project we have about twenty databases in one SQL Server instance. We have db_owner rights to these databases. My intention is to monitor certain things from these databases (e.g. file size). But because we don't have sysadmin rights, we don't have all those management tools for these database...

SQLMetal generates code from stored procedures incorrectly.

sqlmetal wrongly generates code for my sproc as ISingleResult. I need multiple results! Check this out. SqlMetal actually generates ISingleResult for a sproc like this: CREATE PROCEDURE GetDetailReportData AS BEGIN select * from Log END How do you get sqlmetal to generate with multiple results for a sproc? Does anyone ever use sproc...

How to display all fields regardless of match?

I have two tables, ProblemChildren and DailyTable. ProblemChildren consists of an ID and a name. DailyTable consists of a ID, name, and date_hit. Right now, I'm displaying all ProblemChildren.name where it exists in DailyTable. So if the name "Mike" in ProblemChildren does not have a record in DailyTable, then his name is not outpu...

Why is executemany slow in Python MySQLdb?

I am developing a program in Python that accesses a MySQL database using MySQLdb. In certain situations, I have to run an INSERT or REPLACE command on many rows. I am currently doing it like this: db.execute("REPLACE INTO " + table + " (" + ",".join(cols) + ") VALUES" + ",".join(["(" + ",".join(["%s"] * len(cols)) + ")"] * len(data)...

SQL statement takes over 8 hours to run

I am doing a query from an SQL database. The table has over a million records in it. This is my SQL statement. It takes over 8 hours to run. Anyone have any ideas? Thanks in advance. Select Count (userID) as DIBWIZHits, Sum (ssnCount) as SSNs From tbl_hits10 Where (appName='DIBwiz QMT' or appName like 'DIBwiz-Full%' or ...

How do I count how many of these columns have a value in SQL?

Assume the following table: ID | Item1 | Item2 | Item3 | Item4 | Item5 ------------------------------------------ A | NULL | NULL | YES | YES | NULL B | NULL | NULL | NULL | YES | NULL C | NULL | NULL | NULL | NULL | NULL I want to return the following data set: ID | Count ------------ A | 2 B | 1 C | 0 Bas...

Is it possible for an SQL table to have zero columns?

I'm using the following query to gather information about a table's columns: SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, Is_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' ORDER BY ORDINAL_POSITION If this query returns zero results, can I s...

Should I switch from Web Services to Entities to do my SQL data manipulation?

I have a number of web application projects in a .net environment that use web services created by my DBA to select, update, and delete data from my SQL database. My DBA suggests that he will continue to create new web service interfaces for new projects in the future. I understand that this process is somewhat inefficient in terms of ...

In a Many to Many relationship, how can records collectively owned be selected.

I'm sure I'm missing the correct terminology here, but for this example many-to-many relationship: A user has many books. A book is owned by many users. What's the best way to see all books owned by both of two users? Right now I'm just using this: SELECT * FROM books WHERE book_id IN (SELECT book_id FROM user_has_book WHER...

SqlBulkCopy fails trying to copy a row with large content in an XML column

I am trying to copy a record from one SQL Server table to another. Both tables have the same structure, one of the columns is of type xml. Source table has a large xml content in of the rows, about 2.5Mb. I saved the content of the xml column into a file, see map.zip attached or download it from https://docs.google.com/leaf?id=0Bz4PXXE...

SQL Server - Query Help

I have the following 3 tables Teams (TeamID int, TeamName varchar(200)) - TeamID is identity col Users (UserID int, Username varchar(200)) - UserID is identity col UsersTeams (ID int, UserID int, TeamID int) - Table has FK references to the 2 tables above The table UsersTeams is used to keep a record of all teams a user is a member of...

Using LIKE operator in Access works but not in Java

I'm making a java application for a toy store and using the MS-Access database. I'm using the operator "LIKE" to search for products in database using the user's keyword. E.g. in Access: SELECT * FROM tblToy WHERE toyName LIKE '*puppy*' OR toyBrand LIKE '*puppy*' this gives me the desired result in access. But in java when i run thi...