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     DYNAMIC WHERE STATEMENT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
DYNAMIC WHERE STATEMENT
 Login/Join
 
Master
posted
I have created a hold file with all the countries from the car file...

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-RUN

I am trying to dynamically build a where statement to run a
report for only the first three of the countries in the hold file.

-SET &WHERE1 = 'WHERE COUNTRY EQ';
-SET &WHERE2 = &WHERE1 | ' ' | &COUNTRY.&I | 'OR' | ; ...

Any ideas?

Thanks! Confused


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Platinum Member
posted Hide Post
Here's an example:

TABLE FILE CAR
PRINT COUNTRY
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD FORMAT ALPHA
END
-RUN


-SET &CNTRY = '';
-READ HOLD &CNTRY.10


-SET &STMT = 'WHERE COUNTRY EQ ';
-SET &IDX = 1;

-REPEAT :LOOP WHILE (&IORETURN EQ 0);

-SET &CNTRY = TRUNCATE(&CNTRY);

-SET &STMT = &STMT | '''' | &CNTRY | '''';


-SET &IDX = &IDX + 1;

-SET &STMT = IF &IDX LT 4 THEN &STMT || ' OR ' ELSE &STMT;


-READ HOLD &CNTRY.10

-:LOOP

-TYPE STMT: (&STMT)  


Regards,
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
Guru
posted Hide Post
Here's a very simplicitic method.

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-*
TABLE FILE HOLDFILE
PRINT COUNTRY
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD AS CHOLD
END
-*
TABLE FILE YOURFILE
PRINT FIELDS
WHERE COUNTRYFIELD IN FILE CHOLD
END


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Expert
posted Hide Post
-SET &ECHO=ALL;

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-SET &SEL_COUNTRY = 'WHERE COUNTRY IN (';

-SET &COUNTER = 1;

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;
-IF &COUNTER GT 3 GOTO EXIT_LOOP1;

-READ H001 &COUNTRY.A10.
-SET &SEL_COUNTRY = &SEL_COUNTRY | ' ''&COUNTRY.EVAL''';
-SET &COUNTER = &COUNTER + 1;
-END_LOOP1
-EXIT_LOOP1

-SET &SEL_COUNTRY = &SEL_COUNTRY || ');';

-TYPE &SEL_COUNTRY

TABLE FILE CAR
PRINT *
&SEL_COUNTRY
END
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
I like Glenda's solution. However, couldn't you eliminate one hold file, as follows:

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD AS COUNTRY
END
-RUN
TABLE FILE YOURFILE
PRINT FIELDS
WHERE COUNTRYFIELD IN FILE COUNTRY
END




   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
Master
posted Hide Post
quote:
Francis Mariani


Thanks everyone.

I like the one by Francis. Now what I want to make a 2nd pass to get the next 2 (or in this case), the remaining countries (values) in the database?

Seems to me I wanna do something with the &lines counter.

Ideas? Idea


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
Idea:
TABLEF FILE CAR
  LIST COUNTRY
  ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
TABLE FILE HOLD1 
"HOLD1:"
  PRINT *
END
-RUN
TABLE FILE HOLD1
  PRINT COUNTRY
  IF LIST FROM 4 TO 5
  ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD2 
"HOLD2:"
  PRINT *
END
-RUN
TABLE FILE CAR
  PRINT SEG.MODEL
  BY COUNTRY
  BY CAR
  WHERE COUNTRY IN FILE HOLD2;
END


The LIST verb in the first HOLD request adds a column called LIST to its output, which the WHERE in the second HOLD request references in order to pull the desired instances.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
You can use &LINES in your -REPEAT statement which will loop through the -REPEAT as many times as there are &LINEs. The WHERE statement in your initial TABLE FILE CAR can be used as your selection criteria to only get those countries which you desire.




   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
Virtuoso
posted Hide Post
Tom,
Something to show the flexibility of DM:
  
-* File tomsweb.fex
-SET &ECHO=ALL;
-DEFAULT &F=3, &T=4
DEFINE FILE CAR
-* Number the countries
L/I1 WITH COUNTRY = 1+LAST L;
AL/A1=EDIT(L);
-* Create DM variables
DMCOUNTRY/A30 = '-SET &|COUNTRY' | AL | ' = ''' | COUNTRY || ''';';
END
-RUN
-* Save the DM variables in a fex
FILEDEF CSET DISK CSET.FEX
TABLE FILE CAR
PRINT DMCOUNTRY
ON TABLE SAVE AS CSET
END
-RUN
-* Include the variables in the procedure
-INCLUDE CSET
TABLE FILE CAR
PRINT SALES
BY COUNTRY BY BODYTYPE
WHERE COUNTRY IN (
-* Use the variables that you want, here the 3rd and the 4th
-REPEAT #W FOR &I FROM &F TO &T;
'&COUNTRY.&I'
-#W
);
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
Try two loops:

-SET &ECHO=ALL;
-*
-SET &REP_LIMIT = 2;
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-*
-SET &LIMIT = &LINES;
-*
-READ H001 NOCLOSE &COUNTRY.A10.
-*
-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;
-*
-SET &SEL_COUNTRY = 'WHERE COUNTRY IN (';
-SET &COUNTER = 1;
-*
-REPEAT END_LOOP2 &REP_LIMIT TIMES;
-IF &IORETURN NE 0 GOTO EXIT_LOOP2;
-SET &SEL_COUNTRY = &SEL_COUNTRY | ' ''&COUNTRY.EVAL''';
-SET &COUNTER = &COUNTER + 1;
-READ H001 NOCLOSE &COUNTRY.A10.
-END_LOOP2
-EXIT_LOOP2
-*
-SET &SEL_COUNTRY = &SEL_COUNTRY || ');';
-TYPE &SEL_COUNTRY
-*
TABLE FILE CAR
HEADING CENTER
" CAR REPORT BY COUNTRY "
" AS OF: &DATE"
PRINT MODEL
BY COUNTRY
&SEL_COUNTRY
END
-RUN
-END_LOOP1


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
I have a solution. Thanks everyone for your ideas!
Nice Thread


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report 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     DYNAMIC WHERE STATEMENT

Copyright © 1996-2020 Information Builders