Decode funktion

Decode funktion

• edotcom

  yeah, its kinda fuzzy my questions.<br><br>I have a table with "over", "under", and "price". this table contains intervals
  
  over under Price
  0 100 400
  101 250 600
  251 357 800
  
  And continues like that. I have to get the user value and run through the table to find the right price (between over and under), and use the price value for a calculation in my query. Did that make any sense at all :)
  
  
  

• rmillerii

  MSSQL does not have a DECODE function and I'm not familiar with a function that performs the same functionality.
  
  One way to implement what you want is to use the case statement. Example.
  
  Select
  Case when @UserInput between 1 and 5 then 'A'
  Case when @UserInput between 6 and 10 then 'B'
  Case else 'C'
  end as MyColumn
  From
  MyTable
  
  I believe Oracle also supports the case syntax, so if you modify your SQL statement inside Oracle and it works, then it should easily migrate over to MSSQL.
  
  Larry Pope
  
  

• Steven Ramacher

  Thanks a lot, I think that will work just fine!
  
  I have sub question then, is it possible to use the a case in a loop Because I have a table where I don't know the values of the intervals.
  
  Thanks, Mark
  
  

• Mauricio hevs

  No, that makes sense now. You want to return the correct price for a given quantity. At least that's my take on it. I'm curious, Is this for a SSIS package or for some transactional system
  
  The following SQL statement would be good if your issuing the statement for a transactional system one at a time.
  Select
  Price
  From
  PriceTable
  Where
  @UserValue Between Over and Under
  
  You will of course need to build logic for when the input parameter doesn't match any records, or worse yet there is bad data and returns multiple records.
  
  If your trying to do the lookup process in bulk, there are other ways (conditional joins) that might perform better.
  
  Larry Pope
  
  

• JaganMalkareddy

  I should have seen that myself, but I didn't so thanks :) No, its a online cargo price calculater. There are many values and options involved in cargo, so it makes some bad bad statements.
  
  I have to see about the performance... I hope it will work, otherwise I'll just have to optimize along the way. I'm so lucky this is a pilot project, so they just want it to work.
  
  Thanks again, Mark
  
  

• Derek Moffett

  I'm not entirely sure what you are trying to accomplish. I kind of have an idea but before I offer a suggestion, I'd like to get a little more detail.
  
  Could you provide some background and a quick example
  
  Larry Pope
  
  
• Decode funktion