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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Varchar to int conversion failure

Author  Topic 

em172967
Starting Member

10 Posts

Posted - 2014-10-17 : 10:49:24
I can't run this union because of data type error. I've tried doing alot of casts to either turn Budget to int or '0 as Budget' in second union query to a decimal (20,7). Nothing works though

Error Msg: Conversion failed when converting the varchar value 'BUDGET' to data type int


SELECT        SourceID, PeriodEndDateTime, BudgetID, FiscalYearID, AccountID, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode, 
AccountCorporation, AccountCorporationName, AccountDescription, Budget, BudgetYTD, 0 AS ACTIVITY, 0 AS BALANCE, RowUpdateDateTime
FROM GlBudgetsByPeriod
WHERE (AccountClass = 7) AND (FiscalYearID >= 2012)
UNION ALL
SELECT SourceID, DateTime, 0 AS BUDGETID, YEAR(DateTime) AS YEAR, AccountID, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode,
AccountCorporation, CorporationNameAccountID, AccountDescription, NULL AS Expr1, 0 AS [BUDGET YTD], Activity, Balance, RowUpdateDateTime
FROM GlStatisticsAmounts
WHERE (YEAR(DateTime) >= 2012)

em172967
Starting Member

10 Posts

Posted - 2014-10-17 : 10:55:15
Where it says Null as Expr1 it should be 0 as Budget. I tried null to get around the error and think i forgot to change it when i pasted the code in here. Don't see anyway to edit last post -_-
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-17 : 12:03:34
[code]
SELECT SourceID, PeriodEndDateTime, BudgetID, FiscalYearID, AccountID, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode,
AccountCorporation, AccountCorporationName, AccountDescription, Budget, BudgetYTD, 0 AS ACTIVITY, 0 AS BALANCE, RowUpdateDateTime
FROM GlBudgetsByPeriod
WHERE (AccountClass = 7) AND (FiscalYearID >= 2012)
UNION ALL
SELECT SourceID, DateTime, '0' AS BUDGETID, YEAR(DateTime) AS YEAR, AccountID, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode,
AccountCorporation, CorporationNameAccountID, AccountDescription, '0' AS Budget, 0 AS [BUDGET YTD], Activity, Balance, RowUpdateDateTime
FROM GlStatisticsAmounts
WHERE DateTime >= '20120101'
[/code]
Go to Top of Page

em172967
Starting Member

10 Posts

Posted - 2014-10-17 : 12:59:36
Great Scott, that works.

thanks for the help.
Go to Top of Page
   

- Advertisement -