Hi I'm project managing a development that's pulling data from all kinds of data sources (SQL MySQL, Filemaker, excel) before installing into a new database structure with a record base through 10 years. Obviously I need to clean all this before exporting, and am wondering if there are any apps that can simplify this process for me, or ...
I have a particularly slow query due to the vast amount of information being joined together. However I needed to add a where clause in the shape of id in (select id from table). I want to know if there is any gain from the following, and more pressing, will it even give the desired results. select a.* from a where a.id in (select id...
Just wanted to know if anyone is really using Objects and Collections in Oracle ? Is this something that should be avoided ? eg create type t_person as object ( id integer, first_name varchar2(30), last_name varchar2(30) ); ...
I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column. so for example i have a table like Table(ID, Name, RecordStatus) Record status can only have a value 1 or 2 (active or deleted), and i want to create a unique constraint on ID, RecordStatus only when RecordStatus = 1,...
I want to be able to find out how much space is left in my database files so that I can know when to increase the space so it doesn't do it when the application is running hard out. I would prefer to be able to script this so I can run it across multiple databases ion a regular basis. I have SQL Server 2000 and SQL Server 2005 database...
I have table with some fields that the value will be 1 0. This tables will be extremely large overtime. Is it good to use bit datatype or its better to use different type for performance? Ofcource all fields should be indexed. ...
I have a simple SQL query (in PostgreSQL 8.3) that grabs a bunch of comments. I've composed a bunch of ids before-hand and that gets fed into the WHERE IN clause like so... SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) This returns the comments in a natural order which in my case is the ids like 1,2,3,4. What I'm wan...
I'm not talking about doing a "SET NOCOUNT OFF". But I have a stored procedure which I use to insert some data into some tables. This procedure creates a xml response string, well let me give you an example: CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS DECLARE @reply varchar(2048) ... Do a bunch of inserts/updates... SE...
I want to make a query to list cats that took longer than average cats to sell? I have five tables: Animal, Sale, AnimalOrderItem, AnimalOrder, and SaleAnimal Animal table: AnimalID, Name, Category (cat, dog, fish) SaleAnimal table: SaleID, AnimalID, SalePrice Sale table: SaleID, date, employeeID, CustomerID Ani...
I am currently developing a very simple database that tracks people and the company certifications they hold. This db will need to store letters of certification as PDF files. I was asked to develop this in MS Access, but I think it would be better to build it in SQLServer and use Windows Forms to build the interface. This app will need...
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL): FOR EACH @LVL_NUM < @MAX_LVL: INSERT INTO ALLOCS SELECT 'OUT', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCN_SUMRY(@LVL_NUM) INSERT INTO ALLOCS SELECT 'IN', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCNS(@LVL_NUM) INNER JOIN ALLOCN_SUMRY...
What is the best way to retrieve the primary key of an inserted row, when using ODBC objects in .NET? For example (VB): Dim command As OdbcCommand = gOdbcConn.CreateCommand() command.CommandText("INSERT INTO auhinode (node_key, node_desc) VALUES (0, 'New Node')") ... Dim result As Integer = command.ExecuteNonQuery() I've seen a coupl...
I'm trying to do a sub-select and Inner Join in the same query, with little success. The query, as it stands is below, and returning the error The multi-part identifier "Company.LocalityId" could not be bound. UPDATE Company SET Company.BusinessRegionId = b.Id FROM ( SELECT Id FROM BusinessRegio...
Hi folks, this is a follow on question to a previously asked question. I have the following data in a single db table. Name LeftId RightId ------------------------------------------ Cat 1 Cat 1 Dog 2 Dog 2 Dog ...
I am unfamiliar with Oracle and i need to have equivalent of my script for Oracle. Script: SELECT COL_NAME(parent_object_id, parent_column_id) AS ForigneKeyField, COL_NAME(referenced_object_id, referenced_column_id) AS PrimaryKeyField, OBJECT_NAME(referenced_object_id) AS PrimaryTable FROM sys.foreign_key_columns W...
I am using sql server 2000 and asp.net with c# for making a search engine and i want to refine the results obtained from a search without making server connection again and again due to speed factor how can i make my search fast?..............how can i make sql server connection lesser?... ...
how to get bottoms 3 record on msaccess ...
I have this query: SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName FROM Items WHERE Items.ProductCode IN ( SELECT TOP 10 Recommended.ProductCode FROM Recommended INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode AND Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL') ORDER BY CHECKSUM(NEWID()));...
We have a pretty large website, which handles a couple million visitors a month, and we're currently developing an API for some of our partners. In the near future, we hope to expose the API to all of our visitors. As we are trying to limit the number of requests to say, 100.000 / day and 1.000 / minute, I need to log the total number o...
If I need to search on Date of birth which is stored without the hours and minutes but the date I have to search with includes hours and minutes what is the best way to return all rows where date is matched on only day, month and year i.e. Stored as 01-JAN-50 10.22.06.000000000 date selected 01-JAN-50 10.22.06.010101120 If I us...