Re: Partial string comparison in DECODE ?

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 20 Oct 1999 16:16:40 GMT
Message-ID: <7ukpt8$nfd$7@news.seed.net.tw>

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', .....)) Received on Wed Oct 20 1999 - 11:16:40 CDT