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     CONDITIONS USING IF STATEMENT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
CONDITIONS USING IF STATEMENT
 Login/Join
 
Member
posted
How to achieve the following scenario

IF ( V_EMP IS (EMP_FILE) ) OR ( V_VAR EQ '122' )

Here V_EMP and V_VAR are the two variables of table request
EMP_FILE is the hold file with the list of EMP NO

and also how to filter if we have more than one filter condition

eg: Suppose we had filter conditions on two hold files
like IF ( V_EMP1 IS (EMP_FILE1) ) AND ( V_EMP2 IS (EMP_FILE2) )

Can any body resolve this ....


Thanku
cdprasad

WF/7.14 WIN2000
 
Posts: 20 | Registered: December 21, 2006Report This Post
Platinum Member
posted Hide Post
Try replacing the IS with EQ.


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Virtuoso
posted Hide Post
Try

WHERE (V_EMP IN FILE EMP_FILE) OR (V_VAR EQ '122');

and

WHERE (V_EMP1 IN FILE EMP_FILE1) AND (V_EMP2 IN FILE EMP_FILE2);


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Alan's suggestion should work fine. Here's another idea that overcomes a certain limitation. There is a limit to the number of values that can exists in the test file. So a simple JOIN could also resolve the problem. Even if the test files are just large but not over the limit a join may be more efficient, especially if the DBMS can do the join instead of FOCUS.

JOIN V_EMP IN hostfile TO crfield IN EMPFILE AS J0

In the first example you would also need to do a SET ALL=ON for a left outer join and then

WHERE crfield NE ('' or 0 or MISSING depending on format of crfield) OR V_VAR EQ '122'

In the second example a second join would also be used

JOIN V_EMP1 IN hostfile TO crfield1 IN EMP_FILE1 AS J0
JOIN V_EMP2 IN hostfile TO crfield2 IN EMP_FILE2 AS J1

If you use any reference to a field in EMP_FILE1 and EMP_FILE2 the inner join takes care of the selection criteria

TABLE FILE hostfile
PRINT
V_EMP
crfield1 NOPRINT
crfield2 NOPRINT
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Member
posted Hide Post
Thank u Alan and Darin lee

Alan method is working fine.

But when the size of the hold file grows the method fails i.e the limitation of where clause.

In that case we can go for the method like suggested by Darin Lee


Thanks & Regards,
cdprasad
 
Posts: 20 | Registered: December 21, 2006Report This Post
Platinum Member
posted Hide Post
I have a scenario where I need to filter against a hold file which can contain hundreds or even thousands of rows. The hold file was achieved in a somewhat complex manner, so a simple join wouldn't work for me.

I had a query against an Oracle table, filtered against a large hold file...

TABLE FILE XYZ
...
ON TABLE HOLD AS LARGEHOLD
END

TABLE FILE ORACLE_TABLE
SUM
FIELD
BY BYFIELD
IF FIELD2 EQ (LARGEHOLD)
ON TABLE HOLD AS RESULTS
END


I found that the performance was very slow...about 25 seconds to retrieve a few thousand records from a table with 1.75 million rows. It turns out that the resultant Oracle query gets bogged down with the {WHERE field IN list} clause (which is the result of the IF FIELD2 EQ (LARGEHOLD) statement). My list was about 135 entries.

So what I did was create a 2-step process. I moved the filter against LARGEHOLD to a second query, like this:

TABLE FILE ORACLE_TABLE
SUM
FIELD
BY BYFIELD
ON TABLE HOLD AS RESULTS
END

TABLE FILE RESULTS
SUM
FIELD
BY BYFIELD
IF FIELD2 EQ (LARGEHOLD)
ON TABLE HOLD AS FINAL
END


This had the effect of reducing response time to 1/5 of the original query. It went from 25 seconds to 5 seconds.

And as an added bonus, I don't hit the Oracle limit of 1000 expressions in a list (when my list happens to be larger than 1000 items), because the filter is now against the Focus file, which does not have this limitation.

Here is the error message I received when filtering directly against the Oracle table, with a list > 1000 items:
(FOC1400) SQLCODE IS 1795 (HEX: 00000703)
: ORA-01795: maximum number of expressions in a list is 1000

Hope this helps someone.
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 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     CONDITIONS USING IF STATEMENT

Copyright © 1996-2020 Information Builders