Some of Oracle's analytic functions allow for a windowing clause to specify a subset of the current partition, using keywords like "unbounded preceding/following", "current row", or "value_expr preceding/following" where value_expr is a physical or logical offset from the current row or value (depending on whether you have specified ROW ...
This code involves a recursive Stored Procedure call and a "not so great" method of avoiding cursor name collision. In the end I don't care if it uses cursors or not. Just looking for the most elegant approach. I'm mainly going to use it as a simple method to track down Stored Proc hierarchies (without buying a product). I tried cursors ...
I have some data files to import into a database with some "unique" delimiters: Field Separator (FS): SOH (ASCII character 1) Record Separator (RS) : STX (ASCII character 2) +’\n’ I'd like to import the files into Postgres using the COPY command but while I can specify a custom field delimiter, it can't handle the record separator. I...
I have a script that did double inserts into the database with the same data. Is there a good way to do this (without scanning through, inserting every record into an array, and then deleting duplicate array entries)? ...
An existing process changes the status field of a booking record in a table, in response to user input. I have another process to write, that will run asynchronously for records with a particular status. It will read the table record, perform some operations (including calls to third party web services), and update the record's status f...
I feel like an idiot asking this... Table 1: users id serial person integer username char(32) Table 2:persons id serial name char(16) Can I run a query that returns the name field in persons by providing the username in users? users 1 | 1 | larry123 persons 1 | larry 2 | curly SQL? select name from persons where users.person=p...
Hi everyone, I am divided between using a C# implementation and a pure SQL 2005/2008 implementation. I need to determine the length of usage based on timestamps of their last actions. My table contains "friendID" (uniqueIdentifier) and "lastAction" datetime. Here is an example: Bob, 1:00 PM Bob, 1:01 PM Bob, 1:20 PM Bob, 1:25 PM...
Who is worth reading? Who is the Scott Gu of the SQL world? thanks ...
I'm using SQL Server 2008 Management studio viewing a 2005 server and have just added 2 users. For some reason they both have slightly different icons and I'm not sure why. Anyone have a definitive list of the icons and their meaning or a link to microsoft's doc on it as I can't find anything anywhere. Thanks. ...
Suppose you store instances of a class in a relational table. How would you go about persisting a static attribute of that class? For example: class WebSiteUser { private static $common_homepage_content; private $username; private $password_hash; ... } Corresponding to: CREATE TABLE web_site_users ( username charact...
I’ve just found out that the execution plan performance between the following two select statements are massively different: select * from your_large_table where LEFT(some_string_field, 4) = '2505' select * from your_large_table where some_string_field like '2505%' The execution plans are 98% and 2% respectively. Bit of a difference ...
This is my setup: SQL server 2005 UserA already setup as user on the server but not on databaseA. GRANT Execute ON [GetOrders] TO [UserA] AS [dbo] As you can see, i have missed out a step. I haven't added UserA to the database yet. This doesn't fail and lets me grant execute on the object. I believe in 2000 it would have thrown an...
Below is a stored procedure to check if there is a duplicate entry in the database based upon checking all the fields individually (don't ask why I should do this, it just has to be this way). It sounds perfectly straightforward but the SP fails. The problem is that some parameters passed into the SP may have a null value and therefore...
I have a table that represents a series of matches between ids from another table as follows: CREATE TABLE #matches ( asid1 int, asid2 int ) insert into #matches values (1,2) insert into #matches values (1,3) insert into #matches values (3,1) insert into #matches values (3,4) insert into #matches values (7,6) insert into #matches v...
For a selectcommand in a sqldatasource is there any way to have the following: select col1, col2 from mytable where col1 IN (@inClause) @inClause would be an array of integer, for example. I don't think this is even possible in T-SQL, so my assumption is that it is not possible, but just wondering if there's a way to do it? ...
Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not h...
I have a sqlite (v3) table with this column definition: "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP The server that this database lives on is in the CST time zone. When I insert into my table without including the timestamp column, sqlite automatically populates that field with the current timestamp in GMT, not CST. Is there a wa...
I have a result from an SQL query that I would like to sort alphabetical, but there are a couple of commonly used results that I would like to float to the top. Is there a simple way I can achieve this either by doing clever SQL or by sorting the (asp.net) datatable once I have it filled? I know the database ID of the things that I wan...
Using SQL Server, how do I script a constraint on a field in a table, so that the acceptable range of values is between 0 and 100? ...
lets say i have tables a and b. b contains items that exist in table a. I want to be able to delete items in a that are in be. What would the sql look like? ...