Solved

I need some help with a MS SQL Query

Posted on 2011-03-20
4
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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 41

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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