What would be the correct universal SQL construct to get the last row inserted (or it's primary key). The ID might be autogenerated by a sequence but I do not want to deal with the sequence at all! I need to get the ID by querying the table. Alternatively, INSERT might be somehow extended to return the ID. Assume I am always inserting a ...
I'm new to SQL and could use some help in creating a database schema for my program, which manages and installs programs for my home network. Are there any guidelines/tutorials for creating database schemas? ...
Hi all together, i was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently i do something like that: StringBuilder jobTypeInClauseBuilder = new StringBuilder(); for(int i = 0; i < jobTypes.length; i++) { Type jobType = jobTypes[i]; if(i != 0) { jobTypeInClauseBuilder.append(','); } ...
I have a query like DELETE from tablename where colname = value; which takes awfully long time to execute. What could be the reason? I have an index on colname. ...
Hello! (Please note that I have seen a similar question on StackOverflow recently, however I can not find it anywhere - if anyone can find it please post the link) I have a table that has a datetime field in it. For example: Table data DateTime date; int number; I need to find the difference between date in a row and date in the ne...
I have a table lnd_wkly_plan_rx_summary and columns pd_end_dt, nrx_cnt containing data PD_END_DT NRX_CNT 12/26/08 1,178.75 12/19/08 2,027.12 12/12/08 1,907.08 12/05/08 2,092.90 11/28/08 1,236.44 11/21/08 1,857.82 11/14/08 1,817.55 11/07/08 1,800.54 10/31/08 1,985.13 i need to put a join on same table lnd_...
Hi there, I have executed a code SELECT CASE b.ON_LOAN when 'Y' then 'In Lib' when 'N' then (SELECT c.duedate from book_copy a, book b, loan c where b.isbn = 123456 and a.isbn = b.isbn and a.book_no = c.book_no) END AS Availability, a.isbn, a.class_number FROM book_copy...
i have a hibernate query that is dynamically put together using the criteria api. it generates queries that are unbearably slow, if executed as-is. but i have noted they are about 1000% faster if I prepend /*+ FIRST_ROWS(10) */ to the query. how can i do this with the criteria api? i tried criteria.setComment(..), but this seems to be ...
I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing performance (some of these columns are being used for joins and some of the tables have 100K+ rows). I would like to change all of these columns to b...
I need to extract some management information (MI) from data which is updated in overnight batches. I will be using aggregate functions to generate the MI from tables with hundreds of thousands and potentially millions of rows. The information will be displayed on a web page. The critical factor here is the efficiency of SQL Server's han...
We are having problems with database performance, and I have a lot of experience with .NET Profilers and always perform the analysis on the app, but much like a lot of developers I am now waiting until really late (when its a problem) to start analyzing and trying to gather the data on how to fix the issue. This is probably not going to...
How do I index a foreign key in Oracle? ...
SELECT A.PD_END_DT Recent_PD_END_DT, B.PD_END_DT Last_PD_END_DT, A.NRX_CNT - B.NRX_CNT DELTA FROM wkly_lnd.lnd_wkly_plan_rx_summary A, wkly_lnd.lnd_wkly_plan_rx_summary B WHERE a.MKT_I D =b.MKT_ID; ...
Hello, We are not hosting our databases. Right now, One person is manually creating a .bak file from the production server. The .bak then copied to each developer's pc. Is there a better apporach that would make this process easier? I am working on build project right now for our team, I am thinking about adding the .bak file into SVN so...
is there a way to tell ms access (2003) to not put joins into parentheses. or at least to understand them without (every other database does) i want something like: SELECT * FROM a INNER JOIN b ON a.a = b.a INNER JOIN c ON b.c = c.c but access tells me that the query is wrong. IT’S NOT, and it’s driving me c...
I have two tables say (FCT_SALES_SUMMARY_A and FCT_SALES_SUMMARY_B). If we assume that table A has be generated on every monday than table B will be generated on next monday i.e after 1 week.like that there will be data for 104 weeks.But the as the weeks increase the previous data will be lost in FCT_SALES_SUMMARY_A as shown below i.e...
I have a table with an image column in SQL Server 2000. I need to save the image data to a file on the file system. In SQL Server 2005 I can use the ADODB.Stream object for file I/O but this doesn't seem to exist in SQL Server 2000. The closest thing I can find is Scripting.FileSystemObject but it only seems to have support for text f...
We performed an upgrade of a proprietary program that formerly used Access and now uses SQL. The table schema are exactly the same, but the problem is that the primary key restarted when we switched to SQL. I've imported the old access database into another sql table in the database. The current (SQL based) table is named Archive and ...
I have this table which doesn't have a primary key. I'm going to insert some records in a new table to analyze them and I'm thinking in creating a new primary key with the values from all the available columns. If this were a programming language like Java I would: int hash = column1 * 31 + column2 * 31 + column3*31 Or something l...
Possible Duplicate: Why would I ever choose to store and manipulate XML in a relational database? Although this question on the surface appears to be a rehash of what's already been previously asked, I'll state up front that it is not. My question is not how to store or retrieve XML from a relational database. The question at ha...