I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and the other is indicating the absence of that same information. Feel free to speculate, but please indicate if that's the case. If there's an...
My SQL is a bit rusty -- is there a SQL way to project an input table that looks something like this: Name SlotValue Slots ---- --------- ----- ABC 3 1 ABC 4 2 ABC 6 5 I...
This might be a little hard to explain, but I will try. I want to display a list of categories (stored in 1 table), and number of domains associated with each category (stored in another table). The monkey wrench in this case is that each domain has a set of records associated with it (which are stored in a 3rd table). I only want to ...
What do I have to consider in database design for a new application which should be able to support the most common relational database systems (SQL Server, MySQL, Oracle, PostgreSQL ...)? Is it even worth the effort? What are the pitfalls? ...
I know a role name and want to find all users in this role. How do I acheive this in SQL Server 2000 (in the SQL script, not in Management Studio or other tool)? ...
Although using SQL FOR XML EXPLICIT is cumbersome, I find myself using it often. Since it allows full control over the generated XML document. My problem is with perfomance, sometimes it would take more than 5 minutes to generate an XML document describing some financial data (invoices, bills .. etc.) for just one month! So I was looki...
I need to insert some data into a table in Oracle. The only problem is one of the fields is a timestamp(6) type and it is required data. I don't care about what actually goes in here I just need to get the right syntax for an entry so that the database will accept it. I'm using the gui web client to enter data however I don't mind usi...
I'm working on scripts that apply database schema updates. I've setup all my SQL update scripts using start transaction/commit. I pass these scripts to psql on the command line. I now need to apply multiple scripts at the same time, and in one transaction. So far the only solution I've come up with is to remove the start transaction/com...
For a web application I want to build a WHERE clause AND submit it to the server. There I will append it to a query. the clause will be something like LASTNAME LIKE 'Pep%' AND (DOB BETWEEN '19600101' AND '19601231 OR SALARY<35000) Can you propose a regular expression to validate the clause before submitting it to SQL Server? (Yes, of ...
Hi, I read an excel sheet into a datagrid.From there , I have managed to read the grid's rows into a DataTable object.The DataTable object has data because when I make equal a grid's datasource to that table object , the grid is populated. My Problem : I want to use the table object and manipulate its values using SQL server,(i.e. I wan...
At my work everyone has sql snippets that they use to answer questions. Some are specific to a customer, while some are generic for a given database. I want to consolidate those queries into a library/repository that can be accessed by anyone on the team. The requirements would be: Accessible Searchable Tagable (multiple tags allowed p...
This is probably a simple answer but I can't find it. I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero. I could do this on the code side but thought it would be best to enforce it on the table. Thanks! I was in error with my last comment all i...
I'm getting odd results from a MySQL SELECT query involving a LEFT JOIN, and I can't understand whether my understanding of LEFT JOIN is wrong or whether I'm seeing a genuinely odd behaviour. I have a two tables with a many-to-one relationship: For every record in table 1 there are 0 or more records in table 2. I want to select all the ...
This case arises in a real-life situation where invalid data was in (and continuing to come into) an Oracle database which is extracted into a data processing system in Focus. Focus would choke and die on some rows with invalid time portions. The Oracle DBA would then go and copy the datetime into the affected column from a good column...
I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to si...
During my work with databases i noticed that i write query strings and in this strings i have to put several restrictions in the where-clause from a list/array/collection. Should looks like this: select * from customer where customer.id in (34, 26, ..., 2); You can simplify this by reducing this to the question that you have collecti...
I have a query that has a list of base values and a list of language values. Each value has a key that matches to the other. The base values are stored in one table and the language values in another. My problem is that I need to get all matching base values removed from the QUERY except for one. Then I export that query into an exce...
How do I query an Oracle database to display the names of all tables in it? ...
I have a database with DateTime fields that are currently stored in local time. An upcoming project will require all these dates to be converted to universal time. Rather than writing a c# app to convert these times to universal time, I'd rather use available sqlserver/sql features to accurately convert these dates to universal time so...
I'm writing SQL (for Oracle) like: INSERT INTO Schema1.tableA SELECT * FROM Schema2.tableA; where Schema1.tableA and Schema2.tableA have the same columns. However, it seems like this is unsafe, since the order of the columns coming back in the SELECT is undefined. What I should be doing is: INSERT INTO Schema1.tableA (col1, col2, ....