I have a table of reports that has these relevant fields: user_id (int 11) submitted_date (datetime) approved_flag (shortint 1) There are multiple rows per user_id, some with approved_flag = 0 and some with approved_flag = 1 and each with a unique submitted_date. I need to get a count of the approved and unapproved reports. But I on...
Hi there, Does anyone know if there is a quicker way of editing a record that has foreign keys in a table (in sql server).. i will explain.. i have approx 5 tables that have there own ID but are linked together using a foreign key... Hence i needed to change the foreign key (the contract number in my case), but i had to copy each recor...
Hi, I'm working on a query which returns numeric values (currency). Some of the values are whole numbers and are being displayed as 3, 5, 2 etc whilst other numbers are coming up like 2.52, 4.50 etc. How can I force oracle to always show the decimal places? Thanks ...
I'm writing a daemon to monitor creation of new objects, which adds rows to a database table when it detects new things. We'll call the objects widgets. The code flow is approximately this: 1: every so often: 2: find newest N widgets (from external source) 3: foreach widget 4: if( widget not yet in database ) 5: add rows f...
According to the sqlite3 documentation, The COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the bu...
Why do Scalar-valued functions seem to cause queries to run cumulatively slower the more times in succession that they are used? I have this table that was built with data purchased from a 3rd party. I've trimmed out some stuff to make this post shorter... but just so you get the idea of how things are setup. CREATE TABLE [dbo].[GIS_L...
Hi, I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it. I have 3 tables a little like this DVD ID | NAME 1 | 1 2 | 1 3 | 2 4 | 3 COPY ID | DVDID 1 | 1 2 | 1 3 | 2 4 | 3 5 | 1 LOAN ID | DVDID |...
I have data in an MYSQL database that looks like this: Project Date Time A 2009-01-01 15 A 2009-01-02 10 B 2009-01-02 30 A 2009-01-09 15 C 2009-01-07 5 I would like to produce output from this data like this: Date Project A Time Project B Time Project C ...
I always thought an SQL compiler would break but apparently nesing can nearly be infinite. Is this code to be trashed immediately or is there some glimmer of hope that something like this can function? This query doesn't really belong to me so I cannot post it... However let's just pretend it is this one: [SELECT /*+ NOPARALLEL bypass_...
Is their any way to group by all the columns of a table without specifying the column names like select * from table group by * Thanks ...
A data import was done from an access database and there was no validation on the email address field. Does anyone have an sql script that can return a list of invalid email addresses (missing @, etc). Thanks! ...
Can you please forward me the answer for my oracle project? I have two different tables, invoice and customer. In the invoice table I already have a column called date and in the customer table I don't have the date column and I already created. I don't know how to bring the date data from the invoice table. Can anyone answer this quest...
I have a database that needs to be able to scale up to billions of entries or rows. Can this many rows be supported per single table? Is it advisable? Would a single table be split over several clusters if used in a NDBCLUSTER. Other load balancing techniques? What are some advisable methods of deploying such a database? What are be...
I need an elegant way to store dynamic arrays (basically spreadsheets without all the functionality) of various sizes (both x and y), mostly being used as ENUMs, lists, lookup data, price sheets, that sort of thing. Multi-lingual would be a great bonus. Speed of the essence. Here's an example of a typical "sheet" ; | 1 | ...
Hi, I'm trying to use a WITH clause in a query but keep getting the message ORA-00942: table or view does not exist I've tried to create a simple query just as an example here: WITH test AS ( SELECT COUNT(Customer_ID) FROM Customer ) SELECT * FROM test; But even this dosen't work, it just gives the message: SELE...
Hi, I have a table USERS with the following fields date(datetime) email(varchar) provider(int) event(int) I am looking for how many records there are with the same email, which occur in a specific month with a specific provider. like for provider= x and month = y i want email occurs [email protected] 5 [email protected]...
I have a stored procedure that executes much faster from Sql Server Management Studio (2 seconds) than when run with System.Data.SqlClient.SqlCommand (times out after 2 minutes). What could be the reason for this? Details: In Sql Server Management Studio this runs in 2 seconds (on production database): EXEC sp_Stat @DepartmentID...
Does anyone know what the complexity is for the SQL LIKE operator for the most popular databases? ...
Hi all, I am trying to perform what I can only describe as the reverse version of IN() using the Nhibernate Criteria.. Rather than saying the value stored in the property is in the list/collection X I want to say, this value X (an ID) is in the list which is a property of the object. Any help appreciated, I can try to explain better...
I want to select the top N rows of a table, ranked by how high the number in one of their columns is. I already have: SELECT * FROM movie ORDER BY worldwide_gross DESC; How can I get the first twenty? If it makes any difference I'm using MySQL. Cheers! ...