Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

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?
0
metalteck
Asked:
metalteck
  • 2
1 Solution
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now