Cast or Convert Issue - SQL Server Forums

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

Cast or Convert Issue

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 23:42:21

I am trying make a journal entry from my SQL database with a Union All. So one side of the journal entry needs to equal sum of the revenue for the debit and a blank for the credit and the other side of the journal entry is the opposite. However, I am getting varchar to numeric error since debit does not like to go from a decimal on one side of the entry to a blank varchar. I tried cast and convert but I think I am doing it wrong.

/****** Script for SelectTopNRows command from SSMS ******/
SELECT Distinct [BillingEntity]
,[Stub_Revenue_Account]
,Dept = '050'
,SubDept = '000'
,Debit = sum([Revenue])
,Credit = ''
,Description = 'sdasd'
From Accrual2011_CPPMWW
Where BillingEntity = '276'
Group by BillingEntity
,[Stub_Revenue_Account]

Union All

SELECT Distinct [BillingEntity]
,[Stub_Revenue_Account]
,Dept = '050'
,SubDept = '000'
,Debit = ''
,Credit = sum([Revenue])
,Description = 'sdasd'
From Accrual2011_CPPMWW
Where BillingEntity = '276'
Group by BillingEntity
,[Stub_Revenue_Account]

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

Gregory Cardullo

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-15 : 00:05:08

Change your empty strings to zeros.

,Credit = ''
,Credit = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 00:07:27

if its a decimal why not make 0.00 as default value rather than ''. Also its not recommended to change decimal to varchar just for making default as ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-15 : 00:38:41

Exactly my point.

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-15 : 06:22:40

I do that with journal entries that I put together then I just delete out the 0s where it should be ''. I am working on a project for someone else and are uploads have to have '' and not 0 or I get a error.

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:37:39

what error you get when its 0? If you want to preserve datatype you need to use 0 itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-15 : 09:22:56

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

It would be nice not to have to go into the entry and delete the zeros but it is not a big issue.

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:24:21

that means there are some non numeric values also coming in your credit field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources