Which are the diferrences between the following two queries? Both returns different rows: with ordered_table as ( select * from table order by column1 ) select first_value(column2 ignore nulls) over (partition by column3) from ordered_table; and select first_value(column2 ignore nulls) over (partition by column3 order by column1) fr...
I am having a problem searching multiple tables ,i have 2 tables tblcourse -courseid -name -status tblenroll -courseid(holds courseid from tblcourse) -studentid lets say a student has 1990 as student num and he registered to 2 courses in tblenrol I want to get the name of the courses that 1990 has and the ones he aint subscribe...
I have some code that I want to only allow access to by one thread. I know how to accomplish this using either synchronized blocks or methods, but will this work in a clustered environment? The target environment is WebSphere 6.0, with 2 nodes in the cluster. I have a feeling that synchronized won't work, since each instance of the ap...
I asked this question in regard to SQL Server, but what's the answer for an Oracle environment (10g)? If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using something like MSSQL's Commom Table Expressions, perhaps? ...
The query is the following: with t as ( select 450 id, null txt , 3488 id_usr from dual union all select 449 , null , 3488 from dual union all select 79 , 'A' , 3488 from dual union all select 78 , 'X' , 3488 from dual ) select id , txt , id_usr , first_value(txt ignore ...
I have to create a Rails migration which creates many triggers and stored procedures. Normally one would do that using the execute method, but because of the size of the statements, I'd rather keep them in an external file and reference it from the migration. How can I do that? Is it even possible? ...
I have upgraded a set of databases from sql server 2000 to sql server 2008 and now large reads are blocking writes whereas this wasn't a problem in sql server 2000 (same databases and same applications & reports) Why? What setting in 2008 is different? Did 2000 default to read uncommitted transactions? (update) Adding with (nolock) to t...
Visual Studio 2008 Database project for SQL Server 2008 The project has placeholders for Pre-Deployment and Post-Deployment SQL scripts. They work - well, most of the time anyways. The project has an option to Always-Recreate-Database: drop the existing database and create a fresh hot-from-the-oven database every time. When I deploy m...
I always see examples this way, but why? Is this good practice? ...
Hi! I hope someone can help me with the following: I need to know if it is possible to implement a trigger that monitors the value of an specific field, and when it changes the value, it should update the value of another field in another table. ...
Hello, I am interested is NVarchar(MAX) a good data type is I want to store short unicode strings which are 1-50 characters long, but most of them (more than 90%) are 5-10 characters long? The column will not be used in comparison and ordering queries. It might be added to index as included column. Expected rows count - more than 10M. ...
Hi all, Here's my situation: I want to SELECT all entries from a database WHERE id = $id. But I want the results to be listed in a certain priority. If, criteria = $criteria, then I want those results displayed first. Otherwise, I just want to keep displaying the rest of the rows. My question is this: will this solve my problem?...
What is considered best practice for executing recurring SQL queries? My understanding is to use a parameterized query and turn it into a prepared statement upon first execution. What if this query needs to be executed by multiple threads? Will I need to create a prepared statement for each type of query for each thread? Or is the par...
Suppose I have a collection of C++ objects in memory and would like to query them using an SQL statement. I’m willing to implement some type of interface to expose the objects’ properties like columns of a database row. Is there a library available to accomplish this? In essence, I’m trying to accomplish something like LINQ without usi...
I am trying to use TOAD and T-SQL to approximate a user's spreadsheet. Here are the basics of what they want: Order Number Customer Name June July Aug Sept Oct Nov Dec 12345 Bleh Company 1000 800 200 The first row represents when the order value was received and t...
We need to backup 40 databases inside a SQL Server instance. We backup each database with the following script: BACKUP DATABASE [dbname1] TO DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH NOFORMAT, INIT, NAME = N'dbname1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO declare @backupSetId as int select @backupSetId = positi...
I have an SSRS report that uses an Oracle datasource. I have a query; select * from myTable t where t.date between Date '2009-08-01' and Date '2009-08-04' + 1 This query works, both in Oracle and from SSRS. It gets all rows from myTable when the t.date column is between 08/01/2009 and 08/04/2009. The t.date column is of type Date() N...
I am having a very difficult time handling null returns in DB2. I've tried IFNULL AND COALESCE functions, but I still end up getting values in the return. Here is the relevant segment of the query: COALESCE ( DATE ( SUBSTR (DIGITS (PODATE),1,2) || char ('/') || SUBSTR (DIGITS (PODATE),3,2) || char ('/') || ...
How can I make the following SQL a Linq query: SELECT * FROM ORDERS INNER JOIN ITEMS ON ORDERS.ID = ITEMS.ORDER_A OR ORDERS.ID = ITEMS.ORDER_B I would think it would be: from o in orders join i in items on o.ID equals i.OrderA or o.ID equals i.OrderB select new { Order = o, Item = i } I'm guessing the compiler wants something else....
I have 2 similar queries select * from openquery(powerschool, 'select * from TEACHERS where teachernumber is not null and schoolid=''1050'' and teacherloginid is not null order by teachernumber') and SELECT * from openqu...