Solved

Case Statement results from Where Clause

Posted on 2015-02-02
3
107 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
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now