| Author |
 Topic  |
|
|
SteevR
Starting Member
18 Posts |
 Posted - 04/21/2009 : 14:48:52
|
Hi,
This is probably pretty simple, so I aplogize first off for my ineptitude.
I have a trigger based audit table that I want to display in an ASP.Net grid. The problem is some of the collected data is from other tables. For example, empID is an INT ID stored in the audited table, but is actually from tblEmployees.
tblAudit
fieldName oldValue newValue
empID 133 140
taskID 5 7
tblEmployees
empID empName
133 Biff Magoo
140 Joe Boxer
So I want my ouput grid to look like this:
fieldName oldValue newValue
empID Biff Magoo Joe Boxer
taskID someTask someOtherTask
I created a table that shows the relationship, but I dont know for the love of everything pete how to get the proper output or if this is even the best way to do it.
tblRelationShip
PKFieldName fieldValue tableName
empID empName tblEmployees
taskID taskName tblTasks
Any poke in the right direction will be helpful.
TIA,
Steve |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/21/2009 : 15:07:50
|
select A.fieldname, coalesce(B.empname,C.Taskname) As oldvalue
from tblAudit A
left join tblEmployees B on B.empid = A.oldvalue and A.Fieldname = 'empid'
left join tbltasks C on c.taskid = A.oldvalue and A.Fieldname = 'taskid'
Something like this?
------------------------
An infinite universe is the ultimate cartesian product. |
 |
|
|
SteevR
Starting Member
18 Posts |
Posted - 04/21/2009 : 15:22:54
|
Hi cat_jesus,
Thanks, I tried this and couldn't get it to work. Also, all the audited fields are not IDs from another table; there are text fields as well that have no relationship. That is why I created the relationship table. If the fieldnames are in that table, then it gets the value from its specified table.
I hope this makes sense... |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/21/2009 : 15:33:13
|
How many tables total? If it's under a dozen you can use the approach I outlined above. I didn't flesh it completely out, I was just giving you a nudge.
With the left joins you'll have nulls in all the old value and new value columns save for the one that's valid for that audit row.
With coalesce you can use many possible columns and the function will use the first non null value.
for example: coalesce(A.oldvalue,B.oldvalue,C.oldvalue,D.oldvalue,E.oldvalue) as oldvalue
However, if you have a huge number of tables, you'll have to use a different approach. How many records are in tblrelationship?
_________________________________________________________________
An infinite universe is the ultimate cartesian product. |
|
|
|
SteevR
Starting Member
18 Posts |
Posted - 04/21/2009 : 15:58:33
|
Thanks cat_jesus,
There are only about 5 possible fields in the audit table that can be linked.
I tried executing your statement with just one linked field and fails with error: cannot convert 'Apr 3 2009' to data type int.
SELECT A.FieldName, COALESCE (A.NewValue, B.FName) AS newvalue
FROM Audit AS A LEFT OUTER JOIN
tblEmployeePersonal AS B ON B.empID = A.NewValue AND A.FieldName = 'empID'
There are many other fields that oldValue and newValue can be besides the linked IDs; dates and text included.
Thanks,
Steve |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/21/2009 : 17:14:11
|
Yeah you'd have to cast or convert them all to a varchar, which you'd have to do anyway for a report.
No biggie
COALESCE (convert(varchar(25),A.NewValue), convert(varchar(25),B.FName))
or something like that.
An infinite universe is the ultimate cartesian product. |
|
|
|
SteevR
Starting Member
18 Posts |
Posted - 04/22/2009 : 08:38:37
|
Hi cat_jesus,
I think the problem lies in the join as both newValue and oldValue fields are already varchar. Here is an example of the audit table data:
fieldName oldValue newValue
empID 135 140
taskID 5 7
entryDate NULL Apr 3 2009
priorityID NULL 3
estimatedTime 5 Hours 6 Days
Thanks for your patience,
Steve |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/22/2009 : 10:35:38
|
quote:
Originally posted by SteevR
Hi cat_jesus,
I think the problem lies in the join as both newValue and oldValue fields are already varchar. Here is an example of the audit table data:
fieldName oldValue newValue
empID 135 140
taskID 5 7
entryDate NULL Apr 3 2009
priorityID NULL 3
estimatedTime 5 Hours 6 Days
Thanks for your patience,
Steve
Well you would have to cast or convert in the join too. For example :
left join tblEmployees B on convert(varchar(25),B.empid) = A.oldvalue and A.Fieldname = 'empid'
An infinite universe is the ultimate cartesian product. |
Edited by - cat_jesus on 04/22/2009 10:36:14 |
|
|
|
SteevR
Starting Member
18 Posts |
Posted - 04/22/2009 : 13:46:25
|
Hi cat_jesus,
Thanks it works great. The SQL looks quite convoluted when finished but it works great.
Great nudge... although it turned out you had to walk me down the whole path... eep.
Steve |
|
|
| |
Topic |
|
Audit table
