locked
Case statement in a where clause RRS feed

  • Question

  • IM trying to do this but i know the syntax is wrong

    where FS_ShipmentCO.ShippedDate > @ParamLowDate and FS_ShipmentCO.ShippedDate < @ParamHighDate and dbo.USSC_ProductLineCrossTable.ProductLine = @ParamBrand
    and (Case
    	when @ParamParts = 'YES' then FS_Item.ItemReference1 <> 03
    	when @ParamParts = 'NO' then FS_Item.ItemReference1 = 03
    	when @ParamParts = 'BOTH' then FS_Item.ItemReference1 >0

    Friday, November 9, 2012 4:17 PM

Answers

  • where FS_ShipmentCO.ShippedDate > @ParamLowDate and FS_ShipmentCO.ShippedDate < @ParamHighDate and dbo.USSC_ProductLineCrossTable.ProductLine = @ParamBrand
    and Case
    	when @ParamParts = 'YES' and FS_Item.ItemReference1 <> 03 Then 1
    	when @ParamParts = 'NO' And FS_Item.ItemReference1 = 03 Then 1
    	when @ParamParts = 'BOTH' And FS_Item.ItemReference1 >0 Then 1
            Else 0 End = 1
    Tom
    • Proposed as answer by Kalman Toth Friday, November 9, 2012 4:42 PM
    • Marked as answer by Mikie Hamilton Friday, November 9, 2012 4:46 PM
    Friday, November 9, 2012 4:27 PM
  • You can have this expression this way:

    where FS_ShipmentCO.ShippedDate > @ParamLowDate and FS_ShipmentCO.ShippedDate < @ParamHighDate and dbo.USSC_ProductLineCrossTable.ProductLine = @ParamBrand
    and 1=(Case
    	when @ParamParts = 'YES' AND FS_Item.ItemReference1 <> 3 then 1
    	when @ParamParts = 'NO' and FS_Item.ItemReference1 = 3
    then 1
    	when @ParamParts = 'BOTH' and FS_Item.ItemReference1 >0
    then 1 
    else 0 end)
    BTW, I got 3 times 'Unexpected Error' message while Tom was trying to post his reply.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Friday, November 9, 2012 4:28 PM
    • Proposed as answer by Kalman Toth Friday, November 9, 2012 4:43 PM
    Friday, November 9, 2012 4:27 PM

All replies

  • where FS_ShipmentCO.ShippedDate > @ParamLowDate and FS_ShipmentCO.ShippedDate < @ParamHighDate and dbo.USSC_ProductLineCrossTable.ProductLine = @ParamBrand
    and Case
    	when @ParamParts = 'YES' and FS_Item.ItemReference1 <> 03 Then 1
    	when @ParamParts = 'NO' And FS_Item.ItemReference1 = 03 Then 1
    	when @ParamParts = 'BOTH' And FS_Item.ItemReference1 >0 Then 1
            Else 0 End = 1
    Tom
    • Proposed as answer by Kalman Toth Friday, November 9, 2012 4:42 PM
    • Marked as answer by Mikie Hamilton Friday, November 9, 2012 4:46 PM
    Friday, November 9, 2012 4:27 PM
  • You can have this expression this way:

    where FS_ShipmentCO.ShippedDate > @ParamLowDate and FS_ShipmentCO.ShippedDate < @ParamHighDate and dbo.USSC_ProductLineCrossTable.ProductLine = @ParamBrand
    and 1=(Case
    	when @ParamParts = 'YES' AND FS_Item.ItemReference1 <> 3 then 1
    	when @ParamParts = 'NO' and FS_Item.ItemReference1 = 3
    then 1
    	when @ParamParts = 'BOTH' and FS_Item.ItemReference1 >0
    then 1 
    else 0 end)
    BTW, I got 3 times 'Unexpected Error' message while Tom was trying to post his reply.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Friday, November 9, 2012 4:28 PM
    • Proposed as answer by Kalman Toth Friday, November 9, 2012 4:43 PM
    Friday, November 9, 2012 4:27 PM
  • Toms answer seems to work fine for me.

    thanks for the help guys!!

    -Mikie

    Friday, November 9, 2012 4:42 PM
  • CASE is an expression and not a control flow statement. You mindset is still back in BASIC, C or COBOL and you have not yet learned to think in SQL. Try this:

    CASE
      WHEN @in_parts_flg = 'YES' AND FS_Items.item_ref_1 <> 03 THEN 'T'
      WHEN @in_parts_flg = 'NO' AND FS_Items.item_ref_ = 03 THEN 'T'
      WHEN @in_parts_flg = 'BOTH' AND FS_Items.item_ref_1 > 0 THEN 'T'
      ELSE 'F' END = 'T'

    The bad news is that "item_ref_1" has the small of repeated group. Look up 1NF  or "First Normal Form"


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, November 9, 2012 5:02 PM