Re: Simple decode problem makes me want to die

slackuser_at_cox.net (slackuser) wrote in message news:<7b53de56.0306090746.3bcd8614_at_posting.google.com>...
> I'm getting a headache over a simple problem with decode.
>
>
> select distinct t5.productline,
> decode(t5.productline,'501','H','WES','H','W501','H',
> 'R1500','H',
> 'ABB','H',
> 'CENTR','H',
> 'FR1','H',
> 'FR6','H',
> 'FR7','H',
> 'GE51','H',
> 'GE52C','H',
> 'GMS71','H',
> 'W501F','H',
> 'WRONG','WRONG',
> 'Y') "BLAH" from partmaster t5 where productline = 'FR7';
>
>
> for <i>some</i> productlines, this statement always defaults to 'Y'.
> Anyone ever had any problems like this?
>
> For instance, if i select FR7 or ABB it still will pull the default
> 'Y' for this decode when it should pull H. If i select GE51 it will
> decode properly to 'H'. I dont' understand why this would happen....
>
> If i select * from partmaster where productline = 'FR7' it will still
> return rows though, so it seems like my problem is only with certain
> data in that one field.
>
>
>
> Frustrating....
>
> for the record it's Oracle 8.0.6 (I know we should upgrade)

You could use RTRIM, LTRIM and/or UPPER functions to eliminate the possibility of any white spaces appended to a value or mixed case values.

Regards
/Rauf Sarwar Received on Tue Jun 10 2003 - 05:53:40 CEST