So I have a function in oracle db that simply returns a date...

[NYCBabak .]

Could you try using the WITH in your define In WebFOCUS when you create a DEFINE that doesnt reference any real columns, you associate it with a real field. Something like this:

MAX_DATE/MDYY WITH PROFIT =your function here;

[Todd Van Valkenburg]

Sorry, I do not understand. If I have this (calling a function without parameters):

max_date/MDYY=SQL.gendev.f_get_max_date_reported();

And what goes along with the WITH (your example has PROFIT). In my case, I do not have any input parameters. Can you elaborate

Thanks.

[NYCBabak .]

Any real field you have in your table will do. WITH syntax is defined in the doc as follows:

WITH realfield associates a virtual field with a data source segment containing a real field. For more information see: https://infocenter.informationbuilders.com/wf80/index.jsptopic=%2Fpubdocs%2Freporting%2FCreatingReportswithWFLanguage%2Fsource%2Ftopic56.htm

The error message youre getting is telling you that you have a define that doesnt reference any field in your table. So, if you add the WITH fieldname to your define, you anchor this field to a real field.

[Todd Van Valkenburg]

Thank you! That worked. I dont understand why this would be needed (maybe due to how an adaptor is used). In any case, this solved the problem and I will make a note of this for future reference.

[NYCBabak .]

This syntax is there to associate a virtual field with a specific segment of your data. In a multi-segmented table, you may want your defined field with a constant value to be a part of a lower segment. This changes the grain of your defined field especially in the more legacy data types. In an RDBMS this may not really matter.

[Todd Van Valkenburg]

So I have a function in oracle db that simply returns a date field. And I am referencing this function in a DEFINE. What I am noticing is that if the oracle function has one input parameter, then the DEFINE works. However, if the same oracle function is altered slightly and does not have an input parameter, then the the DEFINE does not work and generates this error:

ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC36343) SQL SPECIFIC SYNTAX CANNOT BE USED IN CONSTANT DEFINE max_date. WITH OPTION IS REQUIRED.

ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: max_date

ERROR AT OR NEAR LINE 21 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: max_date

(FOC009) INCOMPLETE REQUEST STATEMENT

BYPASSING TO END OF COMMAND

Ideally, the function should not require an input parameter. So I am guessing there is a way to call a function that has no input parameters from a DEFINE but I must be missing something.

This is my define that did not work when the oracle function did not have any input parameters:

max_date/MDYY=SQL.gendev.f_get_max_date_reported();

This is my define that DID work when the oracle function had one input varchar parameter:

dummy/A5V=X;

max_date/MDYY=SQL.gendev.f_get_max_date_reported(dummy);

I tried to find the WITH OPTION in the Creating Reports with WebFOCUS Language but no relevant hits.

Thank you for your help.