sql

How to read files into existing rows LOB columns in Oracle?

I have to migrate data from one database to another. As preparation we have already extracted CLOB and BLOB data into separate files located on the Oracle server. The database contains several CLOB and BLOB columns in different tables that have been initialized with NULL values for all those rows. How can I load the content of a file in...

3rd <day_of_week> of the Month - MySQL

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something lik...

Does "select for update" prevent other connections inserting when the row is not present

Hi all, I'm interested in whether a select for update query will lock a non-existent row. e.g. Table FooBar with two columns, foo and bar, foo has a unique index Issue query select bar from FooBar where foo = ? for update If query returns zero rows Issue query insert into FooBar (foo, bar) values (?, ?) Now is it possible that t...

T-SQL Query : Getting Child nodes of a parent

I have a table with the following schema : ID , CatID, ParentCatID, SiteID I want to get all the sites that belong to the categories that are the roots ( means their ParentCatID = 0) and all their descendants. for example : ID , CatID, ParentCatID, SiteID -------------------------------- 1 , 2 , 0 , 3 1 , 4 , 2 ...

Duplicating a TABLE using Microsoft SQL Server Mangement.

Need to duplicate a TABLE using Microsoft SQL Management Studio 2008 The TABLE needs to duplicate all table row (Primary Key) ID as well. Thanks. ...

Can't run stored proc from ODBC with multiple output parameters

I'm having trouble executing a stored procedure in ODBC to a DB2 database where a stored procedure has multiple input and output params. If I specify only in input params in the call statement with ? like below, i get "SQL0440 - Routine XXXXXXX in *N not found with specified parameters.". I've changed the output params to be of type 'Ret...

Should I use a subquery?

I have two tables, one that stores the current price, and one that stores the historical price of items. I want to create a query that pulls the current price, and the difference between the current price and the most recent historical price. In the historical table, I have the start and end times of the price, so I can just select the...

SQL Where to filter on Nested query

I have a query that looks like this: Insert Into tblX (a,b,c) Select x as a, y as b (select top 1 whatever from tblZ as z where z.aID = y.aID order by z.s desc) as c from tblY as y where y.foo = 'bar' AND c <> NULL The problem is that last line. It tells me that c is an invalid column name. using y.c as well, to the same result. ...

Database Engines and ANSI SQL Compliance

I've been searching for half an hour and can't find any resources stating what level of the SQL ANSI standard is supported on various database engines. It looks like some level of support is provided by most engines, but I'd like to know exactly what level is officially supported. I'm primarily interested in MySQL, PostgreSQL, SQL Serv...

A mysql query that gives the most recent revision for each entity

I have my data split across two tables. One is the entity table where it describes the basics of the entity, and the revision table where the data of an entity resides. If I know the entity I can query for the most recent version: SELECT * FROM `entities` JOIN (SELECT * FROM `revisions` WHERE `entity_fk` = ? ...

Querying by a value in XML in SQL Server 2005

Let's say I have a column in a table where the datatype is XML. I have a specific value I want query for in an xml tag that is unique(no repeating) in the XML. How do I do this? Something like: select * from MyTable where XMLColumn.TagImLookingAt.Value = @QueryValue ...

Left Outer Join with subqueries?

---------- User ---------- user_ID(pk) UserEmail ---------- Project_Account ---------- actno actname projno projname ProjEmpID ProjEmpMGRID Where ProjEmpID,ProjEmpMGRID is the user_id and ProjEmpMGRID can be null. I need to look up the useremail and display the table project_account. I need to query with actNo which has duplicate valu...

What is the difference between ON and WHERE in mysql JOINs?

Possible Duplicates: Difference between and and where in joins In MySQL queries, why use join instead of where? What is the difference between SELECT * from T1 JOIN T2 ON T1.X = T2.X AND T1.Y = T2.Y and SELECT * from T1 JOIN T2 ON T1.X = T2.X WHERE T1.Y = T2.Y ? ...

Which is the best way to perform this query? Maybe UNION

I have these four tables described bellow. Basically I have feeds with entries relationed with categories, and each category can be a main category or not (flag named as "principal"). Also each feed can be a partner feed or not (flag named as "parceiro"). I want to select all feed entries from partrners feeds, so I have this: SELECT `e...

How to find an average grade for each student with SQL

Hi, I have a list of grades that students received. Each student appears multiple times in the table. How do I produce a list of all the students with their average grade? P.s. I've tried looking at previously asked questions to see if I can find something relevant, but with no luck. ...

How to obtain sale by date in java?

I am using ms access DB. I need to obtain sale by date. Here is my table specification: BILL_NO DATE SALE 1 8/30/2010 1000 2 8/30/2010 2000 3 8/31/2010 3000 4 8/31/2010 2000 If i want the sale for 8/31/2010 it should return 5000. I have inserted Date values using java.sql.Date object in DB. ...

The old IN vs. Exists vs. Left Join (Where ___ Is or Is Not Null); Performance

I have found my self in quite a pickle. I have tables of only one column (supression or inclusion lists) that are more or less varchar(25) but the thing is I won't have time to index them before using them in the main query and, depending how inportant it is, I won't know how many rows are in each table. The base table at the heart of al...

How do I write and run an upgrade script for .sdf databases?

How do I write and run an upgrade script for .sdf databases? Would sqlcmd be able to do that? I am not trying to upgrade the version of the database file. I am trying to upgrade the schema and data inside of it. ...

SSRS - Severe error message when running stored procedure

I have a report that runs a stored procedure that takes about an hour to run. After about 30 minutes I get this error message on the report body: An error has occurred during report processing. Query execution failed for data set 'DEV'. A severe error occurred on the current command. The results, if any, should be discarded. Operation ...

stored procedure executes correctly from management studio, but not in production

I have a stored procedure that works correctly when I execute the stored procedure from SQL Server Management Studio. Unfortunately, it doesn't behave the the same way on the production server. On an insert statement, it is only inserting some of the values and the rest are NULL. The NULL values are coming from user defined scalar func...