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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

sql query

hi all

i have a sql report, and in the primary select for the report i have the below case statement,

i now need to refer to this 'case' a few times but each time i have to repeat the code - can anyone advise of a better/cleaner way of doing this?
(case when (MPriceUom = m.StockUom) and (MPrice >= ((UnitCost/100)*10+UnitCost) or  MPrice <=(UnitCost-((UnitCost/100)*10))) then MPrice 
      when (MPriceUom <> m.StockUom) and ConvMulDiv = 'M' and ((MPrice/ConvFactAltUom) >= ((UnitCost/100)*10+UnitCost) or (MPrice/ConvFactAltUom)
		<=(UnitCost-((UnitCost/100)*10))) then MPrice/ConvFactAltUom
		when (MPriceUom <> m.StockUom) and ConvMulDiv = 'D' and ((MPrice*ConvFactAltUom) >= ((UnitCost/100)*10+UnitCost) or (MPrice*ConvFactAltUom)
		<=(UnitCost-((UnitCost/100)*10))) then MPrice*ConvFactAltUom else null end ) as price

Open in new window

  • 2
1 Solution
Pratima PharandeCommented:
One thing you can do like

get the result for this computed columns in sql query and then attach this query in from for case statements.

For Example

Select (case when (MPriceUom = m.StockUom) and (MPrice >= U or  MPrice <=U1 then MPrice
      when (MPriceUom <> m.StockUom) and ConvMulDiv = 'M' and (U2 >= U or U2
            <=U1 then MPrice/ConvFactAltUom
            when (MPriceUom <> m.StockUom) and ConvMulDiv = 'D' and (U3 >= (U or U3
            <=U1 then MPrice*ConvFactAltUom else null end ) as price
From (

Select (UnitCost/100)*10+UnitCost) as U ,(UnitCost-((UnitCost/100)*10))) as U1 , as (MPrice/ConvFactAltUom) as U2 , (MPrice*ConvFactAltUom) as U3 From tablename )
malraffAuthor Commented:
ok, its not really saving me code though?
well you could restructure it a bit (like this) to reduce some of the repeated terms....

and then put it in a with/cte structure so that the calculation becomes available for you to use...

(similar to a nested subquery) but you are allowed to specify your common code in a more linear fashion
whilst the dbms eventually integrates and constructs your final request...

;with Mpriced as (
Mprice * (case

          when (MPriceUom <> m.StockUom)
          Then Case 
               When ConvMulDiv = 'M' 
                and ((MPrice/ConvFactAltUom) >= ((UnitCost/100)*10+UnitCost) 
                       or (MPrice/ConvFactAltUom) <=(UnitCost-((UnitCost/100)*10)))
               then 1.00/ConvFactAltUom

               when ConvMulDiv = 'D'
                and ((MPrice*ConvFactAltUom) >= ((UnitCost/100)*10+UnitCost)
                      or (MPrice*ConvFactAltUom) <=(UnitCost-((UnitCost/100)*10))) 
               then ConvFactAltUom

         when (MPrice >= ((UnitCost/100)*10+UnitCost) 
                 or  MPrice <=(UnitCost-((UnitCost/100)*10)))
         then 1
          end ) as price

   from ....
   where ....
Select m.Price
  from Mpriced as M

Open in new window

malraffAuthor Commented:
yes i like that idea - used cte queries a few times before but it didnt strike me  to use them here !

cheers LFS

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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