Different between Oracle's plus notation over ansi join notation?
What's the difference between using oracle's plus notation over the ansi standard 'join' notation? Is there a difference in performance? Is the plus notation deprecated? Thanks ...
sql
Is it possible to update parentID as part of a move opperation without using temporary columns?
The problem I have is that I have an old database where there typicaly are a lot of tables with internal parent/child relationship. These tables are getting migrated to a newer data structure, but we want to keep the old relationship. Today I do this as follow: Old table is tbl_contract. PK: contract_pk, FK: contract_parent_id, contrac...
Subsonic 2: object brings back multiple database tables info
hi i ask subsonic for a table in my database it returs the table and all other tables connected threw foreign keys, cant i stop it bringing back all the extra table info?? here is my code: [WebMethod] [ScriptMethod] public List<DealEquipment> GetDealEquipment(Guid DealID) { List<DealEquipment> dealEquipmentList = new List<DealEqu...
howto use bcp_bind to insert dateTime values in sql server (2008) ?
Hi, I've a sample table, and it has cols of a few types including datetime. I'm trying to test bulk insert into it using the bcp_* api. I encounter a problem when trying to insert datetime values into the DB (error msgs include 'invalid date time format' when I try to insert as text or 'numeric value out of range' when trying to use ...
call a sql with a compute clause in C#
Does anybody know how to call a sql with a compute clause in C#? I got internal error 30016. my Database is sybase 12.5.4. ...
my mysql index is not having any effect
I have the following query: mysql> explain SELECT Exhibition.venue_id, Exhibition.name, Exhibition.slug, Venue.name, Venue.slug, Venue.location_id, Venue.id, Exhibition.id FROM exhibitions AS Exhibition LEFT JOIN venues AS Venue ON (Exhibition.venue_id = Venue.id) LEFT JOIN temperatures AS Temperature ON (Temperature.ref = Exhibition.id...
Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql
Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi. If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list? That is, (2, 3), (7, 9), ...? For example, suppose we h...
Get primary key of aggregated, group by SQL query using PostgreSQL
Hi, I'm struggling with creating a SQL query involving aggregates using PostgreSQL. Consider the following tables: CREATE TABLE thing ( id INT NOT NULL PRIMARY KEY, price NUMERIC(10,2) NOT NULL, description VARCHAR(255) NOT NULL, url VARCHAR(255) NOT NULL, location_id INT NOT NULL REFERENCES location(id) ) CREATE TABLE locat...
Order of (nolock) and table aliases on SQL Server
So I've got a SQL statement that pared down looks something like this: SELECT column FROM table t (nolock) LEFT OUTER JOIN table2 (nolock) t2 on t.id = t2.id This statement works on my SQL 2005 and SQL 2008 environments. It does not on a remote SQL 2005 environment. I've switched the last line to: LEFT OUTER JOIN table2 t2 (nolock) o...
Use APPLY with an UPDATE statement as the row source
I tried to perform the following in order to update a psuedo-identity value at the same time as using the value to create new rows, but APPLY does not like UPDATE statements as the right table source. What's the most elegant alternative outside of simply using an identity column? create table Temp1( id int not null identity(1,1) pr...
SQL Performance: UNION or ORDER BY
The problem: we have a very complex search query. If its result yields too few rows we expand the result by UNIONing the query with a less strict version of the same query. We are discussing wether a different approach would be faster and/or better in quality. Instead of UNIONing we would create a custom sql function which would return ...
Type II dimension joins
I have the following table lookup table in OLTP CREATE TABLE TransactionState ( TransactionStateId INT IDENTITY (1, 1) NOT NULL, TransactionStateName VarChar (100) ) When this comes into my OLAP, I change the structure as follows: CREATE TABLE TransactionState ( TransactionStateId INT NOT NULL, /* not an IDENTITY column i...
CREATE TABLE reverse engineering in Oracle
In Oracle SQL Developer, there's a "SQL" tab for each table. This tab contains most of the SQL code (CREATE TABLE, CREATE TRIGGER, etc) that's needed to recreate the table. Is this information available programatically from the database system, or is this an application feature of SQL Developer? If the former, what commands/statements ...
Dependency in identity column values after multiple SQL inserts
If the next is right: There are SQL string with multiple inserts (using a stored procedure): "EXEC SPInsertData ... EXEC SPInsertData ... EXEC SPInsertData ..." The id in identity column, which is auto incremented, of every new record is smaller than the id of the next one. E.g. after executing the given SQL string the id of the first...
java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-01438: value larger than specified precision allows for this column
I'm getting this error message in production. Unfortunately this comes from a purchased application and the support process is not precisely fast. This happens when data is being copied from one table to another. Both tables are supposed to have the same columns types and length. So far I have reviewed a some of them by doing the fol...
How do I write this SQL query?
I have a table that records a history of child items linked to a parent, with two columns: ParentId ChildId Some example data looks like this: ParentId -- ChildId 001 -- 001 001 -- 001 001 -- 001 001 -- 002 001 -- 002 001 -- 002 001 -- 003 001 -- 003 001 -- 003 001 -- 003 001 --...
To improve SQL-queries in DDL
Improvements done nvarchar(5000) -> nvarchar(4000) BUT no nvarchar in PostgreSQL => TEXT memory limits to some variables the syntax slightly changed to more readable dashes to underscores Magnus' improvements I am following my plan for my first database project. I would like to know any weaknesses in the queries and in the relationa...
How to deal with concurrent updates in databases ?
What's the common way to deal with concurrent updates in an SQL database ? Consider a simple SQL schema(constraints and defaults not shown..) like create table credits ( int id, int creds, int user_id ); The intent is to store some kind of credits for a user, e.g. something like stackoverflow's reputation. How to deal with con...
Is there any ISO standard of how to write SQL queries in DDL?
I am doing my first database project. I would like to know which of the following ways should I use to make SQL queries in DDL. #1 CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, dateofbirth DATE NULL ); #2 CREATE TABLE employee...
Interesting SQL quiz
Hi! Recently I came across with the following quiz. Imagine we have this table +--------+ | colors | +--------+ | red | | black | | white | | green | | orange | +--------+ The task is to write a SQL query that will select all pairs without allowing duplicates. Permutations are counted too ({red, black} = {black, red}, hence onl...