Link to home
Start Free TrialLog in
Avatar of richcfml
richcfml

asked on

I need some help with a MS SQL Query

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
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of richcfml
richcfml

ASKER

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.