Varchar to int conversion failure

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 -_-

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]

em172967
Starting Member

10 Posts

Posted - 2014-10-17 : 12:59:36

Great Scott, that works.

thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources