OraFAQ Forum: Reports & Discoverer » Code debugging - short code (don't get scared) !!

Home » Developer & Programmer » Reports & Discoverer » Code debugging - short code (don't get scared) !!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Code debugging - short code (don't get scared) !! [message #546516]

Wed, 07 March 2012 04:37

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Code debugging - short code (don't get scared) !! [message #546526 is a reply to message #546521]

Wed, 07 March 2012 05:10

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;

Report message to a moderator

Re: Code debugging - short code (don't get scared) !! [message #546528 is a reply to message #546526]

Wed, 07 March 2012 05:20

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Code debugging - short code (don't get scared) !! [message #546532 is a reply to message #546528]

Wed, 07 March 2012 05:28

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 1187 times)

Report message to a moderator

Re: Code debugging - short code (don't get scared) !! [message #546536 is a reply to message #546532]

Wed, 07 March 2012 05:37

Maverick27
Messages: 84
Registered: October 2008

Member

Thank u Littelfoot- it's working with DECODE (SIgn..

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 06:42:42 CST 2024