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
 General SQL Server Forums
 New to SQL Server Programming
 Help: Cannot Convert a char value to Money

Author  Topic 

Nav522
Starting Member

27 Posts
Posted - 2010-03-23 : 12:13:31
Hello Folks,
AM having a stored procedure and making some modificatins to it. Am trying to add a new parameter called @client and making modifications regarding it. It executed fine but when am running the report its throwing me the error "Cannot Convert a char value to Money". Any ideas about this.


Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 12:18:19
it seems you've declared the parameter as a character data type and using it for manipulation on a money field. In such cases you need to do explicit cast or convert before manipulations b/w them and also values in parameter should be those which can be converted to money

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Nav522
Starting Member

27 Posts
Posted - 2010-03-23 : 12:33:43
Am really in a confused state. I dont want to use a parameter now but i just want to add the Field called ClientID..It throws me an error saying Ambigous column.


alter PROCEDURE [dbo].[sp_rptClaimsSummary43_BO]
--@StartDate DateTime,
--@EndDate DateTime
@Frequency varchar(20)

AS

/* Dates Start*/

declare @v_startdate datetime,@v_enddate datetime

BEGIN
IF @Frequency = 'DECADE'
begin
select @v_startdate= cast('20000101' as datetime)
,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end

ELSE IF @Frequency = 'YEARLY'
begin
select @v_startdate= dateadd(year, datediff(year, 0, getdate()), 0)
,@v_enddate= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end

ELSE if @Frequency = 'MONTHLY'
begin
select @v_startdate =dateadd(Month, datediff(Month, 0, getdate())-1, 0)
,@v_enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
end
END



Select 'BCCLARGEBC'=IsNull(Sum(Cast(ClaimClientFieldValue as Money)),0)
into #BCCLARGEBC
from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode =153
and cp.claimProductInvoiceDate between @v_startdate and @v_enddate
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and ccf.clientID=104
and ccf.clientFieldID=117
and left(claimclientbusunit,3) in ('70f','661','662','663','666','667','668','669','670','672','70c','999','41c')
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.ActionCode

Select 'BCCISGBC'=IsNull(Sum(Cast(ClaimClientFieldValue as Money)),0)
into #BCCISGBC
from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode =153
and cp.claimProductInvoiceDate between @v_startdate and @v_enddate
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and ccf.clientID=104
and ccf.clientFieldID=117
and left(claimclientbusunit,3) in ('60u','60m','673','63m','64m','64r','651')
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.ActionCode

Select 'UniCareBC'=IsNull(Sum(Cast(ClaimClientFieldValue as Money)),0)
into #UniCareBC
from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode =153
and cp.claimProductInvoiceDate between @v_startdate and @v_enddate
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and ccf.clientfieldID=117
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null

Select 'Client'='BCCLARGE',a.actionCode, 'Claims'=isNull(Count(c.claimID),0),
'ClaimSum'=sum(
Case c.providerID
When 2435 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2729 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2574 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2736 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2593 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2556 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2635 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2322 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2321 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2542 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2548 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2647 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2071 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2082 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2533 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
Else c.ClaimTotalBill
End),
clientID-- This is the one i have added
into #xxx from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode in (309, 324)
and a.ActionDate between @v_startdate and @v_enddate
and ccf.clientID=104
and ccf.clientFieldID=10
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and left(claimclientbusunit,3) in ('70f','661','662','663','666','667','668','669','670','672','70c','999','41c')
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.actionCode,clientID
Insert Into #xxx
Select 'Client'='BCCISG',a.actionCode, 'Claims'=isNull(Count(c.claimID),0),
'ClaimSum'=sum(
Case c.providerID
When 2435 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2729 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2574 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2736 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2593 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2556 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2635 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2322 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2321 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2542 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2548 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2647 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2071 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2082 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2533 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
Else c.ClaimTotalBill
End),
clientID -- Again i have used it here

from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode in (309, 324)
and a.ActionDate between @v_startdate and @v_enddate
and ccf.clientID=104
and ccf.clientFieldID=10
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and left(claimclientbusunit,3) in ('60u','60m','673','63m','64m','64r','651')
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.actionCode,clientID

Insert Into #xxx
Select 'Client'='UniCare',a.actionCode, 'Claims'=isNull(Count(c.claimID),0),
'ClaimSum'=sum(
Case c.providerID
When 2435 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2729 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2574 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2736 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2593 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2556 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2635 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2322 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2321 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2542 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2548 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2647 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2071 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2082 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2533 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
Else c.ClaimTotalBill
End),
clientID --Agian

from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Inner Join tblClaimClientFields ccf on ccf.claimID=c.claimID and ccf.clientID=c.clientID
Where a.actionCode = 309
and a.ActionDate between @v_startdate and @v_enddate
and ccf.clientID=118
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.actionCode,clientID

Insert Into #xxx
Select 'Client'='UniCare',a.actionCode, 'Claims'=isNull(Count(c.claimID),0),
'ClaimSum'=sum(
Case c.providerID
When 2435 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2729 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2574 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)

When 2736 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2593 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2556 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2635 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2322 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2321 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2542 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2548 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2647 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2071 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2082 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
When 2533 then c.ClaimTotalBill + isNull(c.claimProviderLateCharges,0)
Else c.ClaimTotalBill
End),
clientID

from tblClaims c
Inner Join tblClaimProducts cp on cp.claimID=c.claimID
Inner Join tblActions a on cp.claimID=a.claimID and cp.productID=a.productID
Where a.actionCode = 324
and a.ActionDate between @v_startdate and @v_enddate
and a.actionOverriddenFlag = 0
and a.actionDeletedFlag = 0
and c.clientID=118
and (Select top 1 claimID from tblActions where actionDeletedFlag=0 and actionOverriddenFlag=0
and claimID=cp.claimID and productID=cp.productID
and actionCode in (186, 190, 198, 222, 224, 225, 233, 236, 244, 248, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 271, 281,
307, 306, 308, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 339, 340, 342, 343, 344, 345, 347, 350, 312, 313, 314, 315, 316, 317, 318,
319, 320)) is null
Group by a.actionCode,clientID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 12:38:33
you've give it an alias (c.cp or a). its occuring on more than one table thats why it gives ambiguos column error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site