Help with Decode statement to replace Case 1

[hazelsisson]

Hi all,

I've devised a CASE statement for use within my stored procedure SQL insert query but then found out it doesn't work in our version of Oracle.
(The error message is PLS-00103: Encountered the symbol "CASE" when expecting one of the following: ( - + mod not null others <an identifier>).

Is there any way to re-write my case statement as a Decode statement?

I've simplified the query to show you, but hopefully you can get the idea - the case statement just sets an indicator to 'Y' if any value of j.journey_date is less than the bind variable :sdate.

insert into zcmintranet@hrtest
( zcmi_con_no, zcmi_car_reg, zcmi_mls_ntx, zcmi_old_clm )
select u.emp_num, j.reg_num, (sum(j.total_miles) - sum(nvl(j.h2o_miles,0))) nt, 
[COLOR=red](CASE when min(j.journey_date) < :sdate then 'Y' else '' end) old_claim[/color]				   
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(:sdate) and trunc(:edate) 
group by u.emp_num, j.reg_num

I have tried replacing the case statement with a local function to return the desired result but this didn't work as it's an insert over a database link and required changing some global parameters which I cannot change.

Many thanks,
Hazel

 

[LKBrwnDBA]

try this:

...
(DECODE(SIGN(min(j.journey_date) - :sdate),-1,'Y','')) old_claim

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hazelsisson]

Wow, I never would have thought of that!

It works perfectly, thanks a million.

Hazel

 

[SantaMufasa]

Hazel,

Here in Utah, "ZCMI" is a very famous initialisation for the name of the very first department store in the western United States. Out of curiosity, what does ZCMI stand for in your application?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[hazelsisson]

Hi Santa,

the table "zcmintranet" is part of our Human Resources system - the "cmi" stands for "car mileage intranet".

I'm not too sure why it's prefixed with a "z" - possibly because it's an extra table not included in the standard build of the application. All fields in the table are prefixed by "zcmi".

Not very exciting really!