locked
Case statement in Where Clause of SQL Query RRS feed

  • Question

  • Hi,

    I have a sql query and i want to know how to use a case statement to perform certain operation.Here is the query

    SELECT
    TEDW_D_TIME_TYPE_1.MILITARY_HOUR,
    SUM(VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.SALES_DOLLARS) AS SoldSalesDollars
    FROM 
    EDW.Logical.VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS
    INNER JOIN EDW.Calendar.TEDW_D_TIME_TYPE_1
    ON VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.TIME_KEY = TEDW_D_TIME_TYPE_1.TIME_KEY 
    INNER JOIN EDW.Calendar.TEDW_D_DATE_TYPE_1
    ON VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY
    AND TEDW_D_DATE_TYPE_1.DATE_SHORT = @Date
    WHERE VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.LOCATION_NUM = @Location
    GROUP BY
    TEDW_D_TIME_TYPE_1.MILITARY_HOUR

    Now in the where clause i need to use something like this 

    WHERE

    Case when @Region = "Total" Then DO nothing

    else

     VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.LOCATION_NUM = @Location

    Can someone please help me how to frame this query?

    Thanks

    Tuesday, August 6, 2013 4:00 PM

Answers

  • Hello,

    Try it this way with a simple Boolean OR logic:

    WHERE (@Region = "Total")
          OR
          (@Region <> "Total" 
           AND VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.LOCATION_NUM = @Location)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sergio S Arias Tuesday, August 6, 2013 4:05 PM
    • Marked as answer by SqlDev12 Tuesday, August 6, 2013 5:22 PM
    Tuesday, August 6, 2013 4:03 PM

All replies

  • Hello,

    Try it this way with a simple Boolean OR logic:

    WHERE (@Region = "Total")
          OR
          (@Region <> "Total" 
           AND VEDW_F_UNAUDITED_POS_TRANSACTION_TRANSACTIONS.LOCATION_NUM = @Location)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sergio S Arias Tuesday, August 6, 2013 4:05 PM
    • Marked as answer by SqlDev12 Tuesday, August 6, 2013 5:22 PM
    Tuesday, August 6, 2013 4:03 PM
  • What do you mean by "DO nothing"?

    Why don't you just write:

    where vedw_f_unaudited_pos_transaction_transactions.location_num = @Location
    
    and @Region <> "Total"


    Bonediggler

    Tuesday, August 6, 2013 4:05 PM
  • Hi Bonediggler,

    Sorry my questions might not be clear, actually if the Parameter region = Total then exclude the where clause and if the parameter Region <> Total then

    where vedw_f_unaudited_pos_transaction_transactions.location_num = @Location

    But the solution you gave is not working here.

     
    Tuesday, August 6, 2013 5:19 PM