sql

Querying multiple rows from Oracle table using package

I wrote a package to query rows from a table. This select query will call other functions and returns all the rows from table. But when i write a package with all functions and sprocs , my sproc with select statement gives me an error saying i cannot execute without into statement. But if i use into then it will return only one row. How ...

SQL: how do write the ORDER BY based on value within a group?

I have a table project issues updated 1 1 2009-09-03 1 2 2009-09-08 2 1 2009-09-12 2 2 2009-09-01 and I would like to sort so that the projects are sorted in descending order so that the project with the latest updated issue is first etc., but all issues of a project are kept together and the issues are in asce...

How can I handle multiple row revisions in a single table?

I'm working on an app where users enter pricing quotes. They want to be able to have multiple revisions of the quotes and have access to all of them to revise and view. Currently the data is stored in a Quotes table that looks like this: QuoteID (PK, autonumber) data1, data2, data3 and so on. QuoteID foreign keys to other tables for o...

Rails - What Should I use for Search?

I have a couple specific needs for my search and I'm interested to get people's opinions on what search approach makes the most sense. Based on my explanation below, would you recommend that I use basic sql queries? Or step up to a more advanced search solution, like Sphinx? I have two models that I want to search in: products and v...

LINQ To SQL - Poor Sql Performance

Hey all, I have a WPF at a customer site that makes calls to a remote SQL 2005 server using Linq To Sql. Almost everyday the customer experiences aweful slow downs, and I am not sure what to do. The quick fix is to restart the mssql service and that seems to do the job, but that is not a solution. Tonight I used the SQL profiler and ...

Passing a list of system.guid as params to as SQL2008 sproc

I would like to pull a set of records in a SQL sproc by passing in a list of system.guid and also by passing in a list of integer keys. I can put the guids ihto a string, of course, but the question is - how do I quote the test values so they are recognized as UIDs by SQL In essence I was to DECLARE @pklist AS VARCHAR(1000) -- This ...

Getting unneccessary data from mysql database??? (php)

I have a database that has a users first and last name. Some cases i display the users full name, but in other cases i just show the first name. i have a function that gathers user information. within the function, i use an sql query to gather the first and last name, but i also concat the first and last name and label it full_name. Ex...

SQL statement for maximum common element in a set

I have a table like id contact value 1 A 2 2 A 3 3 B 2 4 B 3 5 B 4 6 C 2 Now I would like to get the common maximum value for a given set of contacts. For example: if my contact set was {A,B} it would return 3; for the set {A,C} it would return 2 for the set {B} it would return 4 What SQL s...

SQL query to display db data

I have the following database table: Answer MemberID | QuestionNo | AnswerNo | AnswerString 10 | 1 | 2 | q1 anwer2 10 | 2.1 | 3 | q2.1 answer3 10 | 2.2 | 5 | q2.2 answer5 10 | 7 | 1 | q7 answer 7 11 | 1 | 3 | q1 a...

How to update a column fetched by a cursor in TSQL

Before I go any further: Yes, I know that cursors perform poorly compared with set-based operations. In this particular case I'm running a cursor on a temporary table of 100 or so records, and that temporary table will always be fairly small, so performance is less crucial than flexibility. My difficulty is that I'm having trouble findi...

Efficent opening balance calculation in SQL

I'm looking for a good approach for calculating and caching periodic opening account balances using SQL. I know I can use SQL to sum up a bunch of transactions from start of time to the start of the period in question, but I'm more interested in whether its worth caching those calculated balances (at various points in time) in another ...

SQL CASE Statement Not Working Correctly

I have a view in SQL Server 2008 with several columns that are expressions of one column divided by another. I have to account for the divisor being 0, so I use a CASE statement. Here is an example of one: CASE SUM(dbo.GameStats.BringBacksAttempted) WHEN 0 THEN 0 ELSE SUM(dbo.GameStats.BringBacks) / SUM(dbo.GameStats.B...

Having trouble with SQL COUNT

I'm using MS SQL 2008 and I have a table of statuses (id, name) and a table of items (id, name, statusid, deleted). I want to count the number of items with each status, and have the following query: SELECT status.id, ISNULL(COUNT (items.name), 0) AS 'count' FROM status LEFT OUTER JOIN items ON items.statusid = status.id GROUP BY statu...

How can I increment an identity column without inserting a value ?

First, I'm aware of this question which didn't get answered because what the OP was really trying to do was'nt incrementing an identity column I've got an identity column with a current seed value of x, and I would like to reseed it to x+1 (ie I want my identity column to jump directly from x to x+2. I know I can do that using the fo...

Check if email exists

Hi, I have a classic ASP page with some code to check if an email exists in the table as follows; <% '' //Check the submitted email against existing ones in the database set CmdCheckEmail = server.CreateObject("ADODB.Command") CmdCheckEmail.ActiveConnection = MM_dbconn_STRING CmdCheckEmail.CommandText = "SELECT COUNT(Re...

How do aggregates (group by) work on SQL Server?

How does SQL Server implement group by clauses (aggregates)? As inspiration, take the execution plan of this question's query: select p_id, DATEDIFF(D, MIN(TreatmentDate), MAX(TreatmentDate)) from patientsTable group by p_id Before query data, simple select statement and its execution plan is this: After retrieving the data with t...

Encrypting/decrypting data to database

I need to create a .NET application that will store some confidential information to the database (e.g. passwords and stuff). I could use symmetric encryption to encrypt these before I store them to database but if someone de-compiles source code symmetric password could be compromised. Since this is going to be service application I ca...

Counting DISTINCT over multiple columns

Is any better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two columns. I hope that makes sense. ...

Why are my accented characters breaking in SQL Server 2005?

When I update my database with this command: UPDATE myTable SET Name = 'Hermann Dönnhoff' WHERE ID = 123; SQL Server actually puts 'Hermann Do¨nnhoff' in the field instead. Instead of faithfully inserting the o-umlaut (char(246)), I'm getting two characters ( char(111) + char (168) ). This happens for all characters that have accent ma...

How to get the start time of an SQL process?

A job running on our SQL server failed. We are running MS SQL server 2005. While investigating, the following question came up: When was this process initiated on the server? Is there any query I can run that will give me this information? ...