Decode question 1

[00112233]

Hello and thanks in advance:

I have a (I'm sure..) little problem. One of the condiutions of a query is as follows:

and x_dep_name in ('0007 Computer', '15 Notebooks')

now: users has started to insert values like '0007 Computer' and the systemd allows to do that.

Quick fix: I have modified the query as follows:

and x_dep_name in ('0007 Computer', '07 Computer', '15 Notebooks')

Then I modify the data in excel to convert '07 Computer' to '0007 Computer'.

A bit of a fain so I have decided to use the decode function to convert convert '07 Computer' to '0007 Computer' as follows:

x_dep_name in (decode(x_dep_name, '07 Computer', '0007 Computer', x_dep_name), '15 Notebooks')

it should work shouldn't it?

The think is that it brings up ALL the departments not only '07' and '15'.

Can anybody help?

Thanks




========================================================

Computer Vancouver: computer resources - list of related websites and resources with manufacturer information, books, reports, training, downloadable software, and industry exhibits.

http://www.vancouverfinder.org/computers/computer-vancouver.html

 

[PHV]

Something like this ?
and decode(x_dep_name, '07 Computer', '0007 Computer', x_dep_name) in ('0007 Computer', '15 Notebooks')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grtfercho]

x_dep_name in (decode(x_dep_name, '07 Computer', '0007 Computer', x_dep_name), '15 Notebooks')

Let's find the reason why this is not working.....

replace the x_dep_name with a value '23 Monitors'.
Decode will check if '23 monitors' is equal to '07 Computer' since it isn't it will use the x_dep_name[/] value so you will have

'23 Monitors' in ('23 Monitors','15 Notebooks')

that's an always true condition. That's why you get all the
Departments.
The solution provided by PHV is doing the decode before checking the list of possible values.That should work without a problem.




grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[00112233]

Got it!

Thanks for your help guys. I'll try it tomorrow....

Regards

Your Vancouver City Guide

http://www.vancouverfinder.org/

- Your Vancouver Guide