Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] A VALUE IS MISSING FOR amper variable
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] A VALUE IS MISSING FOR amper variable
 Login/Join
 
Platinum Member
posted
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
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 127 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1569 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted 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
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post


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
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 127 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] A VALUE IS MISSING FOR amper variable

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.