sql

How Can I Reference Multiple Primary Keys For A Vector Type Primary Key

Hi, I have the following scenario: a table of projects and a table of persons, working on one or serveral projects. Also, I have a project id column (of type int), in the first table, which is a primary key there and I have a vector of project ids, as a column of type int, in my second table (persons), that references primary keys from t...

SQL statement HAVING MAX(some+thing)=some+thing

I'm having trouble with Microsoft Access 2003, it's complaining about this statement: select cardnr from change where year(date)<2009 group by cardnr having max(time+date) = (time+date) and cardto='VIP' What I want to do is, for every distinct cardnr in the table change, to find the row with the latest (time+date) that is before year...

Finding language of a stored procedure in Postgresql

I've got a procedure with a prolang of 16386, which does not correspond to any of the entries in pg_lang; what other possible places can I look to find what language this is? ...

Using a trigger to record audit information vs. stored procedure

Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving...

Is there any Sql Query builder library for .Net?

Something like this. http://code.google.com/p/squiggle-sql/wiki/Tutorial. This is required for cases where It is required to build complex sql from the user input from UI. Currently in the project I am working is using String Manipulation which looks ugly and is difficult to maintain. ...

How to create a temporary table in Oracle

Hi I would like to create a temporary table in a Oracle database something like Declare table @table (int id) In SQL server And than populate it with a select statement Is it possible? Thanks ...

Selecting from a Large Table SQL 2005

I have a SQL table it has more than 1000000 rows, and I need to select with the query as you can see below: SELECT DISTINCT TOP (200) COUNT(1) AS COUNT, KEYWORD FROM QUERIES WITH(NOLOCK) WHERE KEYWORD LIKE '%Something%' GROUP BY KEYWORD ORDER BY 'COUNT' DESC Could you please tell me how can I optimize it to speed up th...

SqlPlus on mac osx 10.6 doesn't work

When i try to run this #sqlplus system@orcl it gives me this error SQL*Plus: Release 10.1.0.3.0 - Production on Tue Apr 20 02:24:41 2010 Copyright (c) 1982, 2004, Oracle. All rights reserved. Enter password: ERROR: ORA-12154: TNS:could not resolve the connect identifier specified the oracle server is working , I can connect th...

MySQL Column Value Pivot

I have a MySQL InnoDB table laid out like so: id (int), run_id (int), element_name (varchar), value (text), line_order, column_order `MyDB`.`MyTable` ( `id` bigint(20) NOT NULL, `run_id` int(11) NOT NULL, `element_name` varchar(255) NOT NULL, `value` text, `line_order` int(11) default NULL, `column_order` int(11) default NUL...

Legit? Two foreign keys referencing the same primary key.

Just as an update for Cliffs, Thanks ChaosPandion for the template. Person PersonID Int PK Network PersonID Int PK FK OtherPersonID Int PK FK OR Person PersonID Int PK Network PersonID Int PK FK FriendID Int PK FK Friend FriendID Int PK OtherPersonID Int FK ++++++ Original Post Below ++++++ Hi All,...

SQL? "Show me all the Activities that can be performed by User X."

I'm at my wits end. I've searched like crazy, read plenty of database textbooks and God knows how many online guides, blogs and forums. Can someone put me out of my misery? PseudoSchema Diagram (Warning: although pretty, not in a format even remotely approaching ER diagrams! Primary keys are bold, foreign keys are italics.) Given the a...

SQL - How can I apply a "semi-unique" constraint?

Hi, I have a (simplified) table consisting of three columns: id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, foreignID INT NOT NULL, name VARCHAR NOT NULL Basically, I would like to add a constraint (at the database level rather than at the application level) where it only possible for one unique 'name' to exist per foreignID. For exampl...

Struggling to create correct relationships in MS Access

http://img714.imageshack.us/img714/7820/croppercapture1.png Basically: an award(course) has many units, which can be either optional or core(mandatory), depending on the award. So for example: the unit 'Advanced Software Engineering' maybe a core unit for the award 'Software Engineering BSc' but only an optional unit for the course 'Web...

can I see all SQL statements sent over an ODBC connection?

I'm working with a third-party application that uses ODBC to connect to, and alter, a database. During certain failure modes, the end-results are not what I expect. To understand it better, I'd like some way of inspecting all the statements sent to the database. Is there a way to do this with ODBC? I know with JDBC I could use http://ww...

SQL Like question

Is there a way to reverse the SQL Like operator so it searches a field backwards? For example, I have a value in a field that looks like this "Xbox 360 Video Game". If I write a query like below, it returns the result fine. SELECT id FROM table WHERE title like "%Xbox%Game%" However, when I search like this, it doesn't find any resu...

ActiveRecord + CodeIgniter - Return single value from query, not in array form.

Say you construct an activerecord query that will always just return a single value, how do you just address that single value instead of getting an array in return? For instance I am using an ActiveRecord query to return the SUM of a single column, it will only return this one single SUM, instead of having to parse the array is there a ...

Designing a game database

I'm trying to design a database to record game histories for a game I'm working on. I have 3 different tables: users, gamehistories, and gamescores. Columns for tables: users: uid, displayname, email gamehistories: gid, pubID, start (datetime), end (datetime) gamescores: gid, uid, score I am trying to produce the following result...

Conditionally set a column to its default value in Postgres

I've got a PostgreSQL 8.4 table with an auto-incrementing, but nullable, integer column. I want to update some column values and, if this column is NULL then set it to its default value (which would be an integer auto-generated from a sequence), but I want to return its value in either case. So I want something like this: UPDATE mytable...

Query performance difference pl/sql forall insert and plain SQL insert

We have been using temporary table to store intermediate results in pl/sql Stored procedure. Could anyone tell if there is a performance difference between doing bulk collect insert through pl/sql and a plain SQL insert. Insert into [Table name] [Select query Returning huge amount of data] or Cursor for [Select query returning huge am...

How can I search for numbers in a varchar column

I've got a simple nvarchar(25) column in an SQL database table. Most of the time, this field should contain alphanumeric text. However, due to operator error, there are many instances where it contains only a number. Can I do a simple search in SQL to identify these cases? That is, determine which rows in the table contain only digits in...