computed column as integer - 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

computed column as integer

Author

Topic

SqlGirl87
Starting Member

26 Posts

Posted - 2012-09-03 : 12:19:07

Hi,

I am trying to run the below script but get the error "Conversion failed when converting the varchar value 'DIFF' to data type int." - Is it possible to convert the temp computed column in the script to an integer for me to run the and <> '0' clause without having to create this into a temp table and run it that way?

Thanks!



SELECT A_ID, (ISNULL(val_a,0) - tesbak.total) AS 'DIFF'
FROM tesMAST F
INNER JOIN (select p_a_id, SUM(ISNULL(val_n,0)) as total from tesmast 
where  and level_number <> '1'
group by  p_a_id) tesBAK
                on f.a_id = tesbak.p_a_id 
WHERE  f.level_number ='1' and 'DIFF' <> 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-03 : 13:27:00

You can't use calculated columns from your where clause in the where clause

SELECT A_ID, (ISNULL(val_a,0) - tesbak.total) AS 'DIFF'
FROM tesMAST F
INNER JOIN (select p_a_id, SUM(ISNULL(val_n,0)) as total from tesmast
where and level_number <> '1'
group by p_a_id) tesBAK
on f.a_id = tesbak.p_a_id
WHERE f.level_number ='1' -- is level_number a string ot int?
and (ISNULL(val_a,0) - tesbak.total) <> 0

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 14:57:33

or if you want to use alias


SELECT *
FROM
(
SELECT A_ID, (ISNULL(val_a,0) - tesbak.total) AS 'DIFF'
FROM tesMAST F
INNER JOIN (select p_a_id, SUM(ISNULL(val_n,0)) as total from tesmast 
where  and level_number <> '1'
group by  p_a_id) tesBAK
                on f.a_id = tesbak.p_a_id 
WHERE  f.level_number ='1' 
)t
WHERE DIFF <> 0

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

SqlGirl87
Starting Member

26 Posts

Posted - 2012-09-04 : 04:54:30

Thanks Jim for the reponse

quote:
Originally posted by visakh16

or if you want to use alias

SELECT *
FROM
(
SELECT A_ID, (ISNULL(val_a,0) - tesbak.total) AS 'DIFF'
FROM tesMAST F
INNER JOIN (select p_a_id, SUM(ISNULL(val_n,0)) as total from tesmast
where and level_number <> '1'
group by p_a_id) tesBAK
on f.a_id = tesbak.p_a_id
WHERE f.level_number ='1'
)t
WHERE DIFF <> 0

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

Thanks Visakh that worked a treat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources