Is this even possible in SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is this even possible in SQL?

Author

Topic

poolmwv
Starting Member

12 Posts

Posted - 2007-10-29 : 12:08:16

I have 4 or 5 tables that are involved:

Asset_Update is 22 columns across and each record may or may not have data in a particular field, eg:

Ser_No, Model, Dev_Type, Asset_Tag, Group, Vendor, PO_Number, etc.
1234 , , PC , 12345 , IT , DELL , etc.
5678 , 5300 , Printer , 67890 , , Lexmark, etc.

About half a dozen of those fields will go directly into the ASSET table. SER_No, Model, Dev_Type, Asset_Tag

The others will go into ASSET_CUSTOM_FIELDS, e.g.

ID_FIELD ID_ASSET DATA
6 17591 ITSD.H/W
7 17591 OUTSERVICE
9 17591 DELL
141 17591 WARRPER
147 17591 DSS
138 17591 DELL
136 17591 1234
142 17591 MAINTPO
145 17591 \\SOMELINK
140 17591 PO NUMBER
46 17591 Free form text
17 17591 SVCAUTH
143 17591 MAINTINT
139 17591 DELL
137 17591 OTHER

These Custom fields are defined in yet another table ID_FIELD 6 = GroupID, ID_FIELD 7 = CompStatus. A join to that field would be nice to use the names of the custom fields but not essential. Or maybe it would be essential once you get down to the actual problem of what I want to do.

No sweat, I have everything done to update the ASSET and ASSET_CUSTOM_FIELD tables.

Now, however, I want to update the ASSET_AUDIT_HISTORY.

So if something is different between ASSET_UPDATE and ASSET or ASSET_CUSTOM_FIELDS, I want each non-null field in ASSET_UPDATE concatenated to make a single record in the ASSET_AUDIT_HISTORY table.

So in the two fake records above, I'd want a record in ASSET_AUDIT_HISTORY that says what field was changed -- if they were different. Let's say the Serial Number and Device Type were the same in the ASSET and ASSET_UPDATE Tables.

I'll be inserting a new history record with the required links to the other tables. But how can I make a string to insert that is essentially:

Asset Tag changed from 23456 to 12345 CRLF Custom Field Group Changed from to IT CRLF Custom Field Vendor changed from IBM to DELL

Model changed from 5200 to 5300, Asset Tag changed from to 67890 Custom Field Vendor changed from IBM to Lexmark

(notice how in record 1 Custom Field Group Changed from to IT and in record 2 Asset Tag changed from to 67890 because there were null values in ASSET or nothing found in ASSET_CUSTOM_FIELDS)

If something could be done purely in SQL, that would be great. Right now the only way I can think of doing it is to get the data, parse it and create sql scripts from another language, like Object-REXX or Visual Basic.

I'm not an idiot, but I play one on the net.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 12:56:25

If this is a one-time-deal you can do an


INSERT INTO AuditTable(Col1, Col2, ..., AuditReason)
SELECT *
FROM
(
    SELECT Col1, Col2, ..., 
        CASE [Vendor] IS NOT NULL AND NOT EXISTS
        (
            SELECT * 
            FROM ASSET_CUSTOM_FIELD AS ACF 
            WHERE ACF.MyPK = A.MyPK 
                AND ACF.FieldName = 'Vendor' 
                AND ACF.FieldValue = A.[Vendor]
        ) THEN 'Vendor is different' ELSE '' END AS AuditReason
        + CASE ... THEN '... next field difference description... ' ELSE '' END
        + ...
    FROM ASSET AS A
) AS X
WHERE AuditReason <> ''

otherwise you need a Trigger so that an Audit Record is made whenever records change

Kristen

poolmwv
Starting Member

12 Posts

Posted - 2007-10-30 : 13:56:46

Thanks, Kristen! This gives me something to chew on. I can't tell off hand if it will exactly work, but it gives me enough of clue that I should be able to hammer out exactly what I need.

The two points that have me confused:

1. I wasn't exactly sure what that "AS X" was referring to.

2. It looks like it's updating the ASSET Table from the ASSET_CUSTOM_FIELD table, but what I'm really needing to do is update the ASSET table AND the ASSET_CUSTOM_FIELD table from Asset_Update table.

See, our application makes it really easy to add fields like PO_Number or Warranty_Period, but puts all of the data from those into the "ASSET_CUSTOM_FIELDS" table. Each piece of data is its own record. They are linked to the asset table by ID_Asset:

(The following three records are all associated with the piece of hardware that has the unique key of 17591)
ID_FIELD ID_ASSET DATA
6 17591 ITSD.H/W (This is the Group field)
7 17591 OUTSERVICE (This is the CompStatus field)
9 17591 DELL (This is the ServiceID field)

The Custome Fields are defined in another table called something like CUSTOM_FIELD DEFS (there are other columns, but this gives you an idea):
ID LABEL
6 Group ID
7 Comp Status
9 Service ID

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 14:25:03

1. "X" is an Alias name for the inner sub-query. The results of the inner sub-query will appear to be Rows and Columns for a pseudo-table called "A"

2. Yeah, that doesn't surprise me! I didn't pay too much attention to which of your tables was which

so you'll need to swap those round as appropriate.

Kristen

poolmwv
Starting Member

12 Posts

Posted - 2007-11-07 : 17:14:54

I'm still working on this. Unfortunately, I am trying to learn it all on my own, as there is no one to ask. There's one contractor here who tried to help, but I can't understand his English very well. Between the two of us we came up with the following. He was saying I would have to use a cursor and loop through the table supplying a variable with the serial number in order for it to work. So I thought I would first see if it would work if I just hard coded a Serial Number that I knew was in there. But I'm getting the error, Msg 102, Level 15, State 1, Server SRV94SQ, Line 15 Incorrect syntax near ')'.

Here's what I have so far, just trying to go against the asset table and not worrying about the custom fields for now:

SELECT RS1.AuditReason1 + RS2.AuditReason2
FROM

(SELECT
CASE
WHEN AU.DevModel IS NOT NULL AND
AU.DevModel <> A.MODEL
THEN 'Changed Model From: ' + A.MODEL + ' To: ' + AU.DevModel + CHAR(10) + CHAR(13)
ELSE ''
END AS AuditReason1

FROM ASSET_UPDATE AS AU
JOIN ASSET AS A
ON A.SERIAL_NUMBER = AU.SerNum
WHERE AU.SerNum = "DX201") RS1,

(SELECT
CASE
WHEN AU.ASSET_NUMBER IS NOT NULL AND
AU.ASSET_NUMBER <> A.TAG_NUMBER
THEN 'Changed Asset Tag From: ' + A.TAG_NUMBER + ' To: ' + AU.ASSET_NUMBER + CHAR(10) + CHAR(13)
ELSE ''
END AS AuditReason2

FROM ASSET_UPDATE AS AU
JOIN ASSET AS A
ON A.SERIAL_NUMBER = AU.SerNum
WHERE AU.SerNum = "DX201") RS2)

I'm not an idiot, but I play one on the net.

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 00:22:44

"Msg 102, Level 15, State 1, Server SRV94SQ, Line 15 Incorrect syntax near ')'."

Remove the final parenthesis after RS2

WHERE AU.SerNum = "DX201") RS2)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources