sql

Executing a stored procedure to insert records into a table - SQL Server 2000

Hi, I'm working in SQL Server 2000. The following query is not working declare @TempAccountKey Table (AccKey int,SitName varchar(1000),SitKey int) insert into @TempAccountKey(AccKey,AccName) exec [usp_Get_AccountForUser] @UserName It is throwing the error EXECUTE cannot be used as a source when inserting into a table variable. A...

SQL View or Table

I have a table of data that gets update once a week. I then have a query that process this data and essentially returns a list of codes and the amount of hours booked to those codes. This query is reasonably complicated and takes about 5 seconds to run. This data needs to be used by a lot of other query's in the DB, so I want to put it ...

sub query returning more than one row

I am trying a query like this in MYSQL select Sum(case when WindowsXP = "PASS" then 1 else 0 end) as PASS , Sum(case when WindowsVista = "FAIL" then 1 else 0 end) as FAIL from OS_Table where BuildID = (select distinct BuildID from OS_Table) group by BuildID The error is Subquery returns more than one row. If I use IN instead of = t...

SQL Server - Rewrite trigger to avoid cursor based approach

If I have table Test with two columns num1 and num2 and the following trigger on it which just increments num2 on inserts of num1: DECLARE @PROC_NEWNUM1 VARCHAR (10) DECLARE @NEWNUM2 numeric(20) DECLARE my_Cursor CURSOR FOR SELECT num1 FROM INSERTED; OPEN my_Cursor FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1 WHILE @@FETCH_STAT...

Deleted some Oracle DBF files by mistake - how can I tell Oracle XE to forget about them?

So I accidently deleted some DBF files (only ones specfic to my tablespaces), fortunately I was only just about to start loading data in so have lost nothing, except now can't re-create the tablespaces. If I run: select name from v$datafile; The results include the DBF files that I deleted. I attempted to run a command I found on th...

How to search in sql for hour/minutes?

I have to search all rows with a filter with specific hour and minute. Example: 26/05/2009 - 16:00 26/05/2009 - 17:00 10/11/2009 - 09:00 10/11/2009 - 10:00 10/11/2009 - 11:00 10/11/2009 - 12:00 I want all rows with hour/minute "17:00" and "18:00". And the date is optional field. I don't know how to do this. I'm using "sql anywhere ...

SQLite select next and previous row based on a where clause

I want to be able to get the next and previous row using SQLite. id statusid date 168 1 2010-01-28 16:42:27.167 164 1 2010-01-28 08:52:07.207 163 1 2010-01-28 08:51:20.813 161 1 2010-01-28 07:10:35.373 160 1 2010-01-27 16:09:32.550 46 2 2010-01-30 17:13:45.750 145 2 2010-01-30 17:13:42.607 142 2 2010-01-30 16:11:58.0...

Zend Framework zend_db_select ORDER

$select->where('MATCH(text,phone,phone2,email,email2,www,gadi,augums,skype) AGAINST(?)',$searching_string); $select->order('MATCH(text,phone,phone2,email,email2,www,gadi,augums,skype) AGAINST(?) DESC',$searching); Getting error: Message: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound The problem seems in...

Microsoft Access 2003 - creating a separate field that totals 5 other fields from the table.

This is what i have so far in the SQL code........ SELECT DISTINCTROW [OEE/A Query].Press, Sum([OEE/A Query].[SumOfLabor Hours]) AS [Sum Of SumOfLabor Hours], Sum([OEE/A Query].[SumOfGood Pieces]) AS [Sum Of SumOfGood Pieces], Sum([OEE/A Query].[Scrap Pieces]) AS [SumOfScrap Pieces], Sum([OEE/A Query].[SumOfMachine Hours]) AS [SumOf...

Microsoft Access 2003 - Laying out code correctly.

SELECT DISTINCTROW [OEE/A Query].Press, Sum([OEE/A Query].[SumOfLabor Hours]) AS [Sum Of SumOfLabor Hours], Sum([OEE/A Query].[SumOfGood Pieces]) AS [Sum Of SumOfGood Pieces], Sum([OEE/A Query].[Scrap Pieces]) AS [SumOfScrap Pieces], Sum([OEE/A Query].[SumOfMachine Hours]) AS [SumOfSumOfMachine Hours], Sum([OEE/A Query].[Total Parts Hour...

Small Php and MySql problem

I have an array of ID:s, and the ID:s are in this format: Bmw_330ci_89492822 So it's a string! Now, I have this code to find whatever is in that array, in MySQL: ($solr_id_arr is the array I mentioned above, it contains string ID:s) ex: $solr_id_arr[0] outputs Bmw_330ci_89492822 $solr_id_arr_imploded = implode(", ", $solr_id_arr);...

How do i delete a foreign key constraint programmatically in Microsoft Access

How do i delete a foreign key constraint programmatically in Microsoft Access, preferable using SQL. For starters i don't know how to find the name of the foreign key. I connect to Access from a Java application using the JDBC-ODBC bridge. I want to execute the SQL from my Java application. I can see the relationship in Access, in the ...

Check if role exists in DB?

I want to add users in same role in more than one database. However the role can/cannot be present in all the database. How can i check if the role exists in database and if it does add users in that role? e.g. IF role exists BEGIN Add user in role END ...

Case statement with multiple whens...not working!

This is a simplified version of what I'm doing, but I can't get anything to work. The statement gives me an error without the comma after 'ERR'. I want the column to be 'Month' and I tohught this would work but I'm having a ton of trouble. Thanks for your help! select a.POL_PRI_RSK_ST_CD, a.MASTER_COMPANY_NBR, case when a.char04...

SQL finding the maximum of all averaged results

I have the relation instructor(ID, name, dept_name, salary). How would I go about finding the name of the department with the highest average salary? ...

MySQL Split to use in "SELECT WHERE IN" statement

I've been scouring various MySQL sites in the hope of finding something that will allow me to turn this: var parameters = "a,b,c,d"; // (Could be any number of comma-delimited values) Into this (assuming that my parameters are somehow becoming the values you see in the IN block): SELECT * FROM mytable WHERE parametertype IN('a', 'b',...

JPA Query Validating if input exist in database

Hi, I want to validate if input exist in database in netbeans IDE protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { Transaction1 t = new Transaction1(); //t.setTransactionID(12345); t.setFromAccNo(Integer....

Log Shipping - 24 hour difference between source and destination system times.

We are trying to have log shipping work between two database servers where the destination server has a system time set 24 hours earlier than the source system time. Is it possible to force the destination machine to restore the data (disregarding the transaction file stamps are 24 hours ahead). ...

Quickly add a copy of a column to a MySQL table.

I need a fast way duplicate a DATETIME column in a table and give it a new name. I have a column named myDate in my table called myResults, I need a query to make a new column in the table called newDate which has the exact same data as the myDate column. Is there a faster way to do this than by doing the obvious 2 step approach o...

What is the best way to tie 1 table to many tables

I have a table. Call it TableA this table will link to many tables and ideally be enforced by database relationships in (many-1)(TableA-TableB) (many-1)(TableA-TableC) ... etc The solution i have is to put all the foreign keys of TableB, TableC, etc in TableA along with a "Type" field (which contains a word version of which relationshi...