SQL Query Help

[chrisppppp]

SQL Server Management Studio 2005

Hi, i hope i'm posting on the correct forum. I'm trying to create a view using an SQL query on our SQL server. What i need to do is create a view showing ClaimID linked to Key Value, however Claim ID is a number and Key Value is a number stored as text. So my query i've created so far goes as follows:

SELECT dbo.tblClaim.ClaimID, dbo.tblChangeLogHeader.TableName, dbo.tblChangeLogHeader.UndoStatus, dbo.tblChangeLogHeader.IsDraftLog,
dbo.tblChangeLogHeader.KeyValue, dbo.tblChangeLogDetail.NewValue, dbo.tblChangeLogDetail.OldValue, dbo.tblChangeLogDetail.FieldName,
dbo.tblChangeLogHeader.ChangeDate, dbo.tblUsers.UserName, dbo.tblUsers.UserId, dbo.tblDepartment.DepartmentID,
dbo.tblDepartment.DepartmentName
FROM dbo.tblChangeLogHeader INNER JOIN
dbo.tblChangeLogDetail ON dbo.tblChangeLogHeader.rowguid = dbo.tblChangeLogDetail.ParentRowGuid INNER JOIN
dbo.tblUsers ON dbo.tblChangeLogHeader.UserID = dbo.tblUsers.UserId INNER JOIN
dbo.tblDepartment ON dbo.tblUsers.DepartmentID = dbo.tblDepartment.DepartmentID CROSS JOIN
dbo.tblClaim
WHERE ((SELECT CONVERT(int, KeyValue) AS Expr1
FROM dbo.tblChangeLogHeader AS tblChangeLogHeader_1) = dbo.tblClaim.ClaimID)



However my link between Claim ID and KeyValue isn't correct i don't think i've converted KeyValue to a number correctly, or i haven't linked it correctly. Can anyone help?

 

[gmmastros]

The problem is that dbo.tblChangeLogHeader.KeyValue is a varchar, but some of the values are int and others are strings, right?

In this case, I would suggest that you create a computed column in that table. Computed columns are just that. They are computed based on some calculation.

There are a couple of benefits with computed columns.

1. When the underlying values are changed, so is the computed value.

2. You can index a computed column so that joins and where clause conditions are faster.

3. None of your existing code will need to change (except where you can leverage the use of this new column).

[!]I encourage you to make a good backup of your database before running the following code.[/!]

For example, in your tblChangeLogHeader table....

Alter Table tblChangeLogHeader Add ClaimId As Convert(Int, Case When IsNumeric(KeyValue + '.0e0') = 1 Then KeyValue Else NULL End)

Create Index tblChangeLogHeader_ClaimId On tblChangeLogHeader(ClaimId)

Now, when you select data from this table, anything that CAN be converted to an integer will have a value for ClaimId. Anything that cannot be converted will have NULL.

You can see for yourself...

Select KeyValue, ClaimId From tblChangeLogHeader

Now, you query would become...

SELECT dbo.tblClaim.ClaimID, dbo.tblChangeLogHeader.TableName, dbo.tblChangeLogHeader.UndoStatus, dbo.tblChangeLogHeader.IsDraftLog,
       dbo.tblChangeLogHeader.KeyValue, dbo.tblChangeLogDetail.NewValue, dbo.tblChangeLogDetail.OldValue, dbo.tblChangeLogDetail.FieldName,
       dbo.tblChangeLogHeader.ChangeDate, dbo.tblUsers.UserName, dbo.tblUsers.UserId, dbo.tblDepartment.DepartmentID,
       dbo.tblDepartment.DepartmentName
FROM   dbo.tblChangeLogHeader INNER JOIN
       dbo.tblChangeLogDetail ON dbo.tblChangeLogHeader.rowguid = dbo.tblChangeLogDetail.ParentRowGuid INNER JOIN
       dbo.tblUsers ON dbo.tblChangeLogHeader.UserID = dbo.tblUsers.UserId INNER JOIN
       dbo.tblDepartment ON dbo.tblUsers.DepartmentID = dbo.tblDepartment.DepartmentID Inner JOIN
       dbo.tblClaim On dbo.tblChangeLogHeader.ClaimId = dbo.tblClaim.ClaimID

Also, since the computed column ClaimId has an index, your performance will improve. Of course, you now have another index on the table so insert/update/delete will become ever so slightly slower (probably un-noticeable).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom