In Oracle 10g I have a table that holds timestamps showing how long certain operations took. It has two timestamp fields: starttime and endtime. I want to find averages of the durations given by these timestamps. I try: select avg(endtime-starttime) from timings; But get: SQL Error: ORA-00932: inconsistent datatypes: expected ...
Writing my first Linq application, and I'm trying to find the best way to do the following: I want to load the entire employees table at once to populate the cache (used for form autocomplete). I can do - var query = from employee in db.Employees select employee; foreach (Employee e in query) { ... } But since this is deferred...
Are there any nvl() equivalent functions in SQL? Or something close enough to be used in the same way in certain scenarios? UPDATE: no if statementsno case statementsno isnullno coalesce select nvl (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581; (expression) SODIUFOSDIUFSDOIFUDSF 1 row(s) retrieved. select isnul...
I have an Informix stored procedure that returns two columns and multiple rows. I can use "EXECUTE FUNCTION curr_sess(2009,'SP')" fine, but how do I get the results into a temp table. EDIT: We are on version 10.00.HC5 Testing Jonathan Leffler's idea didn't work. EXECUTE FUNCTION curr_sess(2009,'SP') works fine. Then I did CREATE...
Hello, I have a Linq to SQL query that was working just fine with SQL Server 2005 but, I have to deploy the web app with a SQL Server 2000 and, when executing that query, I get his error: "System.Data.SqlClient.SqlException: The column prefix 't0' does not match with a table name or alias name used in the query." I have more queries b...
I have a large query (not written by me, but I'm making some modifications). One thing that kind of bugs me is that I've got the same COALESCE function in about four places. Is there a way to factor that out, possibly by joining with a select from DUAL? Is there a performance benefit to factoring it out? Here is the query slightly bo...
Is there any way to see SQL UDF body in DB2 control center? ...
I have two tables with the same columns tbl_source (ID, Title) tbl_dest (ID, Title) I want to update tbl_dest titles from the tbl_source where the ids in dest and source match. However, I don't want to update the dest title if the source title is null (or blank). I've got this: UPDATE tbl_dest SET tbl_dest.Title = ...
I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below. DECLARE @counter int; SET @counter = 1; DECLARE @tempID varchar(50); SET @tempID = ''; DECLARE @tempCat varchar(255); SET @tempCat = ''; DECLARE @tempCatString varchar(5000); SET @t...
Does anyone have any experience that indicates what kind of performance hit a developer could expect by choosing to use an ORM (in Django, RoR, SQLAlechemy, etc) over SQL and hand-designed databases? I imagine there are complicating issues, including whether specifying a database within the constraints of an ORM increases or decreases th...
I am working with a set of what is essentially Attribute/Value pairs (there's actually quite a bit more to this, but I'm simplifying for the sake of this question). Effectively you can think of the tables as such: Entities (EntityID,AttributeName,AttributeValue) PK=EntityID,AttributeName Targets (TargetID,AttributeName,AttributeValue) P...
I have a very very simple SQL string that MS Access doesn't seem to like. Select Top 5 * From news_table Order By news_date This is returning ALL rows from the news_table not just the Top 5. I thought this was a simple SQL statement, apparently it is not. Any fixes or idea as to why this is not working? Thanks! ...
I want to get a list of result sets and columns that i can expect from a SP. I have been able to get at the parameters, script... but i don't know where to get at the result sets and column names. using Microsoft.SqlServer.Management.Smo; using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mydbconn"].C...
I am trying to build an SQL Statement for the following search scenario: I have trying to return all of the columns for an individual record for Table A based on the value of the status column in Table B. Each record in table A can have multiple rows in table B, making it a one to many relationship. The status column is nullable with ...
I am getting JDBC error when I attempt a commit through hibernate to SQL Server Cannot insert explicit value for identity column in table 'Report' when IDENTITY_INSERT is set to OFF I am using mappings generated by netbeans that contain, <class name="orm.generated.Report" table="Report" schema="dbo" catalog="DatabaseName"> <id...
I need to query the database to get the column/field names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field names from the query and nothing else. Please help? Thanks in advance. ...
I am trying to do a nice SQL statement inside a stored procedure. I looked at the issue of seeing the number of days that events happened between two dates. My example is sales orders: for this month, how many days did we have sales orders? Suppose this setup: CREATE TABLE `sandbox`.`orders` ( `year` int, `month` int, `day` int...
I have a simple table of events: event_id | start_time | end_time How do I query the maximum number of simultaneous events? ...
I am trying to find an optimal solution for the following problem: there is a need to design a database (postgres-based), the system of triggers and counters in it, which will form a system of efficiently querying, updating and storing information on 'how much unread comments exist in each article (or blog entry, or smth. similar), that ...
I have this procedure which is inserting records from one table to another. the destination table is having an identity column called LeadId Create Procedure prcInsertPrd As Begin Begin Transaction Declare @Identity int Insert into Temp_ProductsArchive (column1,column2,column3) select (column1,column2,colum...