I have a database column I simply need to check to see if a value is there. DataTable dt = masterDataSet.Tables["Settings"]; DataColumn SETTINGS = dt.Columns["DEFAULTSETTINGS"]; I just need to iterate over the values in this column to see if a value exists. Help ...
Is there a Hibernate configuration (hopefully an annotation on a classes mapped @Column field) that would let me sort a collection of entities associated with the loaded entity by a given column of that entity when a session.load(Entity.class, Id) is called? For example if I had an EntityA that contained a OneToMany association to an En...
I'm migrating data between tables in Access 2003. In the old table, the date was stored as a text field in the format YYYYMMDD. I want to store the field as a datetime in the new table. I've tried using CDate() in my SQL statement, but it just displays as #Error in the results. What am I doing wrong? ...
I have a mysql database of entries with dates. So what I want is to show all the dates in my database and then under each date, I want to show all the entries in the database entered on the specefic date. I am thinking of two loops but I don't know how to write the condition to display all the dates in my database befo...
Hi, When creating indexes for an SQL table,if i had an index on 2 columns in the table and i changed the index to be on 4 columns in the table, what would be a reasonable increase the time taken to save say 1 million rows to expect? I know that the answer to this question will vary depending on a lot of factors, such as foreign keys, ...
When updating a row, I want to have a built-in check to do some bounds checking. Most languages have a MAX() function to return the maximum of the arguments passed, but MySQL seems to use MAX() for something else. For example: UPDATE person SET dollars = MAX(0, dollars-20) WHERE id=1 I want to subtract 20 dollars from person id 1, b...
Hi, How to get start date and end dates in a query from database. Thanks, Lico ...
Say I am storing addresses in a DB table, in this fairly common break down: address_street_line_1, address_street_line_2, address_city, address_state, address_zip, address_country_id (Note: I have read the questions on splitting down further, street type, house number, etc. and for this application I think it would unnecessarily compl...
I'm trying to flip a bit field in SQL Server using an update query, that is, I want to make all the 0's into 1's and vice versa. What's the most elegant solution? There doesn't seem to be a bitwise NOT operator in TSQL (unless I'm missing something obvious) and I haven't been able to find any other way of performing the update. ...
I have a simple table, something like: int id, date created_at, date updated_at. I'd like to order the rows so that any rows that have an updated_at will be sorted by that, and any that don't will be sorted by the created_at. The problem is that something like: SELECT * FROM table ORDER BY updated_at, created_at doesn't work. I've bee...
What is the purpose of naming your constraints (unique, primary key, foreign key)? Say I have a table which is using natural keys as a primary key: CREATE TABLE Order ( LoginName VARCHAR(50) NOT NULL, ProductName VARCHAR(50) NOT NULL, NumberOrdered INT NOT NULL, OrderDateTime DATETIME ...
Hi how can i get only unly unique depts from the below example? Dept Id Created Date 06013cd7-2224-4220-b048-a54bbd1ff403 2009-09-08 17:36:11.293 06013cd7-2224-4220-b048-a54bbd1ff403 2009-09-08 17:41:54.857 5e29bd98-04ba-452d-bfcd-caa63ab9018b 2009-09-08 17:20...
I have two stored procedures, one of which is called a lot and is thus very simple and executes well. Additionally, these actually work very nicely in paralell even though they are serializable. However I have a second query that is only called in certain circumstances which needs to do many more checks before touching the same tables....
Trying to create Database as follows: USE Master GO IF NOT EXISTS(SELECT [Name] FROM sys.databases WHERE [name] = 'QAudit') CREATE DATABASE [QAudit] ON PRIMARY ( NAME = N'QAuditData', FILENAME = N'<filePath,nvarchar(300),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\>QAuditData.mdf' , SIZE = 921600KB , FILEGROW...
This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, EmployerCode VARCHAR(20) N...
I have a select in which I do a "group by". One of the columns is an enum, and I want to select the biggest value for each group (i.e the one with the largest index in the enum). I can do select MAX(enum_column+0) as enum_index to get the the largest index in the group, but how can I turn the enum index back to the enum item? Exam...
Lets say I have a database table called "Scrape" possibly setup like: UserID (int) UserName (varchar) Wins (int) Losses (int) ScrapeDate (datetime) I'm trying to be able to rank my users based on their Wins/Loss ratio. However, each week I'll be scraping for new data on the users and making another entry in the Scrape table...
I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*: user_id | file | permissions -----------+--------+--------------- 1 | a.txt | 6 ( <-- 6 = 4 + 2 = read + write) 1 | b.txt | 4 ...
As a followup to this two questions and its answers Question 1 and Question 2. What gives better performance on Oracle using operator in, or or union? I think on a table with >1 mio rows with 10-500 rows per distinct value of y. select * from table where y in (1,2,3) or select * from table where (y = 1 or y = 2 or y = 3) or selec...
There are already a couple of questions on finding cycles, but I did not find a solution in SQL (MSSQL preferred). The tables would be Node (NodeID INT) and Edge (EdgeID INT, NodeID1 INT, NodeID2 INT) What would be a well-performing solution to find cycles in a directed graph? ...