Home » Developer & Programmer » Reports & Discoverer » how to write CF in the Report
how to write CF in the Report [message #618734] Tue, 15 July 2014 02:34 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,


I need TO call xxc_pkg.xxc_date(item_id)

But i am getting error LIKE the "xxc_date function returned without value"

THEN ,I need TO CREATE CF COLUMN FOR DATE COLUMN TO display IN the SELECT query.

But IN the CF(IN the data model)

function CF_DateFormula return Date is
v_date DATE;
BEGIN
SELECT Max(date)
INTO v_date
FROM x,y
WHERE a.item_id = p_item_id;
EXCEPTION
WHEN OTHERS THEN
v_date := NULL;
END;


I am passing p_item_id AS parameter.But IN the SELECT query IN the Data Model,

selet col1,
col2,
col3
FROM x,y,z
WHERE x.cust_id=:p_cust_id.


FOR these i should CREATE 2 USER parameter's as p_item_id,p_cust_id right?

I need TO CREATE concurrent program FOR this Report AND pass 2 p_item_id,p_cust_id

But this NOT corret , i want TO pass ONLY p_cust_id

how TO DO this?

Thanks

Report message to a moderator

Re: how to write CF in the Report [message #618737 is a reply to message #618734] Tue, 15 July 2014 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You main problem is that function(s) you write end (either successfully or not ("not" being the exception handler section)) without returning a value.

Make sure that RETURN statement(s) exist.

Report message to a moderator

Re: how to write CF in the Report [message #618753 is a reply to message #618737] Tue, 15 July 2014 04:09 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

You main problem is that function(s) you write end (either successfully or not ("not" being the exception handler section)) without returning a value.

Make sure that RETURN statement(s) exist.


Hi Littlefoot, i have created function in the Database in the package(with return type in the Function)

i am trying to call like below
select clo1,
       item_id
       xxc_pkg.xxc_date(item_id)
from x,y,z
where  x.cust_id=:p_cust_id.

I am getting error "xxc_date function returned without value"


And 

function CF_DateFormula return Date is
v_date DATE;
begin
 SELECT Max(date)
 INTO v_date
 FROM x,y 
WHERE a.item_id = p_item_id;
 return(v_date );
end;

In the above query i am passing parameter as p_item_id, should create in the user parameter right? But if i pass p_cust_id &p_item_id i am getting only one record(row) data.So, that's why i need to pass only p_cust_id
And 
select clo1,
       item_id,
      -- xxc_pkg.xxc_date(item_id)
      CF_DateFormula 
from x,y,z
where  x.cust_id=:p_cust_id.


Report message to a moderator

Re: how to write CF in the Report [message #618771 is a reply to message #618753] Tue, 15 July 2014 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are trying to call a stored function using
select xxc_pkg.xxc_date(item_id)
from ...

and Oracle complains that Quote:

xxc_date function returned without value

So, what exactly did you do to make sure that XXC_DATE function *returns* a value? I believe I said that in my previous message; why don't you actually read what people say?
As of CF_DATE formula column and number of its parameters: it is you who should know that. Pass as many parameters as you need in order for the function to return correct result. Is it P_CUST_ID, P_ITEM_ID, both of them, none of them but some third parameter, I wouldn't know.

Report message to a moderator

Re: how to write CF in the Report [message #618772 is a reply to message #618771] Tue, 15 July 2014 06:27 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
I am trying to call function as like below
select xxc_pkg.xxc_date(item_id)
from ...

i am getting "xxc_date function returned without value" i have checked with in the Package in the function i have a return statement in the Function .
Quote:
Make sure that RETURN statement(s) exist.


If i did any wrong means without return then we will get error xxc_date function returned"
without value", but it is fine while compiling the Package it is compiled.

Thanks

Report message to a moderator

Re: how to write CF in the Report [message #618790 is a reply to message #618772] Tue, 15 July 2014 10:50 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lack of RETURN doesn't make a function (or a package) invalid. It fails at runtime.

Could you post code of your XXC_PKG.XXC_DATE function? We'd probably spot the culprit. I suspect that there's an exception handler section which does not have a RETURN.

Report message to a moderator

Re: how to write CF in the Report [message #618823 is a reply to message #618790] Tue, 15 July 2014 22:48 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
 
FUNCTION XXC_DATE(p_item_id IN NUMBER)
  RETURN DATE
  IS
   v_date DATE;
  BEGIN
     SELECT Max(b.need_by_date)
      INTO   v_date
      FROM   po_lines_all a,
             po_line_locations_all b
      WHERE  a.po_line_id = b.po_line_id
             AND a.item_id = p_item_id;
     EXCEPTION
       WHEN OTHERS THEN
               v_date:= NULL;
     END;
	       RETURN v_date;
  END XXC_PO_DATE;

And i am trying to call in the select stmt as below
select 
xxc_pkg.xxc_date(x.item_id)
from table x,table y
where y. x.cust_id=:p_cust_id;


Here i am getting error & not when i compiling the Package


Thanks

Report message to a moderator

Re: how to write CF in the Report [message #618843 is a reply to message #618823] Wed, 16 July 2014 01:57 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are lying. This function code won't compile. Until you post something useful, I don't want to spend any time on you.

Report message to a moderator

Previous Topic: how to implement in the oracle reports just create Data Model
Next Topic: Discoverer 11g - Nested parameter condition issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 22:59:35 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.