Solved

Case Statement results from Where Clause

Posted on 2015-02-02
3
111 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:
Scott Pletcher earned 500 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

785 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