Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report 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: 2127 | Location: Customer Support | Registered: April 12, 2005Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders