Query Error

Question

Post reply

Query Error

Michael Graves

SSC Veteran

Points: 275
More actions
July 27, 2005 at 7:50 am

#186859

I've got table with a field called claimed (nvarchar). This is used to record claims, if a claim is input after the main run it could go in as 2A. When i try to run a query with this in a get a this error.
Server: Msg 170, Level 15, State 1, Line 11
Error converting data type varchar to float
Can anyone help ?
select Pay_No, Name,ContractID,CSM, Team_No, Budget_Code, Team,
Case when employedas >'a' then employedas when subject >'1' then subject end Subject_Employed,
startdate, enddate, starttime, endtime,
case when monthann > 01/08/04 then monthann when dateses > 01/08/04 then dateses end Worked,
hours, mth_paid,
case when hourlyrate > 0 then Hourlyrate when payrate > 0 then payrate end Rate,
case when tempaid > 0 then tempaid when annpaid > 0 then annpaid when paid > 0 then paid end Paid,
case when claim = 1 then 1 when annual = 1 then 2 when stsr = 1 then 3 end Conno,
case when claim = 1 then 'P/T Lecturer Claim' when annual = 1 then 'Annualised Hours' when stsr = 1 then 'Temp Business Support' end Conid
from payslip_vw
order by budget_code,conno, pay_no

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

since [claim] is actually a varchar, you should make your comparisons the same data type:

when [claim] = 1 should be when [claim]='1'

or when it has versions appended to it:

when substring(claim,1,1) = '1' -- this would ignore the '1A' or '2A' stuff.

you may need to do the same fixes to your annual or stsr fields as well, but without knowing the data type it's speculation.

select Pay_No, Name,ContractID,CSM, Team_No, Budget_Code, Team,

Case when employedas >'a' then employedas when subject >'1' then subject end Subject_Employed,

startdate, enddate, starttime, endtime,

case when monthann > 01/08/04 then monthann when dateses > 01/08/04 then dateses end Worked,

hours, mth_paid,

case when hourlyrate > 0 then Hourlyrate when payrate > 0 then payrate end Rate,

case when tempaid > 0 then tempaid when annpaid > 0 then annpaid when paid > 0 then paid end Paid,

case when substring(claim,1,1)= '1' then 1 when annual = 1 then 2 when stsr = 1 then 3 end Conno,

case when substring(claim,1,1)= '1' then 'P/T Lecturer Claim' when annual = 1 then 'Annualised Hours' when stsr = 1 then 'Temp Business Support' end Conid

from payslip_vw

order by budget_code,conno, pay_no

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Graves SSC Veteran Points: 275 More actions · Answer 2

The query worked fine until we tried to use 2a as this can occur. If the A is removed then it works?

Lowell SSC Guru Points: 323518 More actions · Answer 3

that was my point...the example i provided takes the '2A' into consideration by grabbing the substring of the first character, then you get your claim [version?] of '1' or '2' and the select should no longer crash when it tries to compare claim = 1; it would properly compare '2' = '1' for example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Graves SSC Veteran Points: 275 More actions · Answer 4

So the line would read

case when substring(claimed,1,1)= '1' then 1 end claimed

So when you enter the pararmater 2 it will then return all the 2 nd 2A in the claimed field.