When trying to change an existing join in an InfoAssist crea...

[Manoj Chaurasia]

Hi Debra

Please post your code so we can evaluate it.

[Debra Waybright]

When trying to change an existing join in an InfoAssist created report (it currently joins a month end date from the driver table to a date in table B, want to change which date in table B is joined to) after clicking OK in the join window, I get FOC258 error and it babbles about a parameter value which is used in a define field not being found. I believe this error is a red herring since the define code comes right after the join code.

Since this change needs to be made to about 50 InfoAssist created reports, I need to figure out what is going on because that is too many to recreate from scratch and just changing the code directly is not an option.

Have tried changing the join using the drag and drop and by choosing the field in the join filter window.

Any ideas

Thanks in advance,

Deb

[Debra Waybright]

I will post the code, but it is generated in InfoAssist. Im just trying to change the field the METRICS table is joining to on the DATES table. Here is the filter window from the join:

 

Here is the code:

-*Do not delete or modify the comments below

*-INTERNAL_COMMENT LINE#0$PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+DQo8IS0tMS4wLS0+PFJvb3QgcmVsZWFzZT0iOC4yLjAuMyIgdmVyc2lvbj0iMS4zIj4NCiAgICA8T2JqZWN0IG9iamVjdElkPSJUYWJsZUNoYXJ0XzEiPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iTGlua2VkU29ydHMiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImNvbXBvbmVudEN1c3RvbVRpdGxlIiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+ZmFsc2U8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iT3B0aW9uYWxQYXJhbXMiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IldoZXJlVHlwZXNPcmRlcmVkIiB0eXBlPSJMaXN0Ij4NCiAgICAgICAgICAgIDxFbnRyeSB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5JQV9XSEVSRTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+SUFfV0hFUkU8L0VudHJ5Pg0KICAgICAgICA8L1Byb3BlcnR5Pg0KICAgIDwvT2JqZWN0Pg0KICAgIDxPYmplY3Qgb2JqZWN0SWQ9IkdMT0JBTCI+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJTYW1wbGVEYXRhIiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+dHJ1ZTwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJHbG9iYWxSZWNvcmRMaW1pdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NTAwPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbFJ1blJlY29yZExpbWl0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj4wPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbE9wdGltaXphdGlvbiIgdHlwZT0iamF2YS5sYW5nLkJvb2xlYW4iPnRydWU8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iZmllbGREaXNwbGF5TW9kZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+bGFiZWw8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0icHJlZml4RGlzcGxheU1vZGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IkFjdGl2ZV9TdHlsZV9Vc2VyX3R5cGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnBvd2VyPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IlNhdmVTdGFydFRvb2xJblR5cGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPkNoYXJ0PC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbFZhbHVlc1BhZ2luZyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJGb2NleGVjUHJlZmVyZW5jZXMiIHR5cGU9Ik1hcCI+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5U2xpY2Vyc1RhYkVkaXRJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlTZXJpZXNUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImF1dG9EcmlsbFNjcmlwdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyIvPg0KICAgICAgICAgICAgPEVudHJ5IGtleT0icnVuT25TdGFydHVwRGVmZXJyZWRJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlFZGl0TW9kZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUhvbWVUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9InRhcmdldEF1dG9MaW5rSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJtZXRhZGF0YV92aWV3cyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+TWV0YURhdGFUcmVlLlZJRVdfRElNUzwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5RGF0YVRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iaW5mb0Fzc2lzdE1vZGVBbGxvd2VkSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJlbmFibGVBdXRvTGlua0luZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGVmYXVsdF9wcmV2aWV3X3BhZ2VsaW1pdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5SW5zZXJ0VGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJydW5PblN0YXJ0dXBJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnRydWU8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUxheW91dFRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUludGVyYWN0aXZlTW9kZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2

*-INTERNAL_COMMENT LINE#1$YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJlbmFibGVJbmZvTWluaV9TYXZlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRlZmF1bHRfcHJldmlld19wYWdlbGltaXRfbGF5b3V0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj4xPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlTbGljZXJzVGFiSW50ZXJhY3RpdmVJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnRydWU8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGVmYXVsdF9jb21wb3NlX2Zvcm1hdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+UERGPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlSZXNvdXJjZXNGaWVsZFRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUZvcm1hdFRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgIDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJjYXNjYWRlTmFtZXMiIHR5cGU9Ik1hcCIvPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iTWFzdGVyX0ZpbGVzIiB0eXBlPSJTZXQiPg0KICAgICAgICAgICAgPEVudHJ5IHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmVudGVycHJpc2VfYW5hbHl0aWNzL3JwdF9ib2FyZF9tZXRyaWNzPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5lbnRlcnByaXNlX2FuYWx5dGljcy9tYWludF9yZXBvcnRpbmdfZGF0ZXM8L0VudHJ5Pg0KICAgICAgICA8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0ibWV0YWRhdGFWaWV3QXMiIHR5cGU9Ik1hcCI+DQogICAgICAgICAgICA8RW50cnkga2V5PSJlbnRlcnByaXNlX2FuYWx5dGljcy9tYWludF9yZXBvcnRpbmdfZGF0ZXMiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPk1ldGFEYXRhVHJlZS5WSUVXX0RJTVM8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZW50ZXJwcmlzZV9hbmFseXRpY3MvcnB0X2JvYXJkX21ldHJpY3MiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPk1ldGFEYXRhVHJlZS5WSUVXX0RJTVM8L0VudHJ5Pg0KICAgICAgICA8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iU2xpY2VyR3VpSXNsYW5kIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5leUppUVhWMGIxQnlaWFpwWlhjaU9tWmhiSE5sTENKaVQzQjBhVzl1YzBkeWIzVndWbWx6YVdKc1pTSTZkSEoxWlN3aVlsSmxZMHhwYldsMFIzSnZkWEJXYVhOcFlteGxJanAwY25WbExDSmlVSEpsZG1sbGQwTnZiblJ5YjJ4V2FYTnBZbXhsSWpwMGNuVmxMQ0ppVW5WdWRHbHRaVU52Ym5SeWIyeFdhWE5wWW14bElqcDBjblZsZlE9PTwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJTTElDRVJfSU5GT1JNQVRJT04iIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPlBEOTRiV3dnZG1WeWMybHZiajBpTVM0d0lpQmxibU52WkdsdVp6MGlWVlJHTFRnaUlITjBZVzVrWVd4dmJtVTlJbTV2SWo4K1BDRXRMVU5QVFZCTVJWUkZYMU5NU1VORlVsOUhVazlWVUMwdFBqeFRURWxEUlZKZlIxSlBWVkErUEVkU1QxVlFJR2R5YjNWd1RuVnRZbVZ5UFNJd0lpQnpiR2xqWlhKSGNtOTFjRXhoWW1Wc1BTSkhjbTkxY0NBeElpQnpiR2xqWlhKSGNtOTFjRTl5WkdWeVBTSXdJaUJ6YkdsalpYSkhjbTkxY0ZOcGVtVTlJakFpSUhOc2FXTmxja2R5YjNWd2FHbGtaVDBpWm1Gc2MyVWlMejQ4TDFOTVNVTkZVbDlIVWs5VlVEND08L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iZ3JvdXBGaWVsZHMiIHR5cGU9IlNldCIvPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iZ3JvdXBGaWVsZHNST0NoYXJ0IiB0eXBlPSJTZXQiLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Imdyb3VwRmllbGRzUk9UYWJsZSIgdHlwZT0iU2V0Ii8+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJiaW5GaWVsZHMiIHR5cGU9IlNldCIvPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iYmluRmllbGRzUk9UYWJsZSIgdHlwZT0iU2V0Ii8+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJiaW5GaWVsZHNST0NoYXJ0IiB0eXBlPSJTZXQiLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImVuYWJsZVByZXZpZXciIHR5cGU9ImphdmEubGFuZy5Cb29sZWFuIj50cnVlPC9Qcm9wZXJ0eT4NCiAgICA8L09iamVjdD4NCjwvUm9vdD4NCg==

-*Do not delete or modify the comments above

-*COMPONENT=Join_J001

JOIN INNER FILE enterprise_analytics/rpt_board_metrics AT RPT_BOARD_METRICS.RPT_BOARD_METRICS.MONTH_END_DATE

TO UNIQUE FILE enterprise_analytics/maint_reporting_dates AT MAINT_REPORTING_DATES.MAINT_REPORTING_DATES.REPORTING_MTH_END_ONE_YEAR TAG J001 AS J001

WHERE RPT_BOARD_METRICS.RPT_BOARD_METRICS.MONTH_END_DATE GE J001.MAINT_REPORTING_DATES.REPORTING_MTH_END_ONE_YEAR;

END

-*COMPONENT=Define_rpt_board_metrics

DEFINE FILE enterprise_analytics/rpt_board_metrics

d_metric/D12=IF METRIC_NAME EQ &Comparison1.(<Total Admitted Assets,Total Admitted Assets>, <Non-Admitted Assets,Non-Admitted Assets>, <Bond Values,Bond Values>, <Stock Values,Stock Values>, <Other Invested Assets,Other Invested Assets>, <Other Invested Assets - ACG Admitted Value,Other Invested Assets - ACG Admitted Value>, <Other Invested Assets - LP Investments,Other Invested Assets - LP Investments>, <Uncollected Premiums,Uncollected Premiums>,<Total Liabilities,Total Liabilities>, <Unearned Premium Reserves,Unearned Premium Reserves>, <Case Reserves,Case Reserves>, <IBNR,IBNR>, <LAE Reserves,LAE Reserves>, <Total Loss and LAE Reserves, Total Loss and LAE Reserve>, <Actuary Point Estimate,Actuary Point Estimate>, <Actuary High Estimate,Actuary High Estimate>, <Contingency Commission Liability,Contingency Commission Liability>,<Total Surplus,Total Surplus>,<Surplus Notes,Surplus Notes>|FORMAT=A255V).Balance Sheet Metric:.QUOTEDSTRING THEN METRIC_VALUE ELSE 0 ;

END

ENGINE INT CACHE SET ON

SET PAGE-NUM=NOLEAD

-DEFAULTH &WF_HTMLENCODE=OFF;

SET HTMLENCODE=&WF_HTMLENCODE

SET ARGRAPHENGINE=JSCHART

-DEFAULTH &WF_EMPTYREPORT=ON;

SET EMPTYREPORT=&WF_EMPTYREPORT

SET EMBEDHEADING=ON

SET GRAPHDEFAULT=OFF

-DEFAULTH &WF_STYLE_UNITS=PIXELS;

-DEFAULTH &WF_STYLE_HEIGHT=405.0;

-DEFAULTH &WF_STYLE_WIDTH=770.0;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

GRAPH FILE enterprise_analytics/rpt_board_metrics

-* Created by Info Assist for Graph

SUM d_metric AS &Comparison1

BY RPT_BOARD_METRICS.RPT_BOARD_METRICS.MONTH_END_DATE_YEAR_M AS Month End Date

WHERE RPT_BOARD_METRICS.RPT_BOARD_METRICS.MONTH_END_DATE GE J001.MAINT_REPORTING_DATES.REPORTING_MTH_END_ONE_YEAR;

WHERE d_metric IS-NOT MISSING OR d_metric NE 0;

HEADING

&Comparison1

ON GRAPH PCHOLD FORMAT JSCHART

ON GRAPH SET VZERO OFF

ON GRAPH SET GRWIDTH 1

ON GRAPH SET AUTOFIT ON

ON GRAPH SET UNITS &WF_STYLE_UNITS

ON GRAPH SET HAXIS &WF_STYLE_WIDTH

ON GRAPH SET VAXIS &WF_STYLE_HEIGHT

ON GRAPH SET LOOKGRAPH

[Manoj Chaurasia]

Debra

Is REPORTING_MTH_END_ONE_YEAR a DATE field or just the year portion I guess the real question is it the same format as what you used to JOIN to that worked

[Debra Waybright]

Yes, it is a DATE field, same format as other field.

[Manoj Chaurasia]

Then I think you need to report this to techsupport so you can hopefully find the root cause of this

[Debra Waybright]

I was afraid you would say that!

[Toby Mills]

Hi Deb

Out of curiosity, what fieldname does your FOC258 show you after the :

(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: %1

An element in a computational expression is not recognized. It may

be a mistyped fieldname or function.

Another thing Id suggest is removing your WHERE statement from the JOIN if thats possible. Youve got that same JOIN down in your GRAPH FILE request, so maybe its not needed in the JOIN

My rationale goes something like

 

you create a JOIN thats got a WHERE in it that refers to J001.

Then you try to change the JOIN and it temporarily breaks the connection to J001.

 

Maybe for that brief second, WF remembers the rpt_board_metrics file is still JOINed to the maint_reporting_dates table and its attempting to verify the WHERE condition in the JOIN. Seems like WF could get its hands crossed a little.

Or - just remove the JOIN first, maybe save it and then reopen to add the JOIN Something to get a JOIN CLEAR to happen on rpt_board_metrics before you are trying to alter the JOIN.

You can probably tell that I dont use the GUI tools that much

Just trying to think of how WF might be thinking that trips it up.

What do you think

[Debra Waybright]

The fieldname in the error message is the parameter that is used in the define statement.

That is what I was thinking, too. We tried deleting the filter before we changed the join, but it still errored.

Good thought on removing the join, saving and trying again. I will give that a try. Need to have the WHERE on the join because we want to just pull records that are greater than or equal to a date field on the joined table.