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.

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.

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.

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.

There isn't any piece of software that can do everything, but with every piece of software you can do anything.

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

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?

I think the syntax, being server-specific, belongs more naturally in the Access file.

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...

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

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

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.

Darin, I second all your comments. I thought the same thing when I read "That was not possible in WebFOCUS"...

Cheers,

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!

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?

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?

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

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.