sql

Is it possible to store '' (empty string) as a non NULL value in the database?

I am using Oracle DB. At the database level, when you set a column value to either NULL or '' (empty string), the fetched value is NULL in both cases. Is it possible to store '' (empty string) as a non NULL value in the database? I execute this UPDATE contacts SET last_name = '' WHERE id = '1001'; commit; SELECT last_name, ID FROM c...

Why isn't index used for this query?

I had a query where an index was not used when I thought it could be, so I reproduced it out of curiosity: Create a test_table with 1.000.000 rows (10 distinct values in col, 500 bytes of data in some_data). CREATE TABLE test_table AS ( SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data FROM dual CONNECT BY ROWNUM <= 100000...

Find records that do not have related records in SQL

I have 2 tables (Orders, OrderItems) that are related based on a column OrderID. I need to find all Orders that do not have any OrderItems. ...

How do I implement this multi-table database design/constraint, normalized?

I have data that kinda looks like this... Elements Class | Synthetic ID (pk) A | 2 A | 3 B | 4 B | 5 C | 6 C | 7 Elements_Xref ID (pk) | Synthetic ID | Real ID (fk) . | 2 | 77-8F <--- A class . | 3 | 30-7D <--- A class . | 6 | 21-2A <--- ...

access report from dynamic crosstab query and vba to "manually" generate reports

I have come across the problem of generating complex access reports (by complex I mean with data processing, variable number of fields, among others). Let me explain in deeper detail some of the things I need to implement: Some fields should not show according to some values in a query If a certain record does not exist, a nice colo...

simple table design question

I'm trying to think ahead a little and avoid myself some extra pain if possible. I've had this problem in past applications and have usually opted for the most verbose approach but would like the opinions of a few others. If you have a basic table such as below, is it wise, and/or more efficient to include a field which includes a calc...

mySQL Table Structure for a User-based website.

I am developing a website that has user profiles, accounts, account settings, inboxes, etc. just like Facebook or LinkedIn and I'm wondering how to set up the mySQL tables for it. Should I create a table for every user for each function (profile, inbox, etc.)? ...

Top X-posts of the past week? SQL/PHP [figured it out]

How can I get the top X posts of the past week? I have two tables: td_table - holds info about the post; vote_table - holds the info about the votes that have been made for the posts. The following gives me the top three posts (those that have most votes for), but it gives me the top three of all times. $query = 'SELECT t.id, t.content,...

When to use an auto-incremented primary key and when not to?

I'm trying to figure out the "best practices" for deciding whether or not to add an auto-incrementing integer as the primary key to a table. Let's say I have a table containing data about the chemical elements. The atomic number of each element is unique and will never change. So rather than using an auto-incrementing integer for each ...

Combining SQL Rows

I've got SQL Compact Database that contains a table of IP Packet Headers. The Table looks like this: Table: PacketHeaders ID SrcAddress SrcPort DestAddress DestPort Bytes 1 10.0.25.1 255 10.0.25.50 500 64 2 10.0.25.50 500 10.0.25.1 255 80 3 10.0.25.50 500 10.0.25.1 255 16 4 ...

Self join of time series data

I need some help with what I think should be a fairly simple self join query. It just needs to combine the matching start time and end times from two records into one record Say I have the following in a table Time Event 08:00 Start 09:00 Stop 10:30 Start 10:45 Stop 11:00 Start 11:15 Start 12:00 Stop 12:30 Stop I want a view like t...

Filtering SQL Rows

Table Schema ID Activate_Date InActivate_Date 1 2009-12-18 10:25:19.470 2010-01-13 08:32:30.130 2 2009-12-18 10:25:19.470 2010-01-13 08:32:30.253 3 2009-12-18 10:25:19.470 2010-01-13 08:32:30.363 4 2009-12-18 10:25:19.470 2010-01-13 08:32:30.583 5 2009-12-18 10:25:19.470 2010-01-13 08:32:...

Question about if-then and when-then in SQL

Greetings, I have a sql fetch that has an id field - think of it as a foreign key. I need to make a desicion based on the value of this id field such that if the value is less then 3100 run a nested fetch form table B. If the value is greater then 3100 run a nested fetch from a table C. The statement looks like this Select a.ID, a.SN,...

How safe are SQL Server 2005 MDF files? (re: security)

We've started using SQL Server 2005 Express for a project and I noticed that every database has it's own MDF/LDF files. Whenever we need to duplicate our database (to another SQL Server) we simply copy these files and then attach them to the database. However, this has me wondering about the security of these files. If somebody from t...

Help finding old SQL tool that rewrote queries

There was this old sql server tool called Lectoneth or something like that, you'd put sql queries in it, and it would rewrite it for you. I think quest bought them out, but I can't find where to download a free copy of that software. Really helps when you have no dba, and have lots of sql queries to rewrite. Thanks Craig ...

Access - Merge two databases with identical structure

I would like to write a query that merges two Access 2000 databases into one. Each has 35 tables with identical fields and mostly unique data. There are some rows which will have the same "primary key" in which case the row from database A should always take precedence over database B. I use quotes around "primary key" because the databa...

creating funtion using newID()

I keep getting this error: Any Ideas? Invalid use of side-effecting or time-dependent operator in 'newid' within a function. I am working with Ms-sql 2005 Create Function [dbo].[GetNewNumber]( ) RETURNS int AS BEGIN Declare @code int set @code = (SELECT CAST(CAST(newid() AS binary(3)) AS int) ) RETURN (@code) END ...

How do distributed transactions behave with multiple connections to the same DB in a threaded environment?

I’m trying to determine the behaviour of multiple database connection in a distributed transaction. I’ve got a long running process which spawns a series of threads and each thread is then responsible for managing its’ DB connections and such. All of this runs inside of the transaction scope and each thread is enlisted in the transacti...

Using a Right Outer Join to Match Records from Two Different Databases

SQL 2005: I am trying to create an outer join that will pull records from two different databases. My objective is to determine which records in database B don't have matching records in database A. When I tried running the query, it returned the error below. I am not sure how to get around this error: 'Tables or functions 'Asset...

Write a T-SQL query that filters records containing NCHAR(2028)

My ultimate goal is to write a sql script that selects data from a particular table where a nvarchar(max) column contains the character NCHAR(2028). But the obvious: select * from tablename where columnname like '%' + NCHAR(2028) + '%' returns all rows. ...