I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3 Note: Col3 is NEVER NULL ID Col1 Col2 Col3 ------------------------------ 1 A B X 2 NULL C X 3 NULL NULL X 4 D NULL X To get the ...
How to I get the SCHEMA when doing a select on sysobjects? I am modifing a stored procedure called SearchObjectsForText which returns only the Name but I would also like to include the SCHEMA. Right now it is doing something similar to this: SELECT DISTINCT name FROM sysobjects I would like to know what tables need to be joined to ...
For ex: If we have in table records like: 25/06/2009 28/12/2009 19/02/2010 16/04/2011 20/05/2012 I want to split/select this dates according to 6 month intervals starting from current date. result should be like: 0-6 month from now: first record 7-12 month from now: second record ... It will be much apreciated if you make this simp...
Hi, I needed to come up with a SQL query that returns rows that satisfy multiple conditions. This article describes what I needed to do and the solution: http://thenoyes.com/littlenoise/?p=58 Basically it uses a bit operation to figure out if a provided string is found.. but I'm having hard time following how it works. SET @q = 'A,...
I have a table that stores user information. The table has a userid (identity) column in it. The table data is queried by a view which is referenced by a lot of sprocs, but we reference something called auid which is created by the UDF below. The UDF is called in the view and sprocs then join or query from the view based on auid. It ...
Im having a moment, what i want to do is really simple and i think im just looking at the wrong solution. I want to simply return a table that is sorted based on a datestamp of a related table. ie: Table 1: 200 MyStuff OK 201 Other Why 202 Flat So Table 2: 1 200 5/12/2009 MyValue1 2 200 5/11/2009 MyValue2 3 20...
Hi folks, I have a table that contains a few columns and then 2 final (nullable) columns which are varbinary (actually, they are SQL 2008 geography types, but I want to keep this post database agnostic). I've hit around 500mb with around 200K rows. The varbinary is the problem - and I need the data. So, I was wondering if it's bad if ...
I have a bit of an architecture problem here. Say I have two tables, Teacher and Student, both of them on separate servers. Since this tables share a lot of data and functionality, I would like to use this inheritance scheme and create a People table; however, I would need tho keep the Teacher table and the People records relating Teache...
I need to create a script where someone will post an opening for a position, and anyone who is eligible will see the opening but anyone who is not (or opts out) will not see the opening. So two people could go to the same page and see different content, some potentially the same, some totally unique. I'm not sure the best way to arrange ...
I have an UPDATE statement that's intended to update a status field for a limited number of records. Here's the statement: UPDATE warehouse_box SET warehouse_box_status_id = wbsv.warehouse_box_status_id FROM warehouse_box_status_vw wbsv INNER JOIN pallet_warehouse_box pwb ON wbsv.warehouse_box_id = pwb.warehouse_box_id INNER JOIN rou...
Is there a another way to subtract the smallest value from all the values of a column, effectively offset the values? The only way I have found becomes horribly complicated for more complex queries. CREATE TABLE offsettest(value NUMBER); INSERT INTO offsettest VALUES(100); INSERT INTO offsettest VALUES(200); INSERT INTO offsettest VALU...
I have an HTML list that can be sorted (using jQuery's sortable API). I am storing the list items inside a database; each row stores the item's order, and text. How can I store the order of the list items so I do not need to update multiple rows when an item's order changes? List items can be added and removed. Here's an example of what...
I've been stumped with some SQL where I've got several rows of data, and I want to subtract a row from the previous row and have it repeat all the way down. So here is the table: CREATE TABLE foo ( id, length ) INSERT INTO foo (id,length) VALUES(1,1090) INSERT INTO foo (id,length) VALUES(2,888) INSERT INTO foo (id,length) VALUES(3...
We have a table called table1 ... (c1 int indentity,c2 datetime not null,c3 varchar(50) not null, c4 varchar(50) not null,c5 int not null,c6 int ,c7 int) on column c1 is primary key(clusterd Index) on column c2 is index_2(Nonclusterd) on column c3 is index_2(Nonclusterd) on column c4 is index_2(Nonclusterd) on column c5 is index_2(...
Suppose I am calling a query "SELECT name, city, country FROM People". Once I execute my SqlDataReader do columns come in the same order as in my sql query? In other words can I rely that the following code will always work correctly: SqlConnection connection = new SqlConnection(MyConnectionString); SqlCommand command = new SqlCommand(...
With full access to both db , order last ten post from two different wordpress blogs, ordering by post date. In local way, not using foreign services (with sql and php for example) ...
What are good things to check for, with respect to error-handling, when you are dealing with the data-access-layer? For example, let's assume I have this function.. Public Function UserExists(ByVal userName As String) As DataTable Dim dt As Object = Nothing Dim arSqlParameters(0) As SqlParameter arSqlParamet...
Is it possible to use TRY CATCH blocks in SQL Selects? For stuff similar to this for example: select order, CONVERT(DATETIME, orderDate) from orders What's the best way of handling this scenario? ...
Running into a problem. I have a table defined to hold the values of the daily treasury yield curve. It's a pretty simple table used for historical lookup of values. There are notibly some gaps in the table on year 4, 6, 8, 9, 11-19 and 21-29. The formula is pretty simple in that to calculate year 4 it's 0.5*Year3Value + 0.5*Year5Val...
Hi, I got this parameter: $objDbCmd.Parameters.Add("@telephone", [System.Data.SqlDbType]::VarChar, 18) | Out-Null; $objDbCmd.Parameters["@telephone"].Value = $objUser.Telephone; Where the string $objUser.Telephone can be empty. If it's empty, how can I convert it to [DBNull]::Value? I tried: if ([string]:IsNullOrEmpty($objUser.Tele...