Home » Developer & Programmer » Reports & Discoverer » Code debugging - short code (don't get scared) !!
Code debugging - short code (don't get scared) !! [message #546516] Wed, 07 March 2012 04:37 Go to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Can somebody pls tell me why the code below isn't compiling in a Formula Placeholder ?

function New_ItemFormula return Char is
begin
return(select case when DT_EFFECT_DATE >= '01-JAN-11' then 'New' else NULL end from mst_item);
end;


or should i use DECODE ? If so, what will it be ?

Basically, i want to tag all "NEW" prods. in the report using date comparison.

Thanks in advance.

Sorry Folks - i will re-post the SQL statment. Needs some more Where conditions.

[Updated on: Wed, 07 March 2012 04:49]

Report message to a moderator

Re: Code debugging - short code (don't get scared) !! [message #546521 is a reply to message #546516] Wed, 07 March 2012 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What Reports version do you use?

Try to rewrite it as follows:
function new_itemformula return char is
  retval varchar2(20);
begin
  select case when dt_effect_date >= to_date('01.01.2011', 'dd.mm.yyyy') then 'New'
              else null
         end
    into retval
    from mst_item
    where ...;

  return (retval);
end;

Pay attention to correct use of DATE values: always use TO_DATE function with a proper date format mask. Never rely on possible implicit conversion between string (which '01-jan-11' really is - it is not a date!) and date.
Re: Code debugging - short code (don't get scared) !! [message #546526 is a reply to message #546521] Wed, 07 March 2012 05:10 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Thanks as ever Littlefoot. I have modified the SQL so that it returns a "single row" result, but i'm getting a compilation err. Something to do with syntax.
Err. message: Encountered the symbol "CASE".....

function New_ItemFormula return Char is
retval varchar2(20);
begin
SELECT CASE WHEN dt_effect_date >= to_date('01.01.2011', 'dd.mm.yyyy') THEN 'New'
ELSE null END
INTO retval
FROM mst_item
WHERE vc_comp_code = '01'
AND vc_item_code = :vc_item_code;
return (retval);
end;
Re: Code debugging - short code (don't get scared) !! [message #546528 is a reply to message #546526] Wed, 07 March 2012 05:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When reporting an error, specify it! It has its code (whether it is ORA-xxxxx, REP-xxxx, whatever).

I asked you which Reports version you use, didn't I? If it doesn't support CASE, you'll need to rewrite it, somehow. If you go with DECODE, you'll have to use SIGN function with it and subtract DT_EFFECT_DATE and 01.01.2011. The result is number of days, so you'll decide what to return based on that. Something like
select decode(sign(dt_effect_date - to_date('01.01.2011', 'dd.mm.yyyy')), 1, 'New', null)
from ...


Finally, please, click here - it will take only a few seconds to see what it is about, but will make your future messages better.
Re: Code debugging - short code (don't get scared) !! [message #546532 is a reply to message #546528] Wed, 07 March 2012 05:28 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Report Builder 6.0.8.8.3

See attachment for err. type. judging by err message. you could be correct that it doesn't support CASE.
  • Attachment: Err.JPG
    (Size: 39.27KB, Downloaded 970 times)
Re: Code debugging - short code (don't get scared) !! [message #546536 is a reply to message #546532] Wed, 07 March 2012 05:37 Go to previous message
Maverick27
Messages: 84
Registered: October 2008
Member
Thank u Littelfoot- it's working with DECODE (SIgn..
Previous Topic: Report Error Reports Designer has encountered a problem and needs to close. We are sorry for the in
Next Topic: about report pic
Goto Forum:
  


Current Time: Thu Mar 28 07:42:02 CDT 2024