I have a search that is very slowly done by finding the primary records then looping in a thread to sum related transactions. I'm trying to make it work in a single statement, have come close but still have records which are alternating debit and credits. I can't work out how to pull the debit and credit rows up into columns so there is...
Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)? Something like this, only it doesn't work :) delete from table order by id ASC limit ((select count(*) from table ) - N) Thanks. ...
Obviously if I am using JDBC/ODBC, I can use bind variables and prepared statements to prevent SQL injection. However, when data is passed to batch processes that end up invoking Oracle SQLPlus, is there a way to prevent SQL injection? For example: query.sql: select '&1' from dual; exit; If I call this script from SQLPlus thusly: $ ...
I ultimately need a list of "import" records that include "album" records which only have one "song" each. This is what I'm using now: select i.id, i.created_at from imports i where i.id in ( select a.import_id from albums a inner join songs s on a.id = s.album_id group by a.id having 1 = count(s.id) ); The nested sele...
I am using SQL Express 2008 as a backend for a web application, the problem is the web application is used during business hours so sometimes during lunch or break time when there is no users logged in for a 20 minute period SQL express will kick into idle mode and free its cache. I am aware of this because it logs something like: S...
This seems like a common case, but for whatever reason, I am stymied by EF's weak editor. I have two tables, like so: Table1 ( Column1 int, PK Column2 int, PK ) Table2 ( Column1 int, PK, FK Column2 int, PK, FK Column3 int, PK ) Now, the database (out of our control) does not have foreign key constraints. In this example, Table2 is s...
I am moving some data and I need to come up with a TSQL statement to convert dates currently in a datetime field to another database field with the varchar MM/yy format. This statement needs to work on both SQL Server 2k5 and also SQL Compact Edition 3.5 - so the answer needs to be "set" based and not include cursors etc that are not s...
I am a system administrator for a company that supports a CRM CMS (Salesforce). I don't like the built-in functionality so I generally manage data through CSV/XLS files for uploading and downloading, because I can write better queries and the like. One of my tasks is uploading Contacts and Accounts. Because of a lack of unique Identifie...
I have the following: set @SomeVariable = @AnotherVariable/isnull(@VariableEqualToZero,1) - 1 If @VariableEqualToZero is null it substitutes the 1. I need it to substitute 1 if @VariableEqualToZero = 0 as well. How do I do this? ...
In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results. I am creating a functionality where I have to pick up the 5 customers randomly every time. Can anyone tell me how to create a query which will get random 5 rows (Id, and Name) every time when query is executed. edited I am using M...
table name:id col1:owner-name col2:book-id1 col3:book-id2 table name:book-name col1:id col2:name Can anyone get me a query to get following result owner-name,book1-name,book2-name using above 2 table ...
Hi, How can we know the description of every column in a table(DB2) through SQL? My data base is DB2. Thanks in Advance ...
I'm working on repairing the test suite for a project of ours, which is being tested through Hibernate/DBUnit. There are several test cases which all throw a similar exception from Hibernate, which looks something like this: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@109062e in statement ...
hello, refering my last question on extracting SQL stored procedures into .sql files (see here) I have another question: how to avoid or delete the sqlcmd blank line between Resultsets? Reason (see MSDN) When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. This means that stored procedures...
Hi Guys, I'm writing a front end to a database, presenting options as comboboxes. And I need to be able to return nulls to the database. I have a solution at the moment which involves 'fudging' a record into the query to populate the Datatable, then detecting the selection and hard coding null into my update statement if this item has...
Hi, I am actually dropping a temporary table in Database. I have to delete only if it exists in the Database. Please let me know how to check if a table exists in Data base. ...
I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page number. So I have something like that: WITH SelectedItems AS (SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Po...
Hi, Please clear my doubt about this, In SQL Server (2000 and above) is primary key automatically cluster indexed or do we have choice to have non-clustered index on primary key? ...
If I have a table CREATE TABLE users ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, profession varchar(255) NOT NULL, employer varchar(255) NOT NULL, PRIMARY KEY (id) ) and I want to get all unique values of profession field, what would be faster (or recommended): SELECT DISTINCT u.profession FR...
i want to perform analysis on SQL code. In this i would like to separate the blocks in SQL code. for example, in the code below for condition1 loop stmt1; for condition2 loop stmt2; end loop; end loop; i want to separate these two blocks and create a data structure which will contain outer loop block and will also have inner loop bloc...