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/0 1/2011','0 1/10/2011' ) AS sp
ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel 4)
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount
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,
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/0
ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Again thank you very much for the help and prompt response.
Try change your CASE statement including ISNULL check
Open in new window
Raj