We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle. SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate. Date parts examples are day, week, month, year, etc. . . What is the Oracle equivalent? I...
I am going to answer my own question because I just found the answer, but thought it still worth posting here. I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 ...
I have a database with two tables (Table1 and Table2). They both have a common column [ColumnA] which is an nvarchar. How can I select this column from both tables and return it as a single column in my result set? So I'm looking for something like: ColumnA in Table1: a b c ColumnA in Table2: d e f Result set should be: a b c d e f ...
Given a small set of entities (say, 10 or fewer) to insert, delete, or update in an application, what is the best way to perform the necessary database operations? Should multiple queries be issued, one for each entity to be affected? Or should some sort of XML construct that can be parsed by the database engine be used, so that only o...
Ideally, this script could be run multiple times, as new tables were added to the database. SQL Server Management Studio generates scripts for individual database objects, but I'm looking for more of a "fire-and-forget" script. ...
Is there any efficiency difference in an explicit vs implicit inner join? For example: select * from table a inner join table b on a.id = b.id; vs. select a.*, b.* from table a, table b where a.id = b.id; ...
I'm looking for a good solid Regular Expression for detecting SQL in a string. Does anyone have a sample of something they've used before? ...
Let's say that I want to have a table that logs the date and the number of columns in some other table (or really any sort of math / string concat etc). CREATE TABLE `log` ( `id` INTEGER NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL , `count` INTEGER NOT NULL , PRIMARY KEY (`id`) ); Is it possible to have the count column calcula...
When we create stored procedures in Visual Studio (with the extension .sql) the file encoding is automatically set to 'Unicode (UTF-8 with signature) - Codepage 65001'. This causes ours DBA's problems as their scripts don't like it. We have to manually go in and change them to 'Western European (windows) - Codepage 1252'. This only se...
Ok, this should be an easy question to answer. I need the month+year from the datetime in sql server like 'Jan 2008'. I'm grouping the query by month,year. I've searched and found functions like datepart, convert etc. but none of them seem useful for this. Am I missing something here? Is there a function for this? ...
I have a database with a few dozen tables interlinked with foreign keys. Under normal circumstances, I want the default ON DELETE RESTRICT behavior for those constraints. But when trying to share a snapshot of the database with a consultant, I needed to remove some sensitive data. I wish that my memory of a DELETE FROM Table CASCADE comm...
How do you rewrite this in Linq? SELECT Id, Name FROM TableA WHERE TableA.Id IN (SELECT xx from TableB INNER JOIN Table C....) So in plain english, I want to select Id and Name from TableA where TableA's Id is in a result set from a second query. ...
I inserted some values into a table. There is a column whose value is auto-generated. In the next statement of my code, I want to retrieve this value. Can you tell me how to do it the right way? ...
My work has a financial application, written in VB.NET with SQL, that several users can be working on at the same time. At some point, one user might decide to Post the batch of entries that they (and possibly other people) are currently working on. Obviously, I no longer want any other users to add, edit, or delete entries in that bat...
I'm going to guess that the answer is no based on the below error message (and this Google result), but is there anyway to perform a cross-database query using Postgres? databaseA=# select * from databaseB.public.someTableName; ERROR: cross-database references are not implemented: "databaseB.public.someTableName" I'm working with so...
I'm trying to perform a SQL query through a linked SSAS server. The initial query works fine: SELECT "Ugly OLAP name" as "Value" FROM OpenQuery( OLAP, 'OLAP Query') But if I try to add: WHERE "Value" > 0 I get an error Invalid column name 'Value' Any ideas what I might be doing wrong? So the problem was that the order in...
What's the best way to delete all rows from a table in sql but to keep n number of rows on the top? ...
What is the best way to determine duplicate records in a SQL Server table? For instance, I want to find the last duplicate email received in a table (table has primary key, receiveddate and email fields). Sample data: 1 01/01/2008 [email protected] 2 02/01/2008 [email protected] 3 01/12/2008 [email protected] ...
INSERT INTO tblExcel (ename, position, phone, email) VALUES ('Burton, Andrew', 'Web Developer / Network Assistant', '876-9259', '[email protected]') I've got an Access table that has five fields: id, ename, position, phone, and email...each one is plain text field with 50 characters, save for position which is 255 and id which is an aut...
I'm looking to convert a SQL like statement on the fly to the equivalent regex i.e. LIKE '%this%' LIKE 'Sm_th' LIKE '[C-P]arsen' What's the best approach to doing this? P.S. I'm looking to do this on the .Net Framework (C#). ...