Case Statement results from Where Clause

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?
metalteckAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
metalteckAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.