Solved

I need some help with a MS SQL Query

Posted on 2011-03-20
4
210 Views
Last Modified: 2012-05-11
Hello,

I am trying to create a stored procedure with the code below and I am getting the same error no matter what I do to the comparison of the MR.AgencyId, an int field and i.invUserLevel4 that is a varchar field and has some NULL values that need not to be compared.

Error:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'NULL' to data type int.

Code:

use txu

SELECT
MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount,
CASE WHEN ISNULL(SUM(NewApps),0) = 0 THEN 0 ELSE CONVERT(int,(100 * (ISNULL(CONVERT(decimal(5,2), SUM(SigPadCount)), 0)/CONVERT(decimal(5,2), SUM(NewApps))))) END UsagePercentage,
COUNT(i.invSerialNo) as PadsOnHand
FROM
(
SELECT D.AgencyId, AgencyName, MarketingRepId, UAV.FullName
FROM txu.dbo.AgencyData AS D WITH (NOLOCK)
INNER JOIN txu.dbo.Agencies As A WITH (NOLOCK) ON A.AgencyDataId = D.AgencyDataId
INNER JOIN txu.dbo.UserAccountsView AS UAV WITH (NOLOCK) ON UAV.UserId = D.MarketingRepId
) AS MR
INNER JOIN fn_SigPadInfoByAgent('01/01/2011','01/10/2011') AS sp
      ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel4)
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount
0
Comment
Question by:richcfml
4 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35175637
This error may due to the NULL value for any of the SigPadCount.
Try change your CASE statement including ISNULL check

SELECT
MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount,
CASE WHEN ISNULL(SUM(ISNULL(NewApps,0)), 0) = 0 THEN 0 ELSE CONVERT(int,(100 * (ISNULL(CONVERT(decimal(5,2), SUM(ISNULL(SigPadCount, 0))), 0)/CONVERT(decimal(5,2), SUM(ISNULL(NewApps, 0)))))) END 
UsagePercentage,
COUNT(i.invSerialNo) as PadsOnHand
FROM
(
SELECT D.AgencyId, AgencyName, MarketingRepId, UAV.FullName 
FROM txu.dbo.AgencyData AS D WITH (NOLOCK) 
INNER JOIN txu.dbo.Agencies As A WITH (NOLOCK) ON A.AgencyDataId = D.AgencyDataId
INNER JOIN txu.dbo.UserAccountsView AS UAV WITH (NOLOCK) ON UAV.UserId = D.MarketingRepId
) AS MR
INNER JOIN fn_SigPadInfoByAgent('01/01/2011','01/10/2011') AS sp
      ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel4)
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount 

Open in new window


Raj
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35176152
>the varchar value 'NULL'
means that you have a field, that is VARCHAR, with the string value 'NULL' ... which is not NULL.
please double-check the data ...
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 35177900
I think you have non-numerical characters in invUserLevel4 column. Run this query and check such values.
select invUserLevel4 from NewSPDB.dbo.tblInventory where ISNUMERIC(invUserLevel4) = 0

Open in new window

0
 

Author Closing Comment

by:richcfml
ID: 35180482
Thank you everyone for your help. AS it turns out the value of the field invUserLevel4 was set to the string NULL. Corrected the data and everything is working now.

Again thank you very much for the help and prompt response.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question