May 19, 2008, 04:57 PM
Bethanyquote:
'&COUNTRY.(FIND COUNTRY IN HCOUNTRY ).Country'
Can this be used in an IF statement?
Bethany
May 19, 2008, 05:08 PM
j.grossHere's one way:
TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD
END
-RUN
-SET &N=&LINES;
-SET &NN=12*&N;
DEFINE FILE HOLD
I/I2=I+1;
STRINGV/A&NN|V = IF (I EQ 1) THEN COUNTRY ELSE STRING || (', '| COUNTRY);
STRING /A&NN = STRINGV;
END
TABLE FILE HOLD
WRITE LST.STRING
ON TABLE SAVE
END
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING
May 19, 2008, 05:25 PM
BethanyWould it work the same if instead of countries you had a list of numbers (P7 format)?
May 19, 2008, 05:44 PM
j.grossYou'd need to convert the values to alpha format in order to use the catenation operator. EDIT() will do that, if you don't mind the leading zeros (otherwise use FTOA and LJUST)
DEFINE FILE CAR
PACKED/P7=LENGTH;
END
TABLE FILE CAR
BY PACKED
ON TABLE HOLD
END
-RUN
-SET &NN=(7+2)*&LINES-2;
DEFINE FILE HOLD
I/I2=I+1;
STRING/A&NN|V = IF (I EQ 1) THEN EDIT(PACKED) ELSE STRING || (', '| EDIT(PACKED));
END
TABLE FILE HOLD
WRITE LST.STRING/A&NN
ON TABLE SAVE
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING
results in
&STRING = 0000163, 0000165, 0000170, 0000176, 0000177, 0000182, 0000187
May 19, 2008, 05:49 PM
PrarieI'm sorry...missed the part where you wanted it separated by comma's. Yes you could do mine with an IF. ...but guess you are on to what you really wanted.
| In Focus since 1993. WebFOCUS 7.7.03 Win 2003 |
May 19, 2008, 06:28 PM
Darin LeeYou could also just use a loop with a -READ.
TABLE FILE CAR
SUM
MAX.COUNTRY AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE SAVE AS HCOUNTRY FORMAT ALPHA
END
-RUN
-SET &COUNTER=&LINES - 1;
-READ HCOUNTRY &STRING.A10 NOCLOSE
-REPEAT READLIST &COUNTER.EVAL TIMES
-READ HCOUNTRY &VALUE.A10
-SET &STRING='&STRING.EVAL'||','||'&VALUE.EVAL';
-READLIST
-TYPE &STRING
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
May 20, 2008, 02:33 AM
Tony AYou could also use the ACROSS verb to cut out concatenation after a repeated loop to read the &vars. If you use FORMAT COM then you only need to replace the double quotes by singles where necessary as the format will only add double quotes around alphanumeric values and not numeric. Less steps ....
You need to ensure that your resultant variable doesn't exceed limits for the IN statement.
APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE SAVE AS MYHOLD FORMAT COM
END
-RUN
-* Make sure that this read is long enough
-READ MYHOLD &COUNTRY.A100
-RUN
-SET &COUNTRY = STRREP(&COUNTRY.LENGTH, '&COUNTRY.EVAL', 1, '"', 1, '''', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH');
TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END
-RUN
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
May 20, 2008, 05:07 AM
Danny-SRLAnother possibility is to ensure that all values have quotes around them and a trailing comma. Then find the number of countries that answer screening conditions. Finally, as Tony suggests, use ACROSS for putting all values on one line. A finishing touch to remove the last comma.
-* File create_variable.fex
-* To prevent non-printing fields from being saved in the HOLD file
SET HOLDLIST=PRINTONLY
-*
-* Add quotes and comma
DEFINE FILE CAR
QCOUNTRY/A13='''' | COUNTRY || ''',';
END
-*
-* Count the number of selected countries
TABLE FILE CAR
BY QCOUNTRY
WHERE SALES GT 15000;
ON TABLE HOLD
END
-RUN
-*
-* Calculate the full length of the saved line
-SET &QLEN=13 * &LINES;
-*
-* Save the line
TABLE FILE HOLD
SUM QCOUNTRY
ACROSS QCOUNTRY
ON TABLE SAVE
END
-RUN
-*
-* Read the saved values
-READ SAVE &COUNTRY.A&QLEN.EVAL
-*
-* Do away with the last comma
-SET &COUNTRY = &COUNTRY || ',';
-SET &COUNTRY = TRIM('T', &COUNTRY, &QLEN, ',', 1, A&QLEN.EVAL);
-*
-* Run the program
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END
The wonders of WF!
Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
May 20, 2008, 09:03 AM
PBrightwellBethany,
You have been given a lot of solutions, but what are you trying to accomplish? Why do you want the values in an amphere variable separated by commas?
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
May 20, 2008, 11:36 AM
Jim MorrowHere are two more solutions to consider:
This one use Define to build the quoted string and is suitable for feeding an SQL passthru
DEFINE FILE CAR
STRING_WK/A512V = IF LAST STRING EQ ' ' THEN '''' || COUNTRY || ''''
ELSE LAST STRING || ' , ''' || COUNTRY || '''';
END
TABLE FILE CAR
SUM
-** THIS COMPUTE IS NECCESARY TO REMOVE LENGTH IN VARIABLE FORMAT FIELD
COMPUTE STRING/A512 = STRING_WK;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN (
-INCLUDE STROUT.FTM
)
END
-RUN
This one will work with a table request
TABLE FILE CAR
BY COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN FILE STROUT
END
-RUN
Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3
May 28, 2008, 05:51 PM
BethanyThanks for the many suggestions. I'm just getting back to working on this report and I'm still having problems. The big picture is for an FML report that needs to have particular formatting. Due to the way WHEN EXISTS works in FML, I need to determine whether a FOR value will exist in the final report and if not then skip the code for that particular FOR value.
This is the basic code I'm working with now.
This is to set the countries that I need in my output if they do exist;
-SET &COUNTRY1 = 'ENGLAND';
-SET &COUNTRY2 = 'ITALY';
-SET &COUNTRY3 = 'W GERMANY';
This would be the part where I get list of countries that meet a certain criteria and create the list of the ones that actually do exist for my final output.
TABLE FILE CAR
SUM
COUNTRY
BY COUNTRY NOPRINT
WHERE COUNTRY IN ('ENGLAND', 'ITALY');
ON TABLE HOLD
END
-SET &N=&LINES;
-SET &NN=12*&N;
DEFINE FILE HOLD
I/I2=I+1;
STRINGV/A&NN|V = IF (I EQ 1) THEN '''' | COUNTRY | '''' ELSE STRING || (', '| '''' | COUNTRY || '''');
STRING /A&NN = STRINGV;
END
TABLE FILE HOLD
WRITE LST.STRING
ON TABLE SAVE
END
-RUN
-READ SAVE &STRING.A&NN.EVAL
Here in the final output. I want to see if &COUNTRY1 (ENGLAND) is in the list of countries and if it's not then skip the code that would include it on the report. Then check to see if &COUNTRY2 is in the list etc...
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
FOR COUNTRY
-IF &COUNTRY1 NOT IN (&STRING) GOTO SKIP1;
ENGLAND OVER
-SKIP1
-IF &COUNTRY2 NOT IN (&STRING) GOTO SKIP2;
ITALY OVER
-SKIP2
-IF &COUNTRY3 NOT IN (&STRING) GOTO SKIP3;
W GERMANY OVER
-SKIP3
BAR
END
I'm getting errors that "IN" must appear in a valid clause and if I don't get that message, it's still not correctly determining if &COUNTRY1,2,3 is actually in the list.
Thanks again for all of your suggestions.
Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
May 28, 2008, 06:24 PM
Darin LeeDidn't quite understand your big picture for what you are trying to accomplish, but aside from that - NOT IN is not valid syntax for what you are doing. NOT IN file is used to validate a value against a file and is used in a WHERE or IF statement within a TABLE command. It cannot be used to check a string or in Dialogue Manager.
You might try
-IF &STRING OMITS 'ENGLAND' or -IF &STRING NOT-LIKE %ENGLAND% which should both work in Dialogue Manager statements.
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
May 29, 2008, 02:07 AM
Tony ABethany,
If you have the list of values in a file then just read through that file using a repeat loop -
APP PREPENDPATH IBISAMP
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY CONTAINS 'E'
ON TABLE SAVE AS CTRY_S1
END
-RUN
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
FOR COUNTRY
-READ CTRY_S1, &Country
-REPEAT :Loop WHILE &IORETURN EQ 0;
'&Country.EVAL' OVER
-READ CTRY_S1, &Country
-:Loop
""
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |