?
Solved

Case Statement results from Where Clause

Posted on 2015-02-02
3
Medium Priority
?
118 Views
Last Modified: 2015-02-02
In my report I am using a case statement such as the following:

case  when month(getdate()) = 1 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01)
         when month(getdate()) = 2 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
              sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) end as GL_ACCOUNT_Balance

I only want to get the values that don't equal zero.
I know that I can't put GL_Account_Balance <> 0 in the where clause.

Anyone have any suggestions on how I can solve this?
0
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40585011
You can add a HAVING clause, and repeat the entire expression.

case  when month(getdate()) = 1 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01)
          when month(getdate()) = 2 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
               sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) end as GL_ACCOUNT_Balance
...
where ...
having case  when month(getdate()) = 1 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01)
          when month(getdate()) = 2 then sum(gla.DB_BEG_BAL) + sum(gla.CR_BEG_BAL) + sum(gla.DB_AMOUNT_01) + sum(gla.CR_AMOUNT_01) +
               sum(gla.DB_AMOUNT_02) + sum(gla.CR_AMOUNT_02) end <> 0


Or you can add an outer query and make the current a derived table.

select  *
from ( ...current_query... ) as derived_table
where
GL_ACCOUNT_Balance <> 0
0
 

Author Comment

by:metalteck
ID: 40585138
Hi ScottPletcher,

I tried the first option and it worked perfectly.
I'm trying to get the second option to work, so I can make the code look cleaner, but get the following error:

Lookup Error - SQL Server Database Error: The column 'GROUP_DESCRIPTION_LAWSON' was specified multiple times for 'A'.

I called the derived table A and in the where clause had the following statement
Where A.GL_Account_Balance <> 0
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40585198
OK, that makes sense.  Every column name in a derived table must be unique.  You have at least two columns that are both named "'GROUP_DESCRIPTION_LAWSON".

You need to rename all but one of them using "AS ":

SELECT ...
FROM (
    SELECT  ..., GROUP_DESCRIPTION_LAWSON, ..., GROUP_DESCRIPTION_LAWSON AS GROUP_DESCRIPTION_LAWSON2, ...
) AS derived
...
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question