2

I've got the following scenario:

I've got

  • a table of assets with standard columns
  • a table of hardware assets with extension columns
  • a history table where all changes to an asset are stored with asset ID, field name, date, new value
  • a contact table

I want to set a field in hardware assets called "first_usage" to the earliest date of change of the contact field (called "resource_contact") when the new value was a contact with a user-ID (i.e. no technical user or group).

One difficulty was that no ID is stored in the "new value" but a combined string of first, middle and last name (in case change of contact).

My first try of a query looks like this:

UPDATE  dbo.asset_z_hardware 
SET     first_usage = (
        select min(history.com_dt) 
from    dbo.nr_com              history, 
        dbo.ca_contact          contact
where   history.attr_name = 'resource_contact' AND      
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' + 
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
        )
)

This seemed to work quite OK, though only some assets were updated with a date (probably I need to check the string-comparison and contacts which changed meanwhile).

But then I realized that I only should update only active assets! The "inactive" flag is in the main asset table called "ca_owned_resource". Unfortunately, after adding the new condition, the first usage values were all set to the same date!!! How come? I did not find my mistake.

This is the second query with "inactive" flag and link between hardware and main asset:

UPDATE  dbo.asset_z_hardware 
SET     first_usage = (
        select min(history.com_dt) 
from    dbo.nr_com              history, 
        dbo.ca_contact          contact, 
        dbo.ca_owned_resource   res
where   history.attr_name = 'resource_contact' AND
        res.inactive = 0 AND res.own_resource_uuid = own_resource_uuid AND
        history.com_par_id = own_resource_uuid AND
        contact.userid IS NOT NULL AND
        (history.new_value = '' + 
            case when contact.last_name     is not null then        contact.last_name   end +
            case when contact.first_name    is not null then ', ' + contact.first_name  end +
            case when contact.middle_name   is not null then ' '  + contact.middle_name end
         )      
 )

I'd appreciate any help and suggestions for the only-one-date-mistake, also for improving the rest, if any ideas.

  • Try with explicit JOIN syntax and also reference all the columns in the where clause with the alias prefix and see if your results change. Also, if I were doing the testing, I would run the select version of the query before the UPDATE... – Jacob H Oct 18 at 13:52
  • Alternately, run in a transaction that you roll back, and use the OUTPUT clause to see what would be changed. – user1443098 Oct 18 at 14:51
  • user1443098, Thanks for the hint with transaction. If it was in live or pre-live system, this would be a good idea! Fortunately I've got a separate dev system. But I'll keep that in mind! --- Jacob H, the join would not bring a difference, maybe in performance or readability, I already marked the right answer. Thanks nevertheless! – Jana Oct 29 at 12:27
1

Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended.

Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if there’s one there.

  • 1
    Yeah, you are right. First I thought it would be unneccessary, cause "own_resource-uuid" is a FK from asset_z_hardware to res and thus it's the same value. But I was wrong, this was the cause of having always the same date! Thanx! – Jana Oct 29 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.