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]

try this:

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 Convert(Int, dbo.tblChangeLogHeader.KeyValue) = dbo.tblClaim.ClaimID

-George

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

 

[chrisppppp]

Thanks for the quick response, i have tried that and i get the following error:

SQL Execution Error
Error Source: .Net SQLClient Data Provider
Error Message: Conversion failed on when converting the varchar value "GeorgeB" to data type int

Is this because KeyValue also has values which can't be converted to number? Is there a way round this?

 

[bborissov]

Then you have values in dbo.tblChangeLogHeader.KeyValue which are NOT numeric and can't be converted to int.
Did you get any result from this:

SELECT * FROM tblChangeLogHeader
WHERE ISNUMERIC(KeyValue+'e0') = 0

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Oops, it should be:

SELECT * FROM tblChangeLogHeader
WHERE ISNUMERIC(KeyValue+'.e2') = 0

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Is this because KeyValue also has values which can't be converted to number? Is there a way round this?

Yes, and yes.

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 Convert(Int, Case When IsNumeric(dbo.tblChangeLogHeader.KeyValue + '.0e0') = 1 
                                         then dbo.tblChangeLogHeader.KeyValue
                                         else NULL End)  = dbo.tblClaim.ClaimID

-George

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

 

[chrisppppp]

bborissov: when i run that query it runs fine with no problems. Does this mean there is a way round which excludes anything that can't be converted to number?

gmmastros: when i run your updated query i get this error:

SQL Execution Error
Error Source: .Net SQLClient Data Provider
Error Message: Conversion failed on when converting the varchar value "KeithM" to data type int

 

[bborissov]

I didn't ask if it runs fine, but if you get ANY results from it

BTW what type is tblChangeLogHeader.KeyValue?
N/CHAR or N/VARCHAR?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chrisppppp]

Sorry, i do get results from it.

it is a VARCHAR

 

[bborissov]

And dbo.tblClaim.ClaimID?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chrisppppp]

ClaimID is an integer

 

[bborissov]

I ask this because George's code should work:

DECLARE @Test TABLE (Fld1 varchar(2000))
INSERT INTO @Test VALUES('1')
INSERT INTO @Test VALUES('1      ')
INSERT INTO @Test VALUES('Boris')
INSERT INTO @Test VALUES('chrisppppp')
SELECT Fld1, Convert(Int, Case When IsNumeric(Fld1 + '.0e0') = 1
                                         then Fld1
                                         else NULL End) AS Fld2
FROM @test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chrisppppp]

I still get the same error as above tho, any ideas, should i just break down the query into a small test query using only the Claim table and the ChangeLogHeader table?

 

[gmmastros]

Flip it around. Convert the int to varchar.

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.KeyValue = Convert(VarChar(20), dbo.tblClaim.ClaimID)

Cross your fingers before running this.

-George

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

 

[chrisppppp]

But i need it to be an integer. Think i need to describe what i'm doing better. The Claim ID table has all claims within it and holds the Claim ID as an integer, the ChangeLogHeader table logs changes to the Claim but it holds the Claim ID as a varchar in the ChangeLogHeader in the KeyValue field. So my idea was to create a query linking these two tables together so that i could then attach this query to the Claim table so i can create a report showing who has made what changes to what claims. The best idea i came about is to link via the query which also converts the KeyValue to a number so that they can be linked, and in the query have an extra field for ClaimID so then i can link this query to the Claim table. Hope that makes sense!!

So am i going down the right track, or completely in the wrong direction?

 

[chrisppppp]

i've just run this code from above:

SELECT * FROM tblChangeLogHeader
WHERE ISNUMERIC(KeyValue+'.e2') = 0

and i've noticed that i don't actually get any true int back, its only converted what i would expect to be varchar's into int, ie:

KeithM
DuncanC

rather than

111111123
123424533

does this help at all?

 

[chrisppppp]

Just looking more into it, i can restrict the dbo.tblChangeLogHeader.TableName = 'Policy' should only really bring int through, so what is the best way of writing my query now do you think?

 

[gmmastros]

But i need it to be an integer.

It is an integer, and will be returned from the query as an integer. It's only being converted to a varchar for the purposes of joining to the other table. I encourage you to try that last query I posted, I think it's the easiest way to return the information you want.


-George

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

 

[SimonSellick]

To return an int, you need to convert it:

SELECT
  convert(integer, KeyValue + '.0e0') ClaimId
, ...
FROM tblChangeLogHeader
WHERE ISNUMERIC(KeyValue + '.0e0') = 0
  AND TableName = 'Policy'

That should then equijoin to the natural int in the claim id table.

HTH

 

[chrisppppp]

Thanks for the reply's, i ended up using what george posted:

SELECT dbo.tblChangeLogHeader.TableName, dbo.tblChangeLogHeader.KeyValue, dbo.tblClaim.ClaimID, dbo.tblChangeLogHeader.ChangeDate,
dbo.tblChangeLogHeader.IsDraftLog, dbo.tblChangeLogHeader.UndoStatus, dbo.tblChangeLogHeader.UserID, dbo.tblUsers.FullName,
dbo.tblDepartment.DepartmentID, dbo.tblDepartment.DepartmentName, dbo.tblChangeLogDetail.FieldName, dbo.tblChangeLogDetail.OldValue,
dbo.tblChangeLogDetail.NewValue, dbo.tblChangeLogHeader.FormName, dbo.tblChangeLogHeader.DatasetName,
dbo.tblChangeLogHeader.DatasetKeyValue, dbo.tblChangeLogHeader.ActionType, dbo.tblChangeLogHeader.rowguid
FROM dbo.tblChangeLogHeader INNER JOIN
dbo.tblClaim ON dbo.tblChangeLogHeader.KeyValue = dbo.tblClaim.ClaimID INNER JOIN
dbo.tblChangeLogDetail ON dbo.tblChangeLogHeader.rowguid = dbo.tblChangeLogDetail.ParentRowGuid INNER JOIN
dbo.tblUsers ON dbo.tblChangeLogHeader.UserID = dbo.tblUsers.UserName INNER JOIN
dbo.tblDepartment ON dbo.tblUsers.DepartmentID = dbo.tblDepartment.DepartmentID
WHERE (tblChangeLogHeader.TableName = 'Claim') AND (ISNUMERIC(dbo.tblChangeLogHeader.KeyValue + '.e2') = 1)


I was just wondering if i could just create the query another way, say if i added a column to my query called Claim ID and then updated all integers from KeyValue into this column where the TableName = 'Claim'. Is this possible? If so, how?