Solved

sql query

Posted on 2011-02-17
4
196 Views
Last Modified: 2012-05-11
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?
select
(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

0
Comment
Question by:malraff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34914533
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 )
0
 

Author Comment

by:malraff
ID: 34914713
ok, its not really saving me code though?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34914977
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 (
select
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
              
               end

         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

0
 

Author Comment

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

cheers LFS
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question