ODBC Error Invalid Character for Cast Specification

The following code is displaying an error in Crystal Reports when drop the DISCOUNT field into the report. When I take it out there is no problem running it. I had to take the sh.OH_TICKET_TOTAL_DISCOUNT
field out of the grouping so I could get it to display only one discount in the report. If I do not it prints two. There seems to be no way around this. So I assumed that you can sum a value but you do not necesarily have to group on it. However if you group on a value....."I think you have to" sum on that value.
Can anyone identify why I am getting the error when I drop the Discount Field into my report.



USE [RAMSDB]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Route_Depositby_Date_2]    Script Date: 10/09/2013 08:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Rpt_Route_Depositby_Date_2]
            @FirstDeliveryDate as Datetime,
            @LastDeliveryDate as Datetime

as
------------------------------------------------------------
--     First Select Putting Data into Temp Table 4
------------------------------------------------------------
select distinct

            RT_ROUTE,
            RT_DESCRIPTION,
            RT_BRANCH
into #temp4
from dbo.ROSS_ROUTE r
     
------------------------------------------------------------
--     Second Select Putting Data into Temp Table 1
------------------------------------------------------------
select distinct

            sh.OH_ROUTE_NUMBER ,
            sh.OH_DELIVERY_DATE as PostDate,
            isnull(sum(sh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount,
            isnull(sum(sh.OH_TICKET_TOTAL),0) as InvoiceAmt,
            isnull(sum (ROA.ROA_AMT),0) as ROA_AMT,
            isnull(sum(de.AMOUNT),0) as Expenses  
into #temp1
from dbo.ROSS_SALESHDR sh
            left outer join dbo.ROSS_DRIVER_EXPENSES de on sh.OH_ROUTE_NUMBER = de.ROUTE_NUMBER
            and  sh.OH_DELIVERY_DATE = de.DELIVERY_DATE      


left outer join

(select distinct
            sh.OH_ROUTE_NUMBER,
            sh.OH_DELIVERY_DATE as PostDate,
            isnull(sum(sh.OH_TICKET_TOTAL_DISCOUNT),0) as Discount,
            sh.OH_CUSTOMER_NUMBER,
            sum (sh.OH_CASH_RECEIVED_PREV_DELIV) as ROA_AMT
from dbo.ROSS_SALESHDR sh
where sh.OH_ACCT_AR_TYPE = '000001' and  sh.OH_CUSTOMER_TYPE_ID In ('000003','000006')
            and sh.OH_CASH_RECEIVED_PREV_DELIV <> 0
group by sh.OH_ROUTE_NUMBER, sh.OH_DELIVERY_DATE, sh.OH_CUSTOMER_NUMBER) ROA
            on sh.OH_ROUTE_NUMBER  = ROA.OH_ROUTE_NUMBER and sh.OH_DELIVERY_DATE = ROA.PostDate
            and sh.OH_CUSTOMER_NUMBER = RoA.OH_CUSTOMER_NUMBER
where sh.OH_ACCT_AR_TYPE = '000001' and  sh.OH_CUSTOMER_TYPE_ID = '000003'
            and sh.OH_DELIVERY_DATE between @FirstDeliveryDate  and @LastDeliveryDate
group by
            sh.OH_ROUTE_NUMBER,
            sh.OH_DELIVERY_DATE
            --sh.OH_TICKET_TOTAL_DISCOUNT
            


select distinct
            rsh.SH_ROUTE,
            rsh.SH_ROUTE_DATE,
            isnull(sum(SH_DEPOSIT),0) as Deposit,
            rsh.SH_SETTLED
            
into #temp2
from dbo.ROSS_ROUTE_SETTLEMENT_HEADER rsh
group by
            rsh.SH_ROUTE,
            rsh.SH_ROUTE_DATE,
            rsh.SH_DEPOSIT,
            rsh.SH_SETTLED



select distinct
            t1.OH_ROUTE_NUMBER ,
            t1.PostDate,
            t1.InvoiceAmt,
            t1.ROA_AMT,
            t1.Expenses,
            t2.Deposit,
            t2.SH_SETTLED,
            t1.DISCOUNT            

into #temp3
from #temp1 t1 left outer join #temp2 t2 on
            t1.OH_ROUTE_NUMBER = t2.SH_ROUTE and t1.PostDate = t2.SH_ROUTE_DATE
order by 1,2,4

/* Join distinct route table */
select
            t3.*,
            r.RT_DESCRIPTION,
            r.RT_BRANCH
from #temp3 t3 inner join #temp4 r
on t3.OH_ROUTE_NUMBER  = r.RT_ROUTE

drop table #temp1;
drop table #temp2;
drop table #temp3;
drop table #temp4;
 
 
 
 
--select * from #temp1

--exec [dbo].[Rpt_Route_Depositby_Date_2] '2013-01-14 00:00:00.000', '2013-3-14 00:00:00.000'
InvalidCharacterForCastSpecifica.png
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Can you build a report that just shows the discount field?

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Very weird. At first I pulled the SP.
Dropped the discount in and ZERO results. Then dropped in an ID next to id. Nothing.
Took it out and the ID. Then started back with the ID then dropped a second different field....a date field. Got records back. Then dropped the Discount back in got results.
Very odd.
Did Verify DB.....Save closed and opened again. Ran no errors. Discount shows up....
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Don't ask me how or why but I did a weird combination of closing opening refreshing saving and verifying database for the report and the darn thing did not fail with an error.....this time. No idea what I did. I am starting to think the old technology is affecting this greatly.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
By simply asking me to build the new report with it. It was enough to get me to create an unusual combination of verifying and saving as stated above that made it work. I am not sure why it did not before but your comment made me try again even thought it seemed futile and it worked.....? I guess that is the expert to member " Never quit attititude" that solved that one. Cause I was about done there.....!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.