Expression based on certain criteria 1

[kernal]

I have a term (4 characters. i.e. 1048) and date (03/01/2008) field in the database. Is the following possible in an expression?

If the date's month is less then 03 and the 4th character in the term is 4 then take the first three characters of the term and make the 4th character a 2 so if the term=1048 and the date month is less than 03 then change the term field to 1028

else if

the date's month is greater then 03 and the 4th character in the term is 4 then just take what is in the term field so if the term=1048 and the date month is greater than 03 then just show what is entered in the term field so 1048

else

just show the other data in the term field.

Thanks for any help.

 

[Dagon]

You seem to change the 3rd rather than 4th character to a 2. However, what you want is certainly possible. You could use a CASE statement e.g.

select 
(case when to_char(date_field, 'MM') = '03' and substr(term,3,1) = '4' then substr(term,1,2)||'2'||substr(term,4,1) else term end)
from
(select trunc(sysdate) as date_field,
        '1048' as term
from dual)

 

[ChrisHunt]

Since this is the 8i forum, the CASE statement may not be available (IIRC). It would be possible to construct a set of nested DECODE statements to do it, but it would be a brain-ache to write and even worse to maintain.

I would suggest that you write a PL/SQL function to do it instead. That way you could properly comment what's going on, and it would be easy to change if the rules change in the future.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

I might be wrong but I thought CASE was available in select statements, providing they weren't used inside PL/SQL.

 

[SantaMufasa]

Kernal,

I must say, your specifications are very ambiguous on this thread:[ul][li]Specifications do not match data samples, and[/li][li]Despite apparently specialised instructions for months > 3, the results appear to be unchanged from the original.[/li][/ul]

So, despite the ambiguous/incomplete specifications, and regardless of whether Oracle8 supports the CASE construct, the following code should work for any Oracle version and is extensible to handle any special processing that clear specifications might entail. Here are sample data, followed by code to deal with your specifications:

select * from kernal;

TERM DT
---- ---------
1084 21-MAR-08
1084 20-FEB-08
1084 20-APR-08
1085 21-MAR-08
1085 20-MAY-08

select decode(sign(to_char(dt,'MM')-3) -- compare MM to '3'
      ,-1,decode(substr(term,4,1),4,substr(term,1,3)||'2',term) -- if MM < 3
      ,0,term  -- if MM = 3
      ,1,term) -- if MM > 3
 from kernal;

DECO
----
1084
1082
1084
1085
1085

Let us know if you have questions or need clarifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kernal]

Duh. That's what I get when I try to explain something with little sleep. It is the 4th character so 1044 would be 1042 if 1044 is the term and the month is less than 03 (March) and it would stay as 1044 if 1044 is the term and the month is on or after 03 (March). The other terms would not be changed (i.e. 1048).

I finally have time to try the suggestions so wish me luck.

 

[kernal]

Thanks everyone. Sorry again for messing up my explanation of the problem. The case expression worked beautifully.