Focal Point
[SOLVED] A VALUE IS MISSING FOR amper variable

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6367073196

June 20, 2019, 10:45 AM
DWaybright
[SOLVED] A VALUE IS MISSING FOR amper variable
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