I did a search of the forum but didn't find an answer that fixes this for me. I have a program that uses variables to know what category to run a report for. It uses SQL to get the data and I have SET statements to help create the SQL. I get the following error whether I run it from the HTML page or stand alone from App Studio:
(FOC295) A VALUE IS MISSING FOR: &MPFIELD
Here's the code that sets the variables: -SET &MPFIELD='Ministry_Partner_Number'; -SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';
-SET &Summ_Where = IF &SUMMARY_CATEGORY EQ 'Countrywide' THEN '' ELSE IF &SUMMARY_CATEGORY EQ 'Region' THEN ' and Policy_Region = ''&SUMMARY_VALUE.EVAL''' ELSE IF &SUMMARY_CATEGORY EQ 'State' THEN ' and Policy_State = ''&SUMMARY_VALUE.EVAL''' -ELSE IF &SUMMARY_CATEGORY EQ 'Agency' THEN ' and Agency_Number = ''&SUMMARY_VALUE.EVAL''' ELSE IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act.&MPFIELD.EVAL = &SUMMARY_VALUE.EVAL)' ELSE '';
I ran it with ECHO=ALL and if the Summary Category is anything other than Ministry, &MPFIELD is blank, as you can see here: -ELSE IF Agency EQ 'Agency' THEN ' and Agency_Number = ''231''' ELSE IF Agency CONTAINS 'Ministry' THEN ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act. = 231)' ELSE '';
The variables get set as needed and the report runs, but the error prevents me from checking &LINES after the SQL code because the error resets &LINES.
Any thoughts?
Thanks.This message has been edited. Last edited by: DWaybright,
WebFOCUS 8.2.03 (production), 8.2.06 (testing) AppStudio, InfoAssist Windows, All Outputs
June 20, 2019, 11:44 AM
FP Mod Chuck
Deb
Try putting a -RUN after your -SET's and before the code.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 20, 2019, 01:16 PM
MartinY
quote:
-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';
Above is not valid IF THEN ELSE You are missing an ELSE Let look at it with incremented position Should it be
IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN
(IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number')
ELSE ????
or
IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN
(IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE ????)
ELSE 'Ministry_Partner_Number'
Also, why set &MPFIELD to a specific value that you tried to change using an IF statement ?
-SET &MPFIELD='Ministry_Partner_Number';
-SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';
I suspect that the first -SET should be:
-DEFAULTH &MPFIELD='Ministry_Partner_Number'
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
June 21, 2019, 08:16 AM
jgelona
Since Martin explained what is wrong, I would like to offer an alternative way to code this. Would this work for you?
-SET &MPFIELD=
- IF &SUMMARY_CATEGORY CONTAINS 'Ministry' AND &SUMMARY_VALUE IN (100,120,122,123,120,243)
- THEN 'National_Ministry_Partner_Number'
- ELSE 'Ministry_Partner_Number';
-*
-SET &Summ_Where = DECODE &SUMMARY_CATEGORY(
- 'Region' ' and Policy_Region = ''&SUMMARY_VALUE.EVAL'''
- 'State' ' and Policy_State = ''&SUMMARY_VALUE.EVAL'''
- 'Agency' ' and Agency_Number = ''&SUMMARY_VALUE.EVAL'''
- 'Ministry' ' and exists (select 1 from biprod.dbo.MST_CRM_ACCOUNT act where act.CRM_Unique_ID = mth.CRM_Unique_ID and act.&MPFIELD.EVAL = ''&SUMMARY_VALUE.EVAL'')'
- ELSE '');
Kind of curious. In your original code, in the -SET for &Summ_Where, why is &SUMMARY_VALUE.EVAL in quotes for all values of &SUMMARY_CATEGORY except 'Ministry'?This message has been edited. Last edited by: jgelona,
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
June 25, 2019, 01:27 PM
DWaybright
posted June 20, 2019 01:16 PM Hide Post quote: -SET &MPFIELD=IF &SUMMARY_CATEGORY CONTAINS 'Ministry' THEN IF &SUMMARY_VALUE EQ 123 OR 120 OR 100 OR 122 OR 243 THEN 'National_Ministry_Partner_Number' ELSE 'Ministry_Partner_Number';
Above is not valid IF THEN ELSE You are missing an ELSE
D'oh! That was indeed the issue. Thanks Martin!
jgelona, the Ministry option was added more recently and I guess I just didn't make the effort for it to match the previous coder's style.
WebFOCUS 8.2.03 (production), 8.2.06 (testing) AppStudio, InfoAssist Windows, All Outputs