Is one of my DB-fields NOT NULL?

Question

I have to deal with a table where there is a set of fields each followed by a second field that will hold a suggested new value until this change is confirmed.

It looks a little like this:

refID    field1    newField1    field2    newField2   ...

refID is an ID value that links to a master table. One row in the master table can have n rows in my detail table. The data-types include ints, strings and dateTimes.

Now, i'm looking to have a query that tells me, given a refID, if there are any suggested changes in the detail table.

I was playing around a little with some UNION selects, with COALESCE() and ISNULL() ... but all those attempts looked a little weird at best. The DB is MS-SQL-Server 2005.

For clarification of my problem:

--this is a simplification of the details table in question
CREATE TABLE [dbo].[TEST_TABLE](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [refID] [int] NOT NULL,
    [firstName] [varchar](50) NULL,
    [newFirstName] [varchar](50) NULL,
    [lastName] [varchar](50) NULL,
    [newLastName] [varchar](50) NULL
)

--here we insert a detail row ... one of many that might exist for the master table (e.g. data about the company)
insert into TEST_TABLE(refID, firstName, lastName) values(666, 'Bill', 'Ballmer')
--this is what happens when a user saves a suggested change
update TEST_TABLE SET newLastName = 'Gates' where ID = 1
--and this is what happens when this suggestion is accepted by a second user
update TEST_TABLE set lastName=newLastName, newLastName = NULL where ID = 1

Answer 1

Is this schema already defined and in production? If not I would strongly recommend having a separate 'changes' table of some description - maybe use fieldname, fieldvalue where fieldvalue is a sql_variant.

I don't think your existing structure is going to look nice when values are 'accepted' (null'd I assume) especially as you won't retain any auditing history with this approach.

Answer 2

Can't test this, but perhaps:

select (field1 is not null and field2 is not null) as ChangesMade where refID = @id

Answer 3

This is the cleanest solution I can think of off the top of my head. You'd need to repeat the logic for each data element (col1, col2, etc):

DECLARE @RefID int, @Changes bit

SET @Changes = 0 --No changes by default

SET @RefID = 42 --Your RefID

IF EXISTS(SELECT * FROM MyDetailTable
          WHERE RefID = @RefID
          AND (
          (Col1 IS NULL AND NewCol1 IS NOT NULL)
          OR 
          (Col1 IS NOT NULL AND NewCol1 IS NULL)
          OR
          (Col1 <> Col2)
          ))
   SET @Changes = 1

Answer 4

I modified randolphos solution.

 select 
        refID ,
        case when 
         newField1 is not null or
         newField2 is not null or
         ...
        then 1 else 0 end  haschanged
    from myTable
    where refID = @refID

Update: basically what Aron Aalton said in another output format.

Answer 5

Here's a simple query:

    SELECT TOP 1 1 as found
      FROM [dbo].[TEST_TABLE] t
     WHERE COALESCE(t.newFirstName,t.newLastName) IS NOT NULL
       AND t.refID = 1

This query will return a single row if there are any proposed changes for a given refID (based on the example in your question.)

For your actual table of course, you'd need to list each of the 'newValue' columns as arguments in the COALESCE function. (In the coalesce list, I recommend explicitly casting any non-VARCHAR to VARCHAR, just to make it clear that every expression in the list is of the same data type.

If you prefer to use a CASE expression rather than COALESCE:

    SELECT TOP 1 1 as found
      FROM [dbo].[TEST_TABLE] t
     WHERE CASE 
           WHEN t.newFirstName IS NOT NULL THEN 1
           WHEN t.newLastName  IS NOT NULL THEN 1
           ELSE NULL
           END IS NOT NULL
       AND t.refID = 1