Don't show when result = 0 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Don't show when result = 0

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-04-19 : 06:46:09

I have a query where I select a complex sum, then I get the results right. The question is, can I hide the rows where the result is 0? I think that the results are now varchars, and it comes like

SELECT cast((here's my long formula) as decimal(12,2)) AS TheComplexOne
FROM MyTable
WHERE 'TheComplexOne' <> '0.00'

but it doesn't hide the ones with 0. I also tried
WHERE 'TheComplexOne' <> 0.00
gives me "Error converting data type varchar to numeric."

I'd guess this is not a very hard thing to do.

As always, thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-19 : 07:16:34

Change your query to

SELECT cast((here's my long formula) as decimal(12,2)) AS TheComplexOne
FROM MyTable
WHERE cast((here's my long formula) as decimal(12,2)) <> 0.00

The reason for this is that, TheComplexOne is not available to the query when it processes the where clause.

Logical sequence in which the query is evaluated is

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

So you can use TheComplexOne in your order by clause, but not anywhere else.

If you are interested in the logical query processing (and a whole lot of other fun stuff), Itzik Ben-Gan's new book has a great diagram that shows the various phases.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-19 : 07:53:22

SELECT cast((here's my long formula) as decimal(12,2)) AS TheComplexOne
FROM MyTable
WHERE 'TheComplexOne' <> '0.00'

In the above, 'TheComplexOne' is not the column name. It is a string becuase you have wrapped it in single quotes. Also you can't use column alias directly in the statement except in ORDER BY clause

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-19 : 08:28:40

2 ways, more or less the same:


SELECT *
FROM (
  SELECT cast((here's my long formula) as decimal(12,2)) AS TheComplexOne
  FROM MyTable
  ) AS dt
WHERE TheComplexOne <> 0

;WITH cte AS (
  SELECT cast((here's my long formula) as decimal(12,2)) AS TheComplexOne
  FROM MyTable
)
SELECT * FROM cte WHERE TheComplexOne <> 0

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-04-19 : 09:22:34

Ok, now it works. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources