Getting Error while Executing the Query

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)

Getting Error while Executing the Query

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 03:18:23

Hello there,

I had a T-SQL, but whenever am trying to run the T-SQL, it's giving following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

T-SQL is as follows:


SELECT	TOP (100) PERCENT	
							ISNULL(dbo.employees.first_name + ' ', '') + ISNULL(dbo.employees.middle_name + ' ', '') + ISNULL(dbo.employees.last_name, '') AS Name,
							dbo.[level].[level],
							dbo.trades.trade,
							CONVERT(varchar, dbo.Corporate_Calendar.Date, 107) AS Date,
							CASE
								WHEN dbo.leave_app.[grant] = 1 AND (dbo.Corporate_Calendar.Date BETWEEN dbo.leave_app.[date_from] AND dbo.leave_app.[date_to]) THEN 'L'
								WHEN (dbo.Corporate_Calendar.Holiday_Type = 'Sunday') THEN 'S'
								WHEN (dbo.Corporate_Calendar.Holiday = '1') THEN 'H'
								WHEN EXISTS (SELECT [empID] FROM dbo.pic_allotment WHERE empID = dbo.employees.username) AND (dbo.Corporate_Calendar.Date BETWEEN dbo.pic_allotment.[date_from] AND dbo.pic_allotment.[date_to]) THEN Cast((dbo.clients.code + '-' + dbo.projects.job_no + '-' + dbo.projects.code) As Varchar(50))
								ELSE '  '
							END AS Status
FROM         dbo.projects INNER JOIN
                      dbo.clients ON dbo.projects.client_no = dbo.clients.id LEFT OUTER JOIN
                      dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode RIGHT OUTER JOIN
                      dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username LEFT OUTER JOIN
                      dbo.trades ON dbo.employees.trade = dbo.trades.id LEFT OUTER JOIN
                      dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby LEFT OUTER JOIN
                      dbo.[level] ON dbo.employees.[level] = dbo.[level].id CROSS JOIN
                      dbo.Corporate_Calendar
WHERE     (dbo.Corporate_Calendar.Date BETWEEN DATEADD(day, - 6, GETDATE()) AND DATEADD(day, 31, GETDATE())) AND (dbo.trades.process = 1)
ORDER BY Name

It was working fine, but when I replaced 'B' with the bold part inside the T-SQL, it's started giving error.

Please help!

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 03:39:46

>> Cast((dbo.clients.code + '-' + dbo.projects.job_no + '-' + dbo.projects.code) As Varchar(50)
Presumably one of those columns is an int so it is trying to cast all the others to an int (int takes precendence over character) beforethe cast.
You need to cast the columns individually beforethe concatenation - probably job_no is the offender so just cast that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 03:40:32

SELECT TOP ( 100 ) PERCENT
ISNULL(dbo.employees.first_name + ' ', '')
+ ISNULL(dbo.employees.middle_name + ' ', '')
+ ISNULL(dbo.employees.last_name, '') AS Name,
dbo.[level].[level],
dbo.trades.trade,
CONVERT(varchar, dbo.Corporate_Calendar.Date, 107) AS Date,
CASE WHEN dbo.leave_app.[grant] = 1
AND ( dbo.Corporate_Calendar.Date BETWEEN dbo.leave_app.[date_from]
AND dbo.leave_app.[date_to] )
THEN 'L'
WHEN ( dbo.Corporate_Calendar.Holiday_Type = 'Sunday' ) THEN 'S'
WHEN ( dbo.Corporate_Calendar.Holiday = '1' ) THEN 'H'
WHEN EXISTS ( SELECT [empID]
FROM dbo.pic_allotment
WHERE empID = dbo.employees.username )
AND ( dbo.Corporate_Calendar.Date BETWEEN dbo.pic_allotment.[date_from]
AND dbo.pic_allotment.[date_to] )
THEN ( Cast(dbo.clients.code AS VARCHAR(50)) + '-'
+ Cast(dbo.projects.job_no AS VARCHAR(50)) + '-'
+ Cast(dbo.projects.code As Varchar(50)) )
ELSE ' '
END AS Status
FROM dbo.projects
INNER JOIN dbo.clients ON dbo.projects.client_no = dbo.clients.id
LEFT OUTER JOIN dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode
RIGHT OUTER JOIN dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username
LEFT OUTER JOIN dbo.trades ON dbo.employees.trade = dbo.trades.id
LEFT OUTER JOIN dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby
LEFT OUTER JOIN dbo.[level] ON dbo.employees.[level] = dbo.[level].id
CROSS JOIN dbo.Corporate_Calendar
WHERE ( dbo.Corporate_Calendar.Date BETWEEN DATEADD(day, -6, GETDATE())
AND DATEADD(day, 31, GETDATE()) )
AND ( dbo.trades.process = 1 )
ORDER BY Name

--------------------------
http://connectsql.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 04:00:54

I tried with lionofdezert code, but still not working

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 04:08:00

same error ?, please share current error as it will be helpful to understand the real problem.

--------------------------
http://connectsql.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 04:15:04

if the only change from working code is as you say then you should just need to change
Cast((dbo.clients.code + '-' + dbo.projects.job_no + '-' + dbo.projects.code) As Varchar(50))
to
convert(varchar(50),dbo.clients.code) + '-' + convert(varchar(50),dbo.projects.job_no) + '-' + convert(varchar(50),dbo.projects.code)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 04:40:54

@lionofdezert: Getting Same Error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

@nigelrivett: I tried to use your given code, but getting same error message..

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-04 : 04:48:38

quote:
Originally posted by daipayan

@lionofdezert: Getting Same Error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

@nigelrivett: I tried to use your given code, but getting same error message..

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

can you post that version of query ?

check all the column that involved in concatenation make sure it is string, if not convert to string before concatenate

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 05:31:41

@khtan: My Query Version is: Microsoft SQL Server Management Studio 9.00.3042.00 in MS SQL Server 2005 and all the concat columns having string value only!

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-04 : 05:34:20

i mean post your query that give the error message

KH
[spoiler]Time is always against us[/spoiler]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 05:37:30

then execute following queries and check if you get any row ??? If YES then condition must be = '1' instead of = 1
SELECT process FROM dbo.trades
WHERE ISNUMERIC(process) = 0

SELECT [grant] FROM dbo.leave_app
WHERE ISNUMERIC([grant]) = 0
--------------------------
http://connectsql.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 05:49:35

@khtan: I had posted the query in the starting of the thread only, still here it goes again:

SELECT TOP (100) PERCENT
ISNULL(dbo.employees.first_name + ' ', '') + ISNULL(dbo.employees.middle_name + ' ', '') + ISNULL(dbo.employees.last_name, '') AS Name,
dbo.[level].[level],
dbo.trades.trade,
CONVERT(varchar, dbo.Corporate_Calendar.Date, 107) AS Date,
CASE
WHEN dbo.leave_app.[grant] = 1 AND (dbo.Corporate_Calendar.Date BETWEEN dbo.leave_app.[date_from] AND dbo.leave_app.[date_to]) THEN 'L'
WHEN (dbo.Corporate_Calendar.Holiday_Type = 'Sunday') THEN 'S'
WHEN (dbo.Corporate_Calendar.Holiday = '1') THEN 'H'
WHEN EXISTS (SELECT [empID] FROM dbo.pic_allotment WHERE empID = dbo.employees.username) AND (dbo.Corporate_Calendar.Date BETWEEN dbo.pic_allotment.[date_from] AND dbo.pic_allotment.[date_to]) THEN Cast((dbo.clients.code + '-' + dbo.projects.job_no + '-' + dbo.projects.code) As Varchar(50))
ELSE ' '
END AS Status
FROM dbo.projects INNER JOIN
dbo.clients ON dbo.projects.client_no = dbo.clients.id LEFT OUTER JOIN
dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode RIGHT OUTER JOIN
dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username LEFT OUTER JOIN
dbo.trades ON dbo.employees.trade = dbo.trades.id LEFT OUTER JOIN
dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby LEFT OUTER JOIN
dbo.[level] ON dbo.employees.[level] = dbo.[level].id CROSS JOIN
dbo.Corporate_Calendar
WHERE (dbo.Corporate_Calendar.Date BETWEEN DATEADD(day, - 6, GETDATE()) AND DATEADD(day, 31, GETDATE())) AND (dbo.trades.process = 1)
ORDER BY Name

quote:
Originally posted by khtan

i mean post your query that give the error message

KH
[spoiler]Time is always against us[/spoiler]

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 05:54:32

I tried your condition, but result remain the same..no change in error message..

quote:
Originally posted by lionofdezert

then execute following queries and check if you get any row ??? If YES then condition must be = '1' instead of = 1
SELECT process FROM dbo.trades
WHERE ISNUMERIC(process) = 0

SELECT [grant] FROM dbo.leave_app
WHERE ISNUMERIC([grant]) = 0
--------------------------
http://connectsql.blogspot.com/

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 06:36:04

Run this

select COUNT(*)
FROM dbo.projects INNER JOIN
dbo.clients ON dbo.projects.client_no = dbo.clients.id LEFT OUTER JOIN
dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode RIGHT OUTER JOIN
dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username LEFT OUTER JOIN
dbo.trades ON dbo.employees.trade = dbo.trades.id LEFT OUTER JOIN
dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby LEFT OUTER JOIN
dbo.[level] ON dbo.employees.[level] = dbo.[level].id CROSS JOIN
dbo.Corporate_Calendar
WHERE (dbo.Corporate_Calendar.Date BETWEEN DATEADD(day, - 6, GETDATE()) AND DATEADD(day, 31, GETDATE())) AND (dbo.trades.process = 1)

if that works then the issue is with the select
comment out the clauses in turn until you identify the line that is causing the problem

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 06:55:22

Which columns in JOIN cluases are of NUMERIC datatype??

--------------------------
http://connectsql.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 07:14:28

I ran the query as you had provided and it's giving the same error as mine:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

quote:
Originally posted by nigelrivett

Run this

select COUNT(*)
FROM dbo.projects INNER JOIN
dbo.clients ON dbo.projects.client_no = dbo.clients.id LEFT OUTER JOIN
dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode RIGHT OUTER JOIN
dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username LEFT OUTER JOIN
dbo.trades ON dbo.employees.trade = dbo.trades.id LEFT OUTER JOIN
dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby LEFT OUTER JOIN
dbo.[level] ON dbo.employees.[level] = dbo.[level].id CROSS JOIN
dbo.Corporate_Calendar
WHERE (dbo.Corporate_Calendar.Date BETWEEN DATEADD(day, - 6, GETDATE()) AND DATEADD(day, 31, GETDATE())) AND (dbo.trades.process = 1)

if that works then the issue is with the select
comment out the clauses in turn until you identify the line that is causing the problem

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 07:16:40

Except dbo.employees.username, dbo.leave_app.updatedby, pic_allotment.empID; all other columns datatype is NUMERIC

quote:
Originally posted by lionofdezert

Which columns in JOIN cluases are of NUMERIC datatype??

--------------------------
http://connectsql.blogspot.com/

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 07:18:51

nigelrivett asked you to comment each line in join clause, one by one and check, which line is the real calprit.

--------------------------
http://connectsql.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 07:22:02

Now comment out joins in turn to find the offending entry
select COUNT(*)
FROM dbo.projects
INNER JOIN dbo.clients ON dbo.projects.client_no = dbo.clients.id
--LEFT OUTER JOIN dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode
--RIGHT OUTER JOIN dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username
--LEFT OUTER JOIN dbo.trades ON dbo.employees.trade = dbo.trades.id
--LEFT OUTER JOIN dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby
--LEFT OUTER JOIN dbo.[level] ON dbo.employees.[level] = dbo.[level].id

I'm not very keen on your column names.
empid and username don't sound like the same domain.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-04 : 08:09:23

As per your instructions, I did the checking and the results are as follows:
select COUNT(*)
FROM dbo.projects
INNER JOIN dbo.clients ON dbo.projects.client_no = dbo.clients.id - Output: 4
--LEFT OUTER JOIN dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode - Output: Msg 8114, Level 16, State 5, Line 1 | Error converting data type varchar to numeric.
--RIGHT OUTER JOIN dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username - Output: 336
--LEFT OUTER JOIN dbo.trades ON dbo.employees.trade = dbo.trades.id - Output: 336
--LEFT OUTER JOIN dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby - Output: 336
--LEFT OUTER JOIN dbo.[level] ON dbo.employees.[level] = dbo.[level].id - Output: 336

quote:
Originally posted by nigelrivett

Now comment out joins in turn to find the offending entry
select COUNT(*)
FROM dbo.projects
INNER JOIN dbo.clients ON dbo.projects.client_no = dbo.clients.id
--LEFT OUTER JOIN dbo.pic_allotment ON dbo.projects.job_no = dbo.pic_allotment.jobcode
--RIGHT OUTER JOIN dbo.employees ON dbo.pic_allotment.empID = dbo.employees.username
--LEFT OUTER JOIN dbo.trades ON dbo.employees.trade = dbo.trades.id
--LEFT OUTER JOIN dbo.leave_app ON dbo.employees.username = dbo.leave_app.updatedby
--LEFT OUTER JOIN dbo.[level] ON dbo.employees.[level] = dbo.[level].id

I'm not very keen on your column names.
empid and username don't sound like the same domain.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 08:17:48

What are the data types for dbo.projects.job_no and dbo.pic_allotment.jobcode?

Jim

Everyday I learn something that somebody else already knew

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources