Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Ability to Specify SQL Functions in MFDs

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Ability to Specify SQL Functions in MFDs
 Login/Join
 
Virtuoso
posted
Would anyone else like to have this ability?

This is a new feature request. I would like the ability to specify a RDBMS function that is to be used on a particular field when reporting from and RDBMS. This request would not apply to MFDs for FOCUS databases.

FOCUS does a very good job at passing as much of the request to the RDBMS engine as it possibly can. However, it would be great if there was the ability to specify RDBMS functions to wrap around fields when they are referenced and passed to the RDBMS.

For example, here is a MFD for a relational table in Oracle:

FILE=ORCLTABL, SUFFIX=SQLORA,$
SEGNAME=SEGONE, SEGTYPE=S0 ,$
FIELD=FAX_NUMBER, ALIAS=FAX_NUMBER, A10, A10, MISSING=ON,$

I would like to be able to control the SQL generated by FOCUS so that is wraps the NVL function around FAX_NUMBER when it sends it to the RDBMS.

For example, I would like the SQL that goes to the RDBMS to have the following it it when FAX_NUMBER is referenced in a request:

NVL(FAX_NUMBER,'No number')

Maybe add an additional option in the MFD such as:

FIELD=FAX_NUMBER, ALIAS=FAX_NUMBER, A10, A10, MISSING=ON, RDBMSFUNC=NVL(FAX_NUMBER,'No number'),$

This would really be useful in situations where more complicated functions could be specified which could eliminate the usage of record selection (WHERE/IF) based on DEFINE fields. Therefore more of the request is sent to the RDBMS.

Any thoughts? I already opened a case with IBI requesting this ability. One of IBIs competitors’ products already has this ability.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
I received a response from IBI that another customer has a pending NFR that is very similar to this. they will relate the two cases.

Anyone else out there who might find this useful? Let IBI know.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Master
posted Hide Post
i'd create a view on the database that handles this.... essentially master files are views anyhow.

create your view with either your NVL, DECODE or CASE function and reference the view with a master file.

We do this all the time, it allows you to optimize your query more easily and precisely as well.

Also, a note... you can create a Materialized View in Oracle (this is a proprietary Oracle funcationality, IBM has the same thing called a Materialized Query Table in DB2) that allows you to pre-aggregate your data and speed up query performance. Big big advantage when doing stuff with OLAP or any kind of aggregation analytics. Message me if you'd like more information.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Virtuoso
posted Hide Post
This is definitely a viable option but there may be some shops (like ours) which may not want to involve the DBA by asking them to do things like this. There are also some COTS applications that use RDBMSs. We do not want to start adding things to the schema and possibly voiding our support with the application vendor.

There are times when the DBA can do things like this for the reporting folks but there may be other times when it would be great to be able to do this directly from WebFOCUS.

Thanks for the comments.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Master
posted Hide Post
There isn't any piece of software that can do everything, but with every piece of software you can do anything.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Master
posted Hide Post
This is a great idea - can I merge in my original topic:

https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/8081041432

I have a feeling that there is syntax somewhere in JDEdwards adapter on how to do this, but its prob not likely to work on other adapters.

I would humbly suggest the syntax might be something like this

FIELD=FAX_NUMBER, ALIAS="RDBMSFUNC=NVL(FAX_NUMBER,'No number')", A10, A10, MISSING=ON, ,$



If the fields required were all in the same table/segment then you would not need owner.schema qualifiers but you might want to extend the syntax to allow multi table arguments for clusters.


ie this uses the alias directly rather than the original:

FIELD=FAX_NUMBER, ALIAS=FAX_NUMBER, A10, A10, MISSING=ON, RDBMSFUNC=NVL(FAX_NUMBER,'No number'),$



I will try and find the jdedwards stuff and post.

To: Jason K

Thanks for responding to my query.

We possibly have a very different situation to you in that we are using a vendor's product and dba'ing and the like requires intervention at considerable expense and is generally a last resort.

As far as using functions within Oracle I am happy doing the Oracle side - it is integrating with wf that is the problem.

There is one solution that I tried which is calling the function from a stored procedure of my own but that runs into Oracle security problems in that the roles that allow initial entry into a function are disabled and the base execute privilege has to be granted to the user which again gets into DBA probs.

Regards

John



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
We are using WF 7.6.9 and still looking for this functionality. Any idea when / if it will be possible to reference database functions directly from within the master file?


WebFOCUS 7.7.02 Teradata, Oracle
Windows
Outputs: HTML / PDF / Excel
 
Posts: 13 | Location: Michigan | Registered: December 09, 2008Report This Post
Virtuoso
posted Hide Post
I think the syntax, being server-specific, belongs more naturally in the Access file.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
hammo1j,

In your case:
FIELD=FAX_NUMBER, ALIAS="RDBMSFUNC=NVL(FAX_NUMBER,'No number')", A10, A10, MISSING=ON, ,$
How would you reference the alias is this case? Would the function alter the alias, the data or, just the returned value which, I assume would equate 'No number' to 'MISSING', right?

I, imho, like the
FIELD=FAX_NUMBER, ALIAS=FAX_NUMBER, A10, A10, MISSING=ON, RDBMSFUNC=NVL(FAX_NUMBER,'No number'),$
syntax better...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
quote:
belongs more naturally in the Access file.
... from a more historical view as well.

In the "early days", long fieldnames were catered for by entries in the access file (I know you remember this Jack Wink) and that was because fieldnames were truncated at around 12(?) chars. This, of course, led to duplicate field names which were resolved using the ACX file entries.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Guru
posted Hide Post
Mickey,

I think this is a capital idea! I use the Synonym Editor to create my MFDs because I like the fact that WebFOCUS is smart enough to only join those table needed for a given request. This makes for a very noticeable performance increase on our end.

It would be great to do things that you can do in SQL right inside the synonym. Setting a filter on a field comes to mind, using SQL-specific functions as you mention would also be great.

Recently I created a computed field called Weight_Class and it was based on another field called GVW. That is easy to do as a DECODE but in my case I had to use the SQL CASE statement so that if no value was found in the GVW column I could also look in another column called Metric_Class. That was not possible in WebFOCUS so I had to do it in SQL. For me it's no big deal because I am the entire WebFOCUS shop. But for those who can not easily access or change the SQL this would be great.

In short, ideally I'd like all the capabilities of building a SQL View but with the added advantages of a WebFOCUS Synonym.

Thanks for bringing this up!

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
A little off the topic, if I may - anytime I read "that was not possible in WebFOCUS" it piques my interest because I have found very few things that are not possible in WF that are possible in SQL. Dan's brief description of a problem doesn't seem that complicated to me-

COMPUTE WEIGHT_CLASS/A20=IF GVW NE '' THEN value-based-on-GVW ELSE value-based-on-METRIC_CLASS.

Back to the original topic - I'd vote for that one as an NFR. Seems like there are a lot of things you could do in an MFD that right now you have to explicity build into a DBMS view. Nothing against DBA's - I like mine - but I don't like to have to get them involved.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Darin, I second all your comments. I thought the same thing when I read "That was not possible in WebFOCUS"...

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
I knew as soon as I typed those words they would illicit responses like these! I had to run to a meeting and I thought, "Oh boy, I should really change that." I let the time pressure get to me! Big Grin

Allow me to re-phrase, actually, let me quote Darin, "there are a lot of things you could do in an MFD that right now you have to explicity build into a DBMS view"

Does that get me off the hook?


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
Just a thought here, but why not just create a stored procedure with the "fancy" logic, and then create a synonym for that?

OR

Create the sql as a SQL file on your file system and create a synonym for that?

Wouldn't that kind of get you what you want?


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Guru
posted Hide Post
dhagen,

If you have access to the SQL Server and the knowledge of how to do that, yes, your idea makes sense. Many of the folks in this thread say they either don't have access to the SQL Server or don't want to get the DBAs involved.

In my case I have access to the SQL Server but I don't have the knowledge to create such a stored procedure. Would you be willing to share a sample?

Thanks,

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
Forget creating the SP, just:
- create a text file with the SQL in it
- move it to the file system where the WFRS is located.
- Open dev studio to the application directory where you want the master to live.
- Right click and select New Synonym.
- Select the SQL Server instance already defined.
- change the "Restrict Object Type to" drop down to "External SQL Scrips"
- follow the instructions.

There you have it. A master file that when invoked will execute the stored SQL statement instead of reading a table.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Ability to Specify SQL Functions in MFDs

Copyright © 1996-2020 Information Builders