I am trying to create a simple bar graph of physicians and how many unique patients they see over a date range.

I have a master file with a text field for the physician name:

FIELDNAME=PHYSICIAN, ALIAS=PHYSICIAN, USAGE=TX50, ACTUAL=TX,
  MISSING=ON, $

InfoAssist informs me that I can't sort on text fields, so I have a DEFINE that converts the TX50 into an A50V data type:

DEFINE FILE metrics_mart_prod/historical_overview_summary
 PHYSICIAN_STRING/A50V=HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PHYSICIAN ;
END

I can successfully use PHYSICIAN_STRING to get the physician names on the bar graph's X-axis.

But things go wrong when I try to create a multi-select on the PHYSICIAN field:

WHERE HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PHYSICIAN EQ &PHYSICIAN_IN.(OR(FIND 
  METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PHYSICIAN IN
    METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY |FORMAT=TX50)).Physician:.;

When run in InfoAssist, this error immediately displays instead of the multi-user prompt:

500 - Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.

When run in App Studio, I get XML markup code indicating an exception code of 1059:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
 <ibfsrpc _jt="IBFSResponseObject" language="EN" localizeddesc="Internal exception processing IBFSService.runAdHocFex"
   name="runAdHocFex" returncode="1059" returndesc="Internal exception processing IBFSService.runAdHocFex" subreturncode="0"
     subsystem="" type="simple">

Has anybody encountered something like this on a multi-select before?

I should also mention that InfoAssist treats the physican name (Field PHYSICIAN) as a measure, not a dimension, which I find odd considering that its data type is TX50.

This message has been edited. Last edited by: <Emily McAllister>,

Aggregate the data on a nightly, daily, hourly basis and use that file for IA.

If you change your master file to A50V and A50V, it will probably give you what you want.

The only concern with doing that is for WHERE statements. If there are more than 50 characters in the CLOB, then you would need to use a LIKE instead of EQ. If all it contains is the physician name, and they are <50 char, then it will work just as if it actually was an A50V.

quote:

Originally posted by dhagen:
If you change your master file to A50V and A50V, it will probably give you what you want.

Tried that earlier. When the browser appears, it is just a white page with a busy cursor.

quote:

Originally posted by Tom Flynn:
Aggregate the data on a nightly, daily, hourly basis and use that file for IA.

The data is collected hourly. I only have an issue with the PHYSICIAN (TX50) field.

I really don't want to create another data collection/aggregation automated process. What I don't understand is that InfoAssist allows me to make PHYSICIAN multi-select. It just fails to run and stops with some kind of internal error. This must be some kind of bug.

I just checked the event log in the Administration area of WebFOCUS.

JAVA is reporting this error:

[2016-10-19 16:27:40,854] ERROR [http-bio-8080-exec-4:IBFSService] sharpet - IBFS_161019_162740-1 Severe
  ERROR_UNHANDLED_EXCEPTION(1059) Internal exception processing IBFSService.runAdHocFex

[2016-10-19 16:27:40,854] ERROR [http-bio-8080-exec-4:IBFSService] sharpet - Caused by:null

Null is Kryptonite to Java.

Maybe I have NULLs in the PHYSICIAN field?

Yup. NULLs in the PHYSICIAN field!

We had a SQL Server programmer design the database, and for a few months last summer (2015) he wasn't collecting physician information; hence the nulls.

So I'm thinking I can write a FEX to retrieve the physician names while excluding the null values. Then use that FEX to populate the control for the HTML page I will create.

However... that does not help me for basic testing, because I only have the FEX chart code right now. Is there a way to exclude null values in the FIND command below?

WHERE HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PHYSICIAN EQ &PHYSICIAN_IN.(OR(FIND
 METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY.HISTORICAL_OVERVIEW_SUMMARY.PHYSICIAN IN
  METRICS_MART_PROD/HISTORICAL_OVERVIEW_SUMMARY |FORMAT=TX50)).Physician:.;

It would be nice if there was a way to do that.

This message has been edited. Last edited by: Squatch,

Do you think you could create a view that excludes the nulls, or use somehow use DBA to do the exclusion?

quote:

Originally posted by Francis Mariani:
Do you think you could create a view that excludes the nulls, or use somehow use DBA to do the exclusion?

I have the skills to do those kinds of things, but there is a division of duty here that requires the IT department to handle it. I'm in BI. So after I talk with my supervisor tomorrow the IT department might be able to do that.

Or, it may be decided that this report is not needed after all.

I seem to be experiencing this same ERROR_UNHANDLED_EXCEPTION(1059) error when switching to Spanish. I opened the portal trace and I found where its stating

quote:

[http-bio-8080-exec-2320:IBFSService] [username] - Caused by:null

Does anyone know if there is any way to find out what variable it might be choking on? This only started occuring in 8.1.05. I never had this issue in 7.7.03 nor 8.0.08.

What is making this even more frustrating is that its very inconsistent. I have been unable to recreate this on demand

quote:

Originally posted by eric.woerle:
Does anyone know if there is any way to find out what variable it might be choking on?

If your data source is a database, do you have access to a database tool with which you can query your data directly? At least that way you can look at the fields and see which ones contain nulls.

Well, in this case, I'm not hitting any database. This a guided adhoc page that reads Master Files to allow the user to create their own queries. The only database that is being queried is WF's database for files/portal pages etc.

Ultimately, I should be able to query on nulls etc. without getting an error because it has been added to the options for drop downs (foc_missing seems to have been added somewhere between 8.0.08 and 8.1.05). To me, that means that WebFOCUS shouldn't be choking on nulls in the data as there is a way to handle them now in both the return of data (SET NODATA, SET MISSING etc) as well as in drop down menus with FOC_MISSING.

In my particular case, what I'm starting to suspect is happening, is that the &IBIWF_language parameter isn't being properly set before executing the page. Either that, or because I didn't specify a spanish translation for the name of the portal page, that the default value isn't being processed properly the first time I try to launch the page. Unfortunately, I don't see any error messages that are alerting me as to what field/variable is receiving the null value to know where my problem is.