mercredi 31 août 2016

Using case instead of IF ELSE to change WHERE clause in SQL

I am trying to build a query in which where clause slightly changes depending on an external variable and I came up with the below script

IF(@threshold='N')

  select  min_price
  ,       s.customization_type
  ,       gii.brand
  ,       gii.item_flag
  from   item_info gii
  ,      style s
  ,      categories gpc  
  where  isnull(@ordered,getdate()) between gpc.start_date and isnull(gpc.end_date, dateadd(day, 1, getdate()))
  and    gii.segment1 = s.style_number   
  and    gpc.line_of_business_category = 'AAA'
  and    gii.inventory_item_id = @v_item_id;

ELSE 
      select  min_price
  ,       s.customization_type
  ,       gii.brand
  ,       gii.item_flag
  from   item_info gii
  ,      style s
  ,      categories gpc  
  where  isnull(@ordered,getdate()) between gpc.start_date and isnull(gpc.end_date, dateadd(day, 1, getdate()))
  and    gii.segment1 = s.style_number   
  and    gpc.line_of_business_category = 'BBB'
  and    gii.inventory_item_id = @v_item_id;

But, I feel like the above query is not clean though it gives the desired results. Is there a better way I can optimized this query like using CASE WHEN?

Aucun commentaire:

Enregistrer un commentaire