I want to add two numbers together but when one of those numbers is null then the result is null. Is there a way around this. I could simply do it in the code but I would rather have it done in the query. This is a oracle database. The table structure hours_t type craft regular overtime A 1 5 ...
Hello everyone, Working on improving performance of our decision center, one of the bottlenecks we identify is the DB. So I wonder, does oracle compiles an Execution Plan for it's views? Lets hypothetically assume I have a defined query being used 10000 times during a request. The query looks something like : select A, B, C from aTb...
How can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005) ...
I want to extract FROM codes WHERE FieldName='ContactMethod' and IsNull(Deactived,'') != 'T' from SELECT FieldDescription,FieldValue FROM codes WHERE FieldName='ContactMethod' and IsNull(Deactived,'') != 'T' order by fielddescription using a regular expression. I have a regex like this: \FROM.*\order which extracts FROM cod...
Is there a simple way to convert a date stored in a varchar in the format mon-yy (e.g. "Feb-09") to a datetime which will convert any invalid values to null. I'm currently using string manipulation combined with a case statement but it's rather cumbersome. ...
I want to compute a checksum of all of the values of a column in aggregate. In other words, I want to do some equivalent of md5(group_concat(some_column)) The problem with this approach is: It's inefficient. It has to concat all of the values of the column as a string in some temporary storage before passing it to the md5 function...
SELECT t1.status, t3.guid, t3.objectID FROM Table1 t1, Table2 t2, Table3 t3 WHERE t2.ID = t3.ID AND t1.ID = t2.ID AND t3.Guid IN ('', '', '') How can I convert this to an update statement where I set the t1.status = 1? ...
In SQL Server this inserts 100 records, from the Customers table into tmpFerdeen :- SELECT top(100)* INTO tmpFerdeen FROM Customers Is it possible to do a SELECT INTO across a UNION ALL SELECT :- SELECT top(100)* FROM Customers UNION All SELECT top(100)* FROM CustomerEurope UNION All SELECT top(100)* FROM CustomerAsia UNION All S...
I usually use SQLDeveloper to browse the database, but I couldn't make it work with hsqldb and I don't know which tables are already created... I guess it's a vendor specific question, and not plain sql, but the point is: how can I see the tables so I can drop / alter them? ...
I have an order by clause that looks like: ( user_id <> ? ), rating DESC, title Where ? is replaced with the current user's id. On postgresql this gives me the ordering I'm looking for i.e. by current user, then highest rating, then title (alphabetically). However on MySQL I get an unclear order current user is neither first nor las...
I am lost in a big database and I am not able to find where the data I get comes from. I was wondering if it is possible with SQL Server 2005 to search for a string in an all the tables, rows and columns of a DB? Does anybody has an idea if it is possible and how? Thanks! ...
I have a table that stores formulary drug information and needs to be updated daily from a central formulary. The temp table is identical to the drug table. The temp table data could be identical (and on most days will be) to the main table or it could have updated rows or new rows. I have a stored procedure to update the main table, b...
I'm trying to write an HQL query to select objects which contain an object in a child collection. Example: Contest Object ContestID ContestName RequiredCountries -> one to many collection of Country objects Country Object CountryCode CountryName The sql equivalent of what i want: SELECT * FROM CONTEST C WHERE C.CONTESTID IN(SELE...
I need to find a select statement that will return either a record that matches my input exactly, or the closest match if an exact match is not found. Here is my select statement so far. SELECT * FROM [myTable] WHERE Name = 'Test' AND Size = 2 AND PType = 'p' ORDER BY Area DESC What I need to do is find the closest match to the 'A...
I am trying to make a query that pulls out all Tickets for a particular company. Within that table will be a column named [Repeat] What I need the query to do is check to see if there are any other rows that have a matching Circuit_ID within the last 30days of that ticket. "SELECT [MAIN_TICKET_ID], [CompID], [ActMTTR], [ActOTR], [DtCr]...
The query: SELECT tbl1.* FROM tbl1 JOIN tbl2 ON (tbl1.t1_pk = tbl2.t2_fk_t1_pk AND tbl2.t2_strt_dt <= sysdate AND tbl2.t2_end_dt >= sysdate) JOIN tbl3 on (tbl3.t3_pk = tbl2.t2_fk_t3_pk AND tbl3.t3_lkup_1 = 2577304 AND tbl3.t3_lkup_2 = 1220833) where tbl2.t2_lkup_1 = 1020000002981587; Facts: Oracle XE tbl1.t1_pk is a pri...
I want to select some rows from a table. Along with the normal columns that I get back from the query result, I also need to append some additional field in the result set. I am exporting the table in to the csv file. The output file will have some extra fields along with the normal column values that were returned from the query. For...
I need to add a row dynamically in SQL after the Marketer number changes with the header "Marketer Total" that should add only the "Total" column. For example, after the last row of Marketer 22 row, there should be "Marketer Total" and then under the Total column should be 1804. The same should occur after the last row of Marketer 500....
What can you do in SQL Server 2008 Standard Edition that you cannot do in SQL Server 2008 Web Edition? I've seen the Microsoft feature lists, but I am wondering from a very practicle standpoint where I am going to run into limitations. ...
I'm using locks and transactions a bit like a VBA excel programmer trying to write a multithreaded c++ server for the first time... I've tried to ask my coworkers for advice, but while we're all quite good (or so we think) at designing complex databases, writing fast and efficient queries, using index and constraints when it's needed, a...