sql

issue in sql Query

I have an column in table where this column name is items it contains value like this itemID items 1 school,college 2 place, country 3 college,cricket 4 School,us,college 5 cricket,country,place 6 football,tennis,place 7 names,tennis,cricket 8 sports,tennis Now I need to write a...

Unit testing Sql for logical reads

We have unit tests for our data access layer. This has helped to spot sql syntax errors. Now that we have these tests I would like to take it one step further. I would like to run the unit tests and find sql that has a high number of logical reads, Automatically.(to find sql in need of tuning) Adding "set statistics IO" to the sql is no...

Query To Get Consecutive Events If They Are Close Enough

This is my table structure: monResults: id Int(10) monDateTime Datetime() typeId int(10) I need a query to get all monResults that have consecutive monDateTimes that are less than 1 minute apart and have the same typeId. I want to be able to get X number of these events. Mostly I don't know how to add the condition to make consec...

UPDATE row when matching row exists in another table

I need to update a field on a table to be true only if a matching row exists in another table, for all the rows where the column is currently null in the main table. This is a description of what I want to achieve: UPDATE [LenqReloaded].[dbo].[Enquiry] A SET [ResponseLetterSent] = 1 WHERE [ResponseLetterSent] IS NULL AND EXISTS ...

Building a non sequential list of numbers (From a large range)

Hi All, I need to create a non sequential list of numbers that fit within a range. For instance i need to a generate a list of numbers from 1 to 1million and make sure that non of the numbers are in a sequential order, that they are completly shuffled. I guess my first question is, are there any good algorithms out there that could he...

run sql queries under a particular schema context

Hello Experts, We are thinking about to create new schema with its own 3 tables which will be created on the fly for an individual customer. To run a particular query for those tables in a procedure, should we have something like this. declare @sName nvarchar(200); select @sName =Schema_Name from schema where Schema_Id = passed_id_fro...

Can we use "if else" in "Check" constraint in sql server

Can i use if else under a check constraint. Can i use check constraint using a variable need xplanation with eg. ...

SQl Management Studio - Find "Rows Affected" BEFORE running Update Statement...

Before running an Update statement in SQL Server Management Studio, I'd like to see how many rows it is going to affect so I know my logic is sound. Any way to do this? I've tried running an execution plan a few times, and after running when I hover over the green update icon it shows rows affected, but sometimes it shows 20.5 rows fo...

How do I select the first row per group in an SQL Query?

I've got this SQL query: SELECT Foo, Bar, SUM(Values) AS Sum FROM SomeTable GROUP BY Foo, Bar ORDER BY Foo DESC, Sum DESC This results in an output similar to this: 47 1 100 47 0 10 47 2 10 46 0 100 46 1 10 46 2 10 44 0 2 I'd like to have only the first row per Foo and ignore the rest. 47 1 100 46 0 100 44 0 2 Ho...

SQL find next record by non-unique field

I need to find the next record in a database with (say) a given Surname. So if the user is looking at A Smith, the query would return either the Next A Smith, or B Smith the solution is probably similar to that discussed here : http://stackoverflow.com/questions/638065/selecting-subsequent-records-arbitrarily-with-limit but using an ODB...

ORACLE - Select without FROM

in SQL Server is possible to make a select without reference to a table. something like Select 1.2 +3, 'my dummy string' As Oracle does not allow a SELECT without a FROM, I use the dual table for this type of operations, ie something like Select 1,2+3, 'my dummy string' FROM DUAL There is a better way of doing this type of querys? ...

Aggregate function returning first row data for column not in the group by

I have a table relationship that looks like the following: barn ------ PK barn_id <other columns> stable --------- PK stable_id FK barn_id stable_number stable_contents timestamp So whenever the contents of a stable change I just put in a new row with the corresponding barn_id and stable_number with new stable_contents and a current ...

Index usage with OUTER JOIN that contains IN statement

SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON b.user IN (:userlist) AND b.key = a.fk_to_b WHERE a.user IN (:userlist) OR b.user IN (:userlist) Table b has an index of: (user, key) The database only uses the index when the :userlist parameter contains a single value. When :users contains multiple values (which ...

Retrieve SqlCommand/OleDbCommand query after inserting parameters?

Is there any way to access the prepared statement as sent to the SQL server? Dim params(1) As OleDb.OleDbParameter params(0) = New OleDb.OleDbParameter("@ID", OleDb.OleDbType.VarChar, 50) params(0).Value = guiItemsGridView.Rows(e.RowIndex).Cells("ID").Value params(1) = New OleDb.OleDbParam...

Impact of defining VARCHAR2 column with greater length

What are the effects of defining a column with VARCHAR2(1000) instead of VARCHAR2(10) in Oracle, when the values are not longer than 10 Byte? Does the column only take the space really necessary to store the values, or would that have any negative impacts on the size/performance of tablespaces/indexes? ...

SQL search multiple fields, return one

I have a query that needs to compare a value against multiple columns, and only return results from one. I keep getting a conversion error when it tries to convert a string argument into an integer, but I haven't been able to find a good way to circumvent this. SELECT DISTINCT CASE WHEN table_one.integer_col = CAST('argument%' AS...

Troubles pulling id from first insert done by stored procedure to do use on second stored procedure

I'm having troubles pulling the id from the first insert to use on the second insert. Here's my SQL (I'm using stored procedures): DECLARE @JoinDate date DECLARE @ID int SET @JoinDate = getdate() EXEC Members_Add $(UserID), '$(UserName)', @JoinDate, '$(firstname)', '$(lastname)', NULL, '$(Country)', NULL, '$(stateorprov...

Escape SQL Parameter

I need to work around an NHibernate bug I reported here. Essentially, NHibernate will generate multiple SQL parameters for the same HQL parameter, which results in problems for queries that use a parameter in a grouping construct. Until the bug is fixed, I figured I'd just concatenate the parameter into the HQL. Obviously this is suscep...

Exception on ExecuteNonQuery method about unsuccessful value convertion

i am using OleDbCommand.ExecuteNonQuery() to insert data into the database: ObjCommand = New OleDbCommand ObjCommand.CommandType = CommandType.StoredProcedure ObjCommand.CommandText = StrSQL ObjCommand.Parameters.Add("field1", OleDbType.VarChar).Value = <something1> ObjCommand.Parameters.Add("field", OleDbType.VarChar).Value = <somethi...

major php issues

I am trying to create a simple login system. When I run the login form (with the correct username and password) it doesn't seem to run the php. Any suggestions? <?php $host="linuxserver"; // Host name $username="jparry2"; // Mysql username $password=""; // Mysql password $db_name="jparry2"; // Database name $tbl_name="customer"; // Tabl...