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] Parameter Help...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Parameter Help...
 Login/Join
 
kupa
Member
posted
I have two parameters. The first one selects the department code and the second one selects the main account within the department code. However, when I choose a department code “20” for the first parameter and 'No Selection" for the second parameter the first parameter is ignored and it appears that all the main accounts appear that are beyond department code “20”. Can someone give me some advice? Below is the code I’m using.
TABLE FILE SQLOUT
PRINT
ACCT_NUM AS 'GWA Acct ,Num'
GWA_TAS AS 'GWA,TAS'
RTRIM_O100925_TITLE_ AS 'Title'
DEPT_XFER AS 'Dept.,Transfer'
SUB_ACCT_SYMBOL AS 'Sub Acct,Symbol'
GWA_FY_DSGNTR AS 'GWA FY,Designator'
FSCL_PRD_BGN AS 'FY,Begin ,Period'
FSCL_PRD_END AS 'FY,End ,Period'
FUND_TYP_CD AS 'Fund Type,Code'
ACCT_TYP_CD AS 'Acct,Type Code'
ACCT_CLASS_TYP_CD AS 'Acct,Class,Type Code'
SUB_CLASS AS 'Sub ,Class'
ACCT_DBT_CRDT_IND AS 'Debt/,Credit'
ACCT_STAT_TYP_CD AS 'Acct Status,Type Code'
HEADING
" <+0> "
"GWA Accounts by Fund Group"
" "
"Department Regular: "Main Account: " "
FOOTING
"&DATEtrMDYY &TOD<+0> "
"Page: WHERE ( ACCT_MAIN EQ &Main.(OR(FIND ACCT.ACCT.ACCT_MAIN,ACCT.ACCT.ACCT_MAIN IN acct)).Main. )
AND ( DEPT_REG EQ &DEPT_REG.(FIND ACCT.ACCT.DEPT_REG,ACCT.ACCT.DEPT_REG IN acct).Dept Regular.);
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

Thanks in advance,
Imani

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: September 27, 2010Report This Post
dbeagan
Platinum Member
posted Hide Post
Parameter selection must be happening in the sql preceding the code you posted. Your "no selection" probably passes FOC_NONE as a value for that parameter which causes that entire line of code to be ignored. Rewrite it as:

WHERE DEPARTMENTCODE = '20'
AND ACCOUNT = &ACCTNUM

that way if &ACCTNUM is FOC_NONE then only the desired line is affected.


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
kupa
Member
posted Hide Post
Thanks for your response. After trying with the amper sign I get prompt to enter the vaules. This is not the behavior I was expecting. Based on what you mentioned, it looks like the sql is running prior to the parameters. How do I include parameters in my SQL for the report based on using a drop drown?

Regards,
Kupa

This message has been edited. Last edited by: kupa,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: September 27, 2010Report This Post
Wep5622
Virtuoso
posted Hide Post
I think you misunderstood what dbeagan was saying.

What he was saying is that if you don't specify an account, the value of that parameter will be FOC_NONE, which in turn disables the WHERE clause that it is part of. I'm guessing (Hey, I'm a noob too!) that means that because the selection of the department also gets ignored.

If that's the case, then the below should fix your problem:
WHERE ACCT_MAIN EQ &Main.(OR(FIND ACCT.ACCT.ACCT_MAIN,ACCT.ACCT.ACCT_MAIN IN acct)).Main.;
WHERE DEPT_REG EQ &DEPT_REG.(FIND ACCT.ACCT.DEPT_REG,ACCT.ACCT.DEPT_REG IN acct).Dept Regular.;


However, with this you will get results for ALL accounts of the selected department if no account was selected - if that's what you want, cool. If not, test if that variable is FOC_NONE before the query and (if it is) substitute its value with something non-existant.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
dbeagan
Platinum Member
posted Hide Post
Thanks Wep5622 for helping clarify. I missed the reference to the where that is buried in footing. But apparently the where for the actual filtering of the data is in the sql which kupa hasn't provided. So I suppose that sql looks more like this all on a single line:
 
WHERE ACCT_MAIN=&Main AND DEPT_REG=&DEPT_REG

but you want to break it into two lines:
 
WHERE ACCT_MAIN=&Main 
AND DEPT_REG=&DEPT_REG


That way, if &DEPT_REG='FOC_NONE' then only the AND DEPT_REG=&DEPT_REG drops off.


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
kupa
Member
posted Hide Post
Good Morning,

I'm still having problems getting the parameters to work.

Here is what I am trying to achieve;

I would like to select the department code and all the main accounts or one or more main accounts.

After I select department code '20' and select "no selection" for the main account, I get the following error message:

This also occurs if I select a main account after select the department code.


0 ERROR AT OR NEAR LINE 40 IN PROCEDURE x2tjg1jtFOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Any ideas?

Thanks,
Imani


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: September 27, 2010Report This Post
kupa
Member
posted Hide Post
Thanks everyone for getting me on the right track. I was able to figure out what was wrong with my parameter string. I just added quotes to the parameter for the field that was passing a string value. Below is the updated code for the parameters:

WHERE DEPT_REG EQ '&DEPT_REG.(FIND ACCT.ACCT.DEPT_REG,ACCT.ACCT.DEPT_REG IN acct).Dept Regular.';
WHERE ACCT_MAIN EQ &Main.(OR(FIND ACCT.ACCT.ACCT_MAIN,ACCT.ACCT.ACCT_MAIN IN acct)).Main.;


Again Thanks for the feedback and comments!...
What would I do without this forum!


Kupa


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: September 27, 2010Report This Post
Doug
Expert
posted Hide Post
Just keep in mind that the FOC_NONE negates that etire statement. The "AND" made the WHERE a single statement that was ignored due to the FOC_NONE.

A search of "FOC_NONE" in WebFOCUS DevStudio (F1) Help explains a lot about FOC_NONE. See "Reference: Internal Processing of FOC_NONE" in particular.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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] Parameter Help...

Copyright © 1996-2020 Information Builders