Re: Partial string comparison in DECODE ?

I know that in Crystal it is much easier, but I would like to create a view for my SqlPLus/Access/VB users that contains the 'decoded' data...So I may be stuck with a lengthy decode or replace function...

"fumi" <fumi_at_tpts5.seed.net.tw> wrote:

>
>TurkBear <johng_at_mm.com> wrote in message
>news:380c8dbe.6893692_at_super.news-ituk.to...
>> I am trying to create a DECODE statement to replicate some formulas I
>developed
>> in Crystal reports,( I want to create a view that includes the resulting
>values)
>> so I need to create a DECODE statement that is equivalent to:
>>
>> if dept_nbr like 'T791%' then 'Management' else if
>> dept_nbr like 'T792%' then 'Second Level' else if
>> dept_nbr like 'T7922%' then 'Second_sub Level2' .....etc
>> ( These are examples, not real , so ignore logic )
>> In other words, the 'comparison part' may be the first 2 or 3 or 4 or 5 or
>6
>> characters in the field...
>>
>> It appears that wild cards are not allowed in the DECODE expression, so
>any
>> ideas ?
>
>
>Since you use Crystal reports, it's better to use a formula
>field in Crystal report. This makes your work easy.
>For example:
>
>if {mytable.dept_nbr} like 'T791*' then
> 'Management'
>else if {mytable.dept_nbr} like 'T792*' then
> 'Second Level'
>else if ....
>
>The comprasion in DECODE is more complicate and un-intuitive:
>
>DECODE(substr(dept_nbr, 1, 4),
> 'T791', 'Management',
> 'T792', 'Second Level',
> DECODE(substr(dept_nbr, 1, 5),
> 'T7922', .....))
>
>
>

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Wed Oct 20 1999 - 12:21:47 CDT