Decode funktion

Hi!

I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc.

Can I use decode? or/and is there better funktion (I'm using MS SQL 2005)

Thanks in advance

Mark

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|||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|||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|||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 ? :)|||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|||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